7

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they happen? I know there is a statement_timeout, lock_timeout and deadlock_timeout but most the places I read state that you don't want to set this in the postgres.conf file.

Just wanted to see if there is a typical/best practice method for this. It looks like setting deadlock_timeout to something like 10 minutes would be sufficient, but I wanted to make sure I wasn't missing some key something as to why that would be a bad idea.

Improve this question
4
  • IIRC with leaving all these settings at their respective defaults, deadlocks resolve themselves pretty quick. Have to have a closer look at our logs to say something more concrete. However, dealing with deadlocks is always better done by ensuring that all queries process their input in the same order - meaning not trying to resolve the deadlocks when they happen but prevent them.
    – dezso
    Jan 5, 2016 at 21:05
  • 1
    @dezso I suspect a misconception between long running block and deadlock. Deadlocks usually don't give the support desk time to kill the (chosen) blocker and usually show an error to the user, long running blockers are something else
    – Tom V
    Jan 5, 2016 at 21:11
  • Going through the code is something that needs to be done for sure and is being done albeit slowly. I was looking for something in the short term that would kill the sessions when they do hang. I am possibly talking about long running block, the two statements hang and neither will finish without the other. The user just gets a spinning wheel when that happens.
    – Toolman21
    Jan 5, 2016 at 21:26
  • In case of a real deadlock, Postgres will kill one of them automatically. I agree with Tom that you most probably do not have a deadlock situation when you have to kill the sessions yourself.
    – user1822
    Jan 5, 2016 at 21:51

1 Answer 1

Reset to default
5

10 minutes is an extremely high setting for deadlock_timeout, which may also explain why you even get the chance to intervene manually. the default is 1 second and the manual advises:

The default is one second (1s), which is probably about the smallest value you would want in practice. On a heavily loaded server you might want to raise it. Ideally the setting should exceed your typical transaction time, so as to improve the odds that a lock will be released before the waiter decides to check for deadlock. Only superusers can change this setting.

I don't expect your typical transactions exceed 10 minutes. Try something like 20 seconds if you have long running queries.

The best defense against deadlocks is also documented here:

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

If you do this consequently, there is no chance for a deadlock.

Improve this answer

Your Answer

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

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