AWS RDS Deadlocks

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

• Amazon RDS Metrics and Dimensions (AWS Documentation)
• How to deal with MySQL deadlocks (Percona Database Performance Blog)
• Explicit Locking (PostgreSQL Documentation)
• pg_locks (PostgreSQL Documentation)
• Data Consistency Checks at the Application Level (PostgreSQL Documentation)