A deadlock is caused when two or more transactions hold locks that the other transactions require. MySQL’s InnoDB engine and PostgreSQL’s engine resolve deadlocks by aborting one of the transactions, allowing the others to complete.

Occasional deadlocks should be tolerated by most applications, but repeated deadlocks can severely impact application performance as queries must be retried until the conditions causing deadlock abate.

MySQL Deadlocks


You can use the  SHOW ENGINE INNODB STATUS  command to show the latest deadlock in MySQL.  In MySQL 5.6.2+ you can enable the innodb_print_all_deadlocks variable to have all deadlocks logged to the mysqld error log.  The Percona Database Performance Blog has a very detailed guide for troubleshooting deadlocks here.  

 

PostgreSQL Deadlocks


To view the active locks in postgres, query the pg_locks view:

 SELECT * FROM pg_locks 

Deadlocks can also be logged by enabling the log_lock_waits setting.

The key to diagnosing deadlocks is understanding what is causing the deadlocks. Check which locks are active, and the application code that issues those queries.

 

Solutions


To solve deadlocks, you must understand when and where locks are acquired in your DBMS. Research exclusive locks, shared locks, gap locks, and index locks. The solution will always involve changing queries, and will sometimes involve changing schema.

In general, you can reduce the impact of deadlocks by:

  • Split longer transactions into smaller ones
  • Avoid executing application code while holding open a lock
  • Ensure the application acquires locks in a consistent order
  • Avoid mixing shared and exclusive locks on the same table

 

Resources