Deadlocks & Locking
AdvancedDeadlocks occur when two transactions hold locks each other needs; InnoDB detects the cycle and rolls back the lighter transaction; access rows in consistent order to avoid them.
Overview
A **deadlock** occurs when two (or more) transactions each hold a lock that the other needs, creating a circular wait with no way to proceed. InnoDB has a deadlock detector that identifies these cycles and automatically rolls back the **"victim"** transaction — the one with the least amount of undo log (cheapest to roll back). The rolled-back transaction receives error 1213 `ER_LOCK_DEADLOCK`. The key prevention strategy is to **always access resources in the same order** across all transactions. Deadlocks are also caused by implicit lock escalation from missing indexes (full-table locks or large gap locks) and bulk operations that lock many rows.
How InnoDB Deadlocks Happen
Classic scenario: Transaction A locks row 1, then tries to lock row 2. Transaction B has row 2 locked and tries to lock row 1. Both are waiting — deadlock. InnoDB detects this within milliseconds and rolls back the cheaper victim. The surviving transaction continues and must retry the rolled-back transaction.
-- Session A -- Session B
START TRANSACTION; START TRANSACTION;
UPDATE accounts SET bal=bal-100 UPDATE accounts SET bal=bal+50
WHERE id = 1; ← locks row 1 WHERE id = 2; ← locks row 2
-- (waits for A to release row 2)
UPDATE accounts SET bal=bal+100
WHERE id = 2; ← waits for B's lock
-- DEADLOCK DETECTED
-- InnoDB rolls back one victim (usually B — lighter undo log)
-- ERROR 1213 (40001): Deadlock found
-- Diagnose
SHOW ENGINE INNODB STATUS; -- "LATEST DETECTED DEADLOCK" section
SELECT * FROM performance_schema.data_lock_waits;Prevention: Consistent Lock Order and Index Usage
The most effective prevention is **consistent row access order** across all transactions — if every operation locks row 1 before row 2, the deadlock cycle can never form. The second cause is **missing indexes**: an UPDATE without an index forces InnoDB to acquire locks on many rows (or the whole table) via gap locks, dramatically increasing deadlock probability.
-- BAD: different order in different transactions → deadlock risk
-- Tx A: UPDATE WHERE id=1; UPDATE WHERE id=2
-- Tx B: UPDATE WHERE id=2; UPDATE WHERE id=1
-- GOOD: always process lower ID first
UPDATE accounts SET bal=bal-100 WHERE id = LEAST(src_id, dst_id);
UPDATE accounts SET bal=bal+100 WHERE id = GREATEST(src_id, dst_id);
-- BAD: UPDATE without index → locks many rows via next-key locking
UPDATE orders SET status='SHIPPED' WHERE customer_email = 'a@b.com';
-- If no index on customer_email → InnoDB may lock large ranges
-- GOOD: index the column used in WHERE
CREATE INDEX idx_orders_email ON orders(customer_email);
-- Check lock contention
SELECT r.trx_id waiting_trx, b.trx_id blocking_trx,
r.trx_query, b.trx_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;Handling Deadlocks in Application Code
Deadlocks are transient and retriable — the rolled-back transaction should be retried. In Spring, annotate the service method with `@Retryable(include = DeadlockLoserDataAccessException.class)`. Keep transactions short to minimise the window for conflict. Avoid holding application-level locks or making external calls (HTTP, cache) inside a DB transaction.
@Service
class TransferService {
// Retry on deadlock — Spring Retry
@Retryable(
include = DeadlockLoserDataAccessException.class,
maxAttempts = 3,
backoff = @Backoff(delay = 50, multiplier = 1.5)
)
@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
// Consistent order prevents deadlock
Long lo = Math.min(fromId, toId);
Long hi = Math.max(fromId, toId);
Account a = accountRepo.findById(lo).orElseThrow();
Account b = accountRepo.findById(hi).orElseThrow();
if (fromId.equals(lo)) {
a.debit(amount); b.credit(amount);
} else {
b.debit(amount); a.credit(amount);
}
}
@Recover
public void recoverTransfer(DeadlockLoserDataAccessException ex,
Long from, Long to, BigDecimal amount) {
log.error("Transfer failed after retries: {} → {}", from, to);
throw new TransferFailedException("Deadlock unresolved after retries", ex);
}
}Key Points to Remember
- 1Deadlock = circular lock wait; InnoDB rolls back the cheapest victim automatically (error 1213)
- 2Prevention: always access rows in the same consistent order across all transactions
- 3Missing indexes cause large gap locks → more rows locked → higher deadlock probability
- 4Keep transactions short — minimise the lock-hold window
- 5Never make HTTP calls or cache operations inside a DB transaction
- 6Retry deadlock victims: use @Retryable(DeadlockLoserDataAccessException.class) in Spring
Interview Questions
Sign in to ask AriaHow does InnoDB detect and resolve deadlocks?
What is the most effective strategy to prevent deadlocks in a banking transfer operation?
Why do missing indexes increase deadlock probability?
How would you handle a deadlock error in a Spring Boot service?
What is the difference between a deadlock and a lock timeout in MySQL?
Ask Aria about Deadlocks & Locking
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.