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.
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.
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.
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: