PostgreSQL – Deadlock Best Practices

deadlocklockingpostgresqlpostgresql-9.3

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.

Best Answer

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.

Related Question