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

3
  • 1
    Postgres 9.5 reaches EOL in February 11, 2021. Consider upgrading to a current version. (May also help with your issue.) Commented Dec 17, 2020 at 14:06
  • You've edited your post to show a WHERE clause, but your pastebin link still doesn't show a WHERE clause.
    – jjanes
    Commented Dec 17, 2020 at 16:29
  • The requests in paste in are just flags, I considered they are not needed to the question.
    – Slim
    Commented Dec 18, 2020 at 23:01

1 Answer 1

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.

4
  • Thank you. My apologies, the "select * from machines" query has a where on the PK. I edited my question to reflect the reality.
    – Slim
    Commented Dec 17, 2020 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. Commented Dec 17, 2020 at 14:56
  • thank for your time. I realized my question was too complicated and I've created another one here dba.stackexchange.com/questions/281846/…
    – Slim
    Commented Dec 19, 2020 at 9:51
  • I think your answer is useful so I prefer to let this one as is.
    – Slim
    Commented Dec 19, 2020 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.