DEADLOCK HANDLINGTransactions
How each engine detects and resolves deadlocks — circular lock dependencies between transactions — and what the application must do in response.
A situation where two or more transactions are each waiting for locks held by the others, causing all to stall. Databases detect deadlocks and abort one victim transaction -- your application must handle this and retry. Minimize by acquiring locks in a consistent order.
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | ✓ Supported | 5.0 | InnoDB automatically detects deadlocks and rolls back the transaction with the least undo work (the 'cheaper' victim). Error code 1213 (ER_LOCK_DEADLOCK). The application must catch this and retry the transaction. |
| PostgreSQL | ✓ Supported | 7.4 | PostgreSQL detects deadlocks using a wait-for graph checked after a configurable lock_timeout or deadlock_timeout (default 1 second). One transaction is chosen as victim and receives error code 40P01. The application must catch and retry. |
| SQL Server | ✓ Supported | 2000 | Deadlock monitor runs every 5 seconds by default (escalating to 100ms when deadlocks are detected). Victim is chosen by lowest deadlock priority, then cheapest rollback. Error 1205. Use SET DEADLOCK_PRIORITY to influence victim selection. |
| Oracle | ✓ Supported | 7 | Oracle detects deadlocks and immediately rolls back only the statement that caused the deadlock (not the entire transaction). ORA-00060 is raised. The transaction remains open and the application can choose to commit, roll back further, or retry the statement. |
| SQLite | ✓ Supported | 3.0.0 | SQLite's locking model (database-level write lock) makes traditional row-level deadlocks impossible. SQLITE_BUSY (error 5) is returned when a write lock cannot be acquired. Use busy_timeout to configure how long to wait before returning SQLITE_BUSY. |
All five engines detect and resolve deadlocks automatically, but the scope of rollback differs critically. MySQL, PostgreSQL, and SQL Server roll back the entire victim transaction — the application must re-execute from the beginning. Oracle only rolls back the individual deadlocking statement, leaving the transaction open; this gives the application finer control but also means it can silently continue in an inconsistent state if not handled carefully. Deadlock avoidance strategies are consistent across engines: always acquire locks in the same order across transactions, keep transactions short, use appropriate index coverage to reduce lock range, and consider optimistic locking (version columns) as an alternative to pessimistic row locks for read-heavy workloads. In SQL Server, Extended Events or the system_health session captures deadlock graphs automatically — reviewing these is the first step in resolving recurring deadlocks.
InnoDB automatically detects deadlocks and rolls back the transaction with the least undo work (the 'cheaper' victim). Error code 1213 (ER_LOCK_DEADLOCK). The application must catch this and retry the transaction.
PostgreSQL detects deadlocks using a wait-for graph checked after a configurable lock_timeout or deadlock_timeout (default 1 second). One transaction is chosen as victim and receives error code 40P01. The application must catch and retry.
Deadlock monitor runs every 5 seconds by default (escalating to 100ms when deadlocks are detected). Victim is chosen by lowest deadlock priority, then cheapest rollback. Error 1205. Use SET DEADLOCK_PRIORITY to influence victim selection.
Oracle detects deadlocks and immediately rolls back only the statement that caused the deadlock (not the entire transaction). ORA-00060 is raised. The transaction remains open and the application can choose to commit, roll back further, or retry the statement.
SQLite's locking model (database-level write lock) makes traditional row-level deadlocks impossible. SQLITE_BUSY (error 5) is returned when a write lock cannot be acquired. Use busy_timeout to configure how long to wait before returning SQLITE_BUSY.