17

A while ago, I spent a nightmarish number of weeks dealing with "deadlock detected" and trying to figure out how to handle it. I ended up finally handling it in such a manner that my code is able to detect when it happens and then retry the same query indefinitely with 50000 microseconds in between each retry, until it works.

Maybe that's bad practice, but so far (months), it has not caused any problems except for logging the "deadlock detected" so-called "errors".

Is it "OK" for me to now suppress the "deadlock detected" errors by marking them as "unimportant" and thus not showing up to me, even if they are still logged into my error log table?

Please don't tell me to "avoid them in the first place". This is simply not possible. They will apparently happen if you do concurrent (multiple processes/script instances) working on the same table/thing. I spent forever trying to "code them away", but it just doesn't seem possible.

Obviously, since I'm asking this instead of just adding the ignore rule and being done with it, I do care about the answers/responses. Still, I don't think I can be convinced at this point that they can be avoided completely. I'm not saying that I get thousands of them logged every hour or anything, but a few each day, seemingly always in the beginning when I do have lots of concurrent processes working on the same table/query.

Improve this question
7
  • 2
    A deadlock is simply an indication of a logical error in your application design. Since you don't want to hear the correct answer to your question, which is indeed "avoid them in the first place", any other answer will be a lie.
    – mustaccio
    Commented Feb 25, 2021 at 16:37
  • A deadlock is almost always a bug in your application. So yes, they are avoidable. But if you really can't find the root cause of the bug, then your approach with a retry is probably the best thing to do. In 30 years of working with relational databases I only had do deal with them maybe 3 or 4 times and all were solved by fixing the bug in the application.
    – user1822
    Commented Feb 25, 2021 at 16:45
  • How surprising that I'd get downvoted by this ultra-toxic community. Anyway, can you be clear about how it's a bug in my application? It logically happens when multiple processes/script instances perform the same work at the same time. How would anyone be able to avoid them in the first place?
    – user15080516
    Commented Feb 25, 2021 at 16:51
  • 2
    Just because the majority of the people disagree with your point of view doesn't mean this is a "toxic community". I didn't downvote, but I could imagine that the downvote was rather caused by the somewhat arrogant tone of your question (which is more a rant against those that disagree with you)
    – user1822
    Commented Feb 25, 2021 at 17:55
  • @a_horse_with_no_name "almost always a bug" is a very broad generalization. Unsure about Postgres, but in SQL Server for example, how would you describe a very typical "bookmark/key lookup deadlock", it's against a single table, the reason it happens is very non-obvious and requires either SNAPSHOT or careful index management to work around. A relatively small number of deadlocks on a busy OLTP database is normal and should always be defended against with retries or user message.
    – Charlieface
    Commented Feb 26, 2021 at 1:17

1 Answer 1

Reset to default
26

A deadlock is a kind of serialization error: you didn't do anything forbidden, it just so happened that there was an interaction with other active transactions that prevented your transaction from being completed. Your reaction is the correct one: retry the transaction. There is absolutely no need to wait before retrying.

I concur that with a sufficiently complicated workload of bigger transactions it can be nigh impossible to rule out deadlocks completely. As long as they happen only rarely, they are not a real problem if as you handle them correctly.

Deadlocks start becoming a problem if they happen too frequently: it means that you have to redo much work, which is bad for performance and puts extra load on your database. Also the one second wait before the deadlock is resolved means that locks are held for a long time (a second is long), which is not great for concurrency.

Even if you cannot completely get rid of deadlocks, you can take measures to reduce them:

  • see that your transactions are short

  • try to reduce the number of data modifications per transaction

Both will reduce the likelihood of running into a deadlock.

It is safe to ignore deadlock errors in your log file, but then you should monitor pg_stat_database.deadlocks and take action of the deadlock count per hour increases beyond an acceptable number.

You seen to take objection to calling a deadlock an error. By definition, an error is a condition that aborts the execution of an SQL statement. So a deadlock is clearly an error.

Improve this answer
1
  • 2
    One thing you should also be doing is to make sure your processes/scripts/etc request their locks on resources in the same order and don’t escalate the locks they take out later. A process that updates table A then B then C then D can run without dead locks with many concurrent requests. If a process has to wait for a lock, it will always only hold locked resources that are not going to be required from a the blocking thread again, so no deadlock can occur.
    – Andrew Sayer
    Commented Feb 25, 2021 at 19:25

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.