2

I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key.

I face a deadlock problem while executing many of these requests:

//this will produce an "AccessExclusiveLock" of type "tuple" on machines
SELECT FROM * machines FOR UPDATE where id = :id;
//this will produce a "RowExclusiveLock" on magictools and a "RowShareLock" on machines
UPDATE magictools SET collections = "large-json" where id = :id

As far I understood running many of these request produces deadlocks. Maybe it's only the updates who are performing this, I don't know. How should I avoid the deadlocks in this case?

I have many indexes on these tables, maybe I have too many indexes?

Below is the report of pg_activity when the problem happened. I do not understand the different modes and lock types, and, just, what happens here? Is it possible that just an update without any transaction causes a deadlock?

deadlock trace link

Slim
  • 237
  • 3
  • 13

1 Answers1

2

There is no deterministic ORDER BY in:

SELECT FROM * machines FOR UPDATE;

So multiple transactions might try to obtain row locks in different (arbitrary) order and interlock.

Also, why would you lock the whole table machines for a single update in magictools? If it's indeed a single UPDATE on magictools per transaction, there is no need to lock rows machines manually at all. Just drop the command. (If there are multiple, then your question is lacking.)

If there is a good reason to lock many or all rows in machines, either do it in deterministic order, like:

SELECT FROM * machines ORDER BY id FOR UPDATE;

Or lock the whole table instead, that's also cheaper:

LOCK TABLE machines IN SHARE MODE;

SHARE MODE is enough for the FK case you have. There is even an example in the manual suggesting as much.

Locking the table also disallows INSERT, of course, unlike the row level locks in your example.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Thank you. My apologies, the "select * from machines" query has a where on the PK. I edited my question to reflect the reality. – Slim Dec 17 '20 at 14:50
  • 1
    @Slim: Please edit some more to clarify. There are multiple "id" floating around. Please clarify what's what, where each transaction starts and ends and whether the given commands are complete. Table definition showing relevant columns with all constraints also help. – Erwin Brandstetter Dec 17 '20 at 14:56
  • thank for your time. I realized my question was too complicated and I've created another one here https://dba.stackexchange.com/questions/281846/is-it-possible-than-two-simple-updates-on-same-table-interlocks – Slim Dec 19 '20 at 09:51
  • I think your answer is useful so I prefer to let this one as is. – Slim Dec 19 '20 at 09:51