Understanding Database Deadlocks and Their Resolution Methods
Database deadlocks are among the most challenging concurrency issues encountered in real-world production systems. While modern databases are designed to handle parallel workloads efficiently, deadlocks remain an unavoidable side effect of correct locking and isolation.
To build a resilient application, it’s crucial to understand how deadlocks form, how databases detect them, and how they are resolved.
This article breaks down database deadlocks from the ground up, covering causes, detection techniques, resolution strategies, and real-world database behaviors.
What is Database Deadlock?
A database deadlock occurs when two or more transactions block each other indefinitely, each waiting for locks held by the other, creating a circular dependency that prevents any of the transactions from proceeding.
How Deadlock Pattern look like?
Transaction A holds a lock on Resource X and waits for Resource Y
Transaction B holds a lock on Resource Y and waits for Resource X
A circular dependency forms, and progress stops completely
Without intervention from the database engine, these transactions would wait forever.
Example
-- Transaction 1 (T1)
BEGIN;
UPDATE orders SET status = 'CONFIRMED' WHERE order_id = 101;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 50;
COMMIT;
-- Transaction 2 (T2)
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 50;
UPDATE orders SET status = 'CONFIRMED' WHERE order_id = 101;
COMMIT;
Transaction 1 (T1) Locks orders(order_id = 101) and waits for inventory(product_id = 50) and Transaction 2 (T2) Locks inventory(product_id = 50) and waits for orders(order_id = 101)
Common Causes of Deadlocks in Practice
Inconsistent Lock Ordering
When different transactions acquire locks on the same resources in different orders. Enforcing a consistent order (e.g., always lock Table A before Table B) is a primary prevention strategy.
Real-world Example: In a banking system, one service updates customer details and then logs the change, while another service logs the action first and then updates the customer.
-- Transaction A
BEGIN;
UPDATE customers SET address = 'New Address' WHERE customer_id = 101;
UPDATE audit_logs SET action = 'ADDRESS_UPDATE' WHERE customer_id = 101;
COMMIT;
-- Transaction B
BEGIN;
UPDATE audit_logs SET reviewed = true WHERE customer_id = 101;
UPDATE customers SET last_updated = NOW() WHERE customer_id = 101;
COMMIT;
Transaction A locks customers, and Transaction B locks audit_logs. Each waits for the other, causing circular dependency.
Long-running Transactions
Transactions that hold locks for extended periods increase the probability of conflict with other transactions.
Real-world Example: In a reporting system, a transaction reads large datasets, performs heavy computation, and then updates a summary table (all within transactions).
BEGIN;
SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';
-- Application processes data for several seconds
UPDATE yearly_summary SET total_sales = 500000 WHERE year = 2024;
COMMIT;
Locks remain held during long processing, and other transactions block and form waiting chains. Deadlock probability increases under concurrency.
Lock Escalation
Databases may automatically convert many fine-grained locks (like row-level) into fewer coarse-grained locks (like table-level) for performance efficiency, which can unexpectedly block other transactions and create deadlocks.
Real-world Example: In a Warehouse management system, bulk updates on inventory rows cause the database to escalate row locks into a table lock.
BEGIN;
UPDATE inventory
SET last_checked = NOW()
WHERE warehouse_id = 5;
COMMIT;
Internally, what is happening is that Many row-level locks are acquired, the database escalates to a table-level lock, and other transactions attempting row updates are blocked.
Concurrent Transaction
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 900;
COMMIT;
Unexpected blocking and circular waits may form.
Poorly Optimized Queries
Inefficient queries that perform large table or index scans can acquire locks, holding them for longer than necessary and increasing contention.
Real-world Example: In a customer support system, missing indexes cause full table scans during updates, locking more rows than necessary.
-- Problematic code
BEGIN;
UPDATE tickets
SET status = 'CLOSED'
WHERE created_at < '2023-01-01';
COMMIT;
It is performing a full table or index scan, a large number of acquired locks, and locks held longer than needed increase contention and lead to deadlocks.
Optimized Version
CREATE INDEX idx_tickets_created_at ON tickets(created_at);
BEGIN;
UPDATE tickets
SET status = 'CLOSED'
WHERE created_at < '2023-01-01';
COMMIT;
Foreign Key Constraints
Actions on a parent table might require the database to internally check or lock related child table records, creating hidden dependencies and potential lock chains.
-- Transaction A
BEGIN;
DELETE FROM documents WHERE doc_id = 200;
COMMIT;
-- Transaction B
BEGIN;
INSERT INTO permissions (doc_id, user_id, role)
VALUES (200, 10, 'EDITOR');
COMMIT;
Deleting a document requires checking related permissions through foreign key constraints, which introduce implicit locks and create lock dependencies that remain invisible in application code.
Deadlock Detection
The database detects deadlocks automatically using the most famous approach, wait-for-graph algorithm.
Wait-for Graph Algorithm
A wait-for graph is a directed graph used by databases to model lock dependencies between transactions.
Each node represents an active transaction.
Each directed edge (T₁ → T₂) means Transaction T₁ is waiting for a resource held by Transaction T₂.
Q. Why do databases use Wait-for Graphs?
Lock-based systems naturally create waiting relationships. Tracking these relationships visually makes deadlock detection efficient. A deadlock is present if and only if a cycle exists in the graph.
Q. How is the Graph Built?
When a transaction requests a lock that cannot be granted:
- The database adds an edge from the waiting transaction to the holding transaction.
The graph is dynamic and updates as locks are acquired or released.
Only blocked transactions participate in the graph.
Deadlock Detection Rule
The database regularly checks (usually every few seconds) all transactions currently waiting on locks.
It builds a wait-for graph showing which transaction blocks which other based on active resource requests and holdings.
Graph traversal algorithms then scan for cycles, declaring a deadlock when one is detected.
Detection Frequency by Database System
Different database systems use varying detection intervals to balance overhead with responsiveness.
PostgreSQL: Checks for deadlocks every 1 second by default after the
deadlock_timeoutperiod.MySQL (InnoDB): Uses immediate detection for simple two-transaction deadlocks, but falls back to periodic checking every ~5 seconds for complex scenarios.
SQL Server: Runs deadlock detection every 5 seconds by default, but can drop to as low as 100 milliseconds under high contention
Deadlock Resolution
Once a deadlock is detected (for example, using the wait-for graph algorithm), the database must break the circular dependency.
To do this, the database first selects a victim transaction. The choice is made carefully to minimize system impact. Typically, the database prefers terminating the transaction that has performed the least amount of work, as rolling it back requires fewer resources. In many systems, newer transactions are also favored as victims under the assumption that older transactions are closer to completion. Some databases additionally support transaction priorities, allowing lower-priority or background tasks to be aborted before critical operations.
Once the victim is chosen, the database rolls back the transaction, releasing all locks held by it. This immediately allows the remaining blocked transactions to continue execution. The rollback preserves atomicity and ensures the database remains in a consistent state.
From an application perspective, deadlocks are not exceptional failures but expected concurrency events. Applications should be designed to catch deadlock errors and retry the transaction, often with a small randomized backoff to avoid repeated collisions. In complex workflows, partial rollbacks using savepoints may also be used to limit lost work, although full rollbacks are more common during deadlock resolution.
While careful transaction design can reduce the likelihood of deadlock, most modern databases rely on detection and resolution rather than strict prevention, as eliminating deadlocks entirely is impractical in high-concurrency environments. The key guarantee provided by the database is that, after resolution, progress resumes safely without violating consistency or isolation.
Deadlock Prevention Strategies
Although deadlocks cannot be completely eliminated in concurrent systems, their frequency and impact can be significantly reduced through careful transaction design and system-level practices. One of the most effective techniques is enforcing a consistent lock ordering across the application. When all transactions acquire locks on shared resources in the same sequence, circular wait conditions are avoided entirely, making deadlocks structurally impossible for those code paths. For example, if an application always updates the users table before the orders table, deadlocks caused by reversed lock ordering are avoided:
-- Consistent ordering (users → orders)
BEGIN;
UPDATE users SET last_login = NOW() WHERE user_id = 10;
UPDATE orders SET status = 'PROCESSED' WHERE order_id = 500;
COMMIT;
-- Problems arise only when different transactions reverse this order.
Another critical strategy is minimizing the scope and duration of transactions. Transactions that hold locks for long periods - especially while performing heavy computation, waiting for user input, or calling external services—dramatically increase contention. By keeping transactions short and limiting them strictly to database operations, locks are released quickly, reducing the chance of conflicts with other concurrent transactions.
-- Read and process outside the transaction
SELECT * FROM reports WHERE year = 2024;
-- Short write transaction
BEGIN;
UPDATE report_summary SET status = 'READY' WHERE year = 2024;
COMMIT;
Query performance also plays a major role in deadlock prevention. Poorly optimized queries that scan large portions of tables or indexes tend to acquire more locks and hold them longer than necessary. Proper indexing, selective queries, and efficient execution plans help reduce lock footprints and improve overall concurrency. Faster queries mean shorter lock lifetimes, which directly lowers deadlock probability.
-- Index to avoid full table scan
CREATE INDEX idx_tickets_status ON tickets(status);
BEGIN;
UPDATE tickets SET status = 'CLOSED' WHERE status = 'RESOLVED';
COMMIT;
Understanding implicit locking behavior, particularly with foreign key constraints, is equally important. Operations on parent tables often require the database to internally lock related child records to maintain referential integrity. When these hidden dependencies are not accounted for, transactions may unintentionally acquire locks in conflicting orders. Designing transactions with awareness of these relationships helps prevent unexpected lock chains.
-- Parent table update
BEGIN;
UPDATE documents SET title = 'Final Draft' WHERE doc_id = 200;
-- Implicitly checks/locks permissions via FK
COMMIT;
Finally, applications should be built with the assumption that deadlocks can still occur under extreme concurrency. Implementing safe retry mechanisms with backoff ensures that when a deadlock does happen, it is handled transparently without user-facing errors. In practice, the most robust systems combine thoughtful transaction design with resilient retry logic, treating deadlocks as a manageable aspect of concurrency rather than a critical failure.
def execute_with_retry(txn_func, retries=3):
for attempt in range(retries):
try:
return txn_func()
except DeadlockError:
if attempt == retries - 1:
raise
time.sleep(random.uniform(0.1, 0.5))
How Specific Database Handle Deadlock?
Different database engines handle detection and resolution differently, based on their design goals and performance trade-offs. Understanding these differences is important when tuning systems or debugging production issues.
SQL Server
SQL Server uses a lock monitor thread that periodically scans for deadlocks by analyzing wait relationships between sessions. When a deadlock is found, SQL Server chooses a victim based on deadlock priority and estimated rollback cost.
-- Set deadlock priority
SET DEADLOCK_PRIORITY LOW;
Example deadlock scenario:
BEGIN TRAN;
UPDATE employees SET role = 'Senior' WHERE emp_id = 77;
-- waits for payroll
UPDATE payroll SET salary = salary + 10000 WHERE emp_id = 77;
COMMIT;
When SQL Server resolves a deadlock, the victim transaction is rolled back with an error:
Transaction (Process ID xx) was deadlocked on lock resources and has been chosen as the deadlock victim.
MySQL
MySQL’s InnoDB engine takes a more aggressive approach to deadlock detection. For simple deadlock patterns, detection happens immediately when a lock request is made. For more complex cases, InnoDB falls back to timeout-based detection.
-- Enable deadlock detection and logging
SET GLOBAL innodb_deadlock_detect = ON; -- Usually default, use only if disabled
SET GLOBAL innodb_lock_wait_timeout = 50; -- The default value, can be adjusted if needed
SET GLOBAL innodb_print_all_deadlocks = ON; -- **Recommended** to log all deadlocks to the error log
Example deadlock in MySQL
-- Transaction 1
START TRANSACTION;
UPDATE wallets SET balance = balance - 500 WHERE user_id = 42;
INSERT INTO ledger (user_id, amount, type) VALUES (42, -500, 'DEBIT');
-- Transaction 2
START TRANSACTION;
INSERT INTO ledger (user_id, amount, type) VALUES (42, 500, 'CREDIT');
UPDATE wallets SET balance = balance + 500 WHERE user_id = 42;
InnoDB detects the deadlock, selects the transaction with lower rollback cost, and aborts it with:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
PostgreSQL
PostgreSQL uses a lazy deadlock detection approach. Instead of checking for deadlocks immediately, it waits for a configurable timeout period before initiating detection. The assumption is that most lock waits are short-lived and will resolve naturally without requiring expensive graph analysis.
-- Configure deadlock detection behavior
SET deadlock_timeout = '1s';
SET log_lock_waits = on;
When a transaction waits longer than deadlock_timeout, PostgreSQL builds a wait-for graph and checks for cycles. If a deadlock is detected, one transaction is aborted and the others continue.
-- Example deadlock scenario
BEGIN;
UPDATE seats SET status = 'HELD' WHERE seat_id = 12;
-- waits for another transaction
UPDATE payments SET amount = amount + 250 WHERE booking_id = 9001;
COMMIT;
If PostgreSQL detects a deadlock, it terminates one transaction with an error like:
ERROR: deadlock detected
This approach minimizes CPU overhead under normal workloads but may result in slightly longer waits before resolution.
Final Thoughts
Database deadlocks are a natural result of concurrent access in multi-user systems, not a database flaw. Modern databases detect and resolve them automatically, but well-designed applications reduce their frequency through consistent locking, short transactions, and proper indexing. Ultimately, treating deadlocks as expected events and handling them with retry logic is key to building reliable and scalable systems.
