Skip to main content
2 of 3
added 273 characters in body

Deadlocks are not caused by a particular statement. It is caused by concurrency issues. So basically, you should start observing how one session of your application deal with from other sessions working concurrently.

Here is a general guideline for avoiding deadlocks:

  1. Always maintain primary keys on tables. This primary key should be the means to identify a particular record in the table. This will avoid too many rows getting within the scope of lock.

  2. Maintain an order in all the transactions. For example, one of your application logic inserts/updates data in table A then table B. There should not be another logic which inserts/updates data in Table B then Table A.

  3. Monitor and catch the culprits. PostgreSQL provides pg_stat_activty and pg_stat_statements like views to monitor sessions and queries. Here is some sample queries using which you can monitor the blocking / deadlocks.https://wiki.postgresql.org/wiki/Lock_Monitoring You may need to adjust log_lock_waits and deadlock_timeout parameters.

  4. Acquire most restrictive lock first in the transaction. so smaller ones won't come along the way.

  5. Last but the least, reduce the size of the transaction. commit more often. long running transactions has more chance of ending up in deadlocks. Moreover long transactions in postgres holds more number of live tuples because of the way MVCC is implemented in Postgres.