SQL Deadlocks: Detection and Prevention
A deadlock occurs when two or more transactions create a circular dependency on locked resources. Transaction A holds a lock that Transaction B needs, while Transaction B holds a lock that...
Key Insights
- Deadlocks occur when two or more transactions create a circular dependency waiting for locks held by each other—understanding the four necessary conditions (mutual exclusion, hold and wait, no preemption, circular wait) is essential for prevention
- Detection tools like SQL Server’s deadlock graphs, PostgreSQL’s
pg_locks, and MySQL’s InnoDB status provide visibility, but prevention through consistent lock ordering and reduced transaction scope is far more effective than reactive handling - Application-level retry logic with exponential backoff should be your last line of defense, not your primary strategy—focus on eliminating deadlock-prone patterns through proper indexing, transaction design, and isolation level selection
What Are SQL Deadlocks?
A deadlock occurs when two or more transactions create a circular dependency on locked resources. Transaction A holds a lock that Transaction B needs, while Transaction B holds a lock that Transaction A needs. Neither can proceed, creating a standstill that the database must resolve by terminating one transaction.
Four conditions must exist simultaneously for a deadlock to occur:
- Mutual exclusion: Resources cannot be shared (locks are exclusive)
- Hold and wait: Transactions hold locks while waiting for additional locks
- No preemption: Locks cannot be forcibly taken from transactions
- Circular wait: A circular chain of transactions exists, each waiting for a lock held by the next
Here’s a classic deadlock scenario with two concurrent transactions on an accounts table:
-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Transaction 1 now holds lock on account 1
WAITFOR DELAY '00:00:05'; -- Simulating processing time
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Transaction 1 needs lock on account 2
COMMIT;
-- Transaction 2 (running concurrently)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
-- Transaction 2 now holds lock on account 2
WAITFOR DELAY '00:00:05'; -- Simulating processing time
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
-- Transaction 2 needs lock on account 1 (DEADLOCK!)
COMMIT;
Transaction 1 locks account 1 and waits for account 2. Transaction 2 locks account 2 and waits for account 1. The database detects this circular wait and kills one transaction, typically the one that’s least expensive to rollback.
Detecting Deadlocks in Your Database
Each major database system provides tools to identify and analyze deadlocks. Detection is your first step toward prevention.
SQL Server offers comprehensive deadlock detection through dynamic management views and Extended Events:
-- Query current locks
SELECT
tl.resource_type,
tl.resource_database_id,
tl.resource_associated_entity_id,
tl.request_mode,
tl.request_status,
wt.blocking_session_id,
s.program_name,
s.host_name,
t.text AS query_text
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_os_waiting_tasks wt ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE tl.request_session_id > 50;
-- Enable deadlock graph capture
CREATE EVENT SESSION deadlock_capture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\Temp\deadlocks.xel');
ALTER EVENT SESSION deadlock_capture ON SERVER STATE = START;
PostgreSQL uses the pg_locks view and server configuration:
-- View current locks and blocking relationships
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- Enable deadlock logging in postgresql.conf
-- log_lock_waits = on
-- deadlock_timeout = 1s
MySQL with InnoDB provides deadlock information through engine status:
SHOW ENGINE INNODB STATUS;
Look for the LATEST DETECTED DEADLOCK section, which shows the transactions involved, locks held, and locks requested.
Common Deadlock Patterns
Understanding typical deadlock scenarios helps you recognize them in your own code.
Index range lock escalation occurs when transactions lock overlapping index ranges in different orders. Foreign key cascades can create unexpected lock chains. Concurrent inserts with triggers may deadlock when triggers access tables in varying sequences.
Here’s a realistic e-commerce deadlock between inventory and order processing:
-- Transaction 1: Create order, then update inventory
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, order_date, total)
VALUES (123, GETDATE(), 299.99);
DECLARE @order_id INT = SCOPE_IDENTITY();
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (@order_id, 456, 2);
-- Update inventory (acquires lock on products table)
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 456;
COMMIT;
-- Transaction 2: Update inventory, then check orders
BEGIN TRANSACTION;
-- Update different product (acquires lock on products table)
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 789;
-- Check if product is in pending orders (needs lock on orders/order_items)
SELECT COUNT(*)
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE oi.product_id = 789 AND o.status = 'pending';
COMMIT;
If these transactions execute concurrently and Transaction 1 locks the orders table while Transaction 2 locks the products table, they’ll deadlock when each tries to access the other’s locked resource.
Prevention Strategies
Prevention beats detection. Apply these strategies to eliminate deadlocks at the design level.
Consistent lock ordering is the most effective prevention technique. Always access tables in the same order across all transactions:
-- BAD: Inconsistent ordering
-- Transaction A: Update accounts 1 then 2
-- Transaction B: Update accounts 2 then 1
-- GOOD: Consistent ordering
-- Always order by account_id
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100
WHERE account_id IN (1, 2)
ORDER BY account_id; -- Ensures locks acquired in consistent order
UPDATE accounts SET balance = balance + 100
WHERE account_id IN (1, 2)
ORDER BY account_id;
COMMIT;
Reduce transaction scope by keeping transactions short and focused:
-- BAD: Long-running transaction
BEGIN TRANSACTION;
-- Business logic processing (5 seconds)
-- External API call (10 seconds)
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
-- Email sending (3 seconds)
COMMIT;
-- GOOD: Minimal transaction scope
-- Do business logic first
-- Make API call
-- Quick transaction
BEGIN TRANSACTION;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
COMMIT;
-- Send email after commit
Choose appropriate isolation levels. Lower isolation levels reduce locking but may introduce anomalies:
-- Use READ UNCOMMITTED for reports where dirty reads are acceptable
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Or use NOLOCK hint (SQL Server)
SELECT COUNT(*) FROM orders WITH (NOLOCK) WHERE status = 'pending';
Optimize indexes to reduce lock escalation from row locks to page or table locks:
-- Ensure covering indexes exist for frequently accessed columns
CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
ON orders(status, order_date)
INCLUDE (customer_id, total);
Retry Logic and Error Handling
Despite prevention efforts, occasional deadlocks may still occur. Implement robust retry logic in your application layer.
Here’s a C# example with exponential backoff:
public async Task<T> ExecuteWithDeadlockRetry<T>(
Func<Task<T>> operation,
int maxRetries = 3)
{
int retryCount = 0;
int delayMs = 100;
while (true)
{
try
{
return await operation();
}
catch (SqlException ex) when (ex.Number == 1205) // Deadlock error
{
retryCount++;
if (retryCount >= maxRetries)
{
_logger.LogError(ex,
"Deadlock retry limit exceeded after {RetryCount} attempts",
retryCount);
throw;
}
_logger.LogWarning(
"Deadlock detected, retry {RetryCount} of {MaxRetries}",
retryCount, maxRetries);
// Exponential backoff with jitter
var jitter = Random.Shared.Next(0, 50);
await Task.Delay(delayMs + jitter);
delayMs *= 2;
}
}
}
// Usage
var result = await ExecuteWithDeadlockRetry(async () =>
{
using var transaction = await _dbContext.Database.BeginTransactionAsync();
// Your transactional operations
var account = await _dbContext.Accounts.FindAsync(accountId);
account.Balance -= amount;
await _dbContext.SaveChangesAsync();
await transaction.CommitAsync();
return account;
});
Python equivalent with PostgreSQL:
import time
import random
from psycopg2 import OperationalError
def execute_with_retry(operation, max_retries=3):
retry_count = 0
delay_ms = 100
while True:
try:
return operation()
except OperationalError as e:
if '40P01' not in str(e): # PostgreSQL deadlock error code
raise
retry_count += 1
if retry_count >= max_retries:
logger.error(f"Deadlock retry limit exceeded after {retry_count} attempts")
raise
logger.warning(f"Deadlock detected, retry {retry_count} of {max_retries}")
jitter = random.randint(0, 50)
time.sleep((delay_ms + jitter) / 1000.0)
delay_ms *= 2
Advanced Mitigation Techniques
For persistent deadlock issues, consider advanced techniques.
Lock hints provide fine-grained control over locking behavior:
-- Use ROWLOCK to prevent lock escalation
UPDATE products WITH (ROWLOCK)
SET stock_quantity = stock_quantity - 1
WHERE product_id = 123;
-- Use UPDLOCK to prevent conversion deadlocks
SELECT * FROM accounts WITH (UPDLOCK, ROWLOCK)
WHERE account_id = 1;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
Snapshot isolation uses row versioning to eliminate read locks entirely:
-- Enable snapshot isolation (SQL Server)
ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Reads don't block writes, writes don't block reads
SELECT * FROM accounts WHERE account_id = 1;
COMMIT;
Table partitioning reduces contention by distributing locks across partitions:
-- Partition orders by date range
CREATE PARTITION FUNCTION OrderDateRange (DATE)
AS RANGE RIGHT FOR VALUES
('2024-01-01', '2024-02-01', '2024-03-01');
CREATE PARTITION SCHEME OrderDateScheme
AS PARTITION OrderDateRange
ALL TO ([PRIMARY]);
Optimistic concurrency control eliminates locks by detecting conflicts at commit time:
-- Add version column
ALTER TABLE accounts ADD version_number INT DEFAULT 1;
-- Update with version check
UPDATE accounts
SET balance = balance - 100,
version_number = version_number + 1
WHERE account_id = 1
AND version_number = @expected_version;
-- If @@ROWCOUNT = 0, someone else modified the row
Deadlocks are inevitable in high-concurrency systems, but with proper detection, consistent design patterns, and layered defense strategies, you can minimize their frequency and impact. Focus on prevention through transaction design, use monitoring to identify hotspots, and implement retry logic as a safety net—not a crutch.