Deadlocks in SQL
Introduction
A database deadlock occurs when transactions form a circular wait for incompatible resources. Transaction A holds something needed by Transaction B, while B holds something needed by A. Neither can progress by waiting for the other to finish, so the database must break the cycle, normally by aborting one participant.
Deadlocks are not necessarily database defects. They are an expected risk of concurrent transactions that acquire multiple resources. Production engineering therefore has two responsibilities: reduce avoidable cycles through transaction and query design, and recover safely when a residual deadlock still occurs.
What Is a Database Deadlock?
Imagine two transfers. Transaction A locks account 101 and next requests account 102. At the same time, Transaction B locks account 102 and next requests account 101. A waits for B, and B waits for A. Since neither transaction can release its first lock before completing or rolling back, their dependency is circular.
The essential feature is the cycle, not simply waiting. A transaction waiting behind a long update is blocked, but it is not deadlocked if the holder can continue and eventually commit independently.
Why Deadlocks Occur
Deadlocks most often arise when code paths touch overlapping resources in different orders. Long transactions widen the time in which paths can overlap. Missing indexes can make statements visit more rows or ranges than intended, while foreign-key checks, triggers, cascades, and secondary-index maintenance can add resources that are not obvious from the application SQL.
High concurrency increases probability but is not the root cause. The root is a possible cyclic acquisition pattern. A two-row transaction can deadlock at modest load if two code paths reverse order, while a carefully ordered high-volume workflow may run without cycles.
How a Deadlock Forms
- Transaction A acquires an incompatible lock on Resource 1.
- Transaction B acquires an incompatible lock on Resource 2.
- A requests Resource 2 and waits for B.
- B requests Resource 1 and waits for A.
- The database detects the circular wait.
- One transaction is selected as the victim and rolled back.
- The survivor acquires the released resource and continues.
A cycle can involve more than two transactions and more than two resources. The database represents dependencies as a wait-for graph and looks for cycles in that graph.
Deadlock Timeline
| Step | Transaction A | Transaction B |
|---|---|---|
| 1 | Begins | Begins |
| 2 | Updates account 101 | Updates account 102 |
| 3 | Requests account 102 and waits | Still holds account 102 |
| 4 | Still holds account 101 | Requests account 101 and waits |
| 5 | Cycle detected | Cycle detected |
| 6 | Victim or survivor | Victim or survivor |
Four Necessary Conditions
The classic Coffman conditions explain when resource deadlocks can exist. Mutual exclusion means at least one resource has incompatible uses. Hold and wait means a transaction retains one resource while requesting another. No preemption means another transaction cannot arbitrarily take the held lock. Circular wait means each participant depends on the next participant in a cycle.
| Condition | Database interpretation |
|---|---|
| Mutual exclusion | Conflicting lock modes cannot coexist |
| Hold and wait | A transaction keeps granted locks while requesting more |
| No preemption | Locks remain until release or database intervention |
| Circular wait | A closed dependency cycle exists |
Consistent resource ordering prevents circular wait and is therefore one of the strongest practical prevention techniques.
Deadlock vs Blocking
Blocking is a one-direction dependency. Transaction B waits for A, but A is runnable and can eventually release its lock. Short blocking is normal concurrency control. A deadlock contains a cycle, so none of the participants in that cycle can finish without intervention.
Deadlock vs Lock Timeout
A timeout means a wait exceeded a configured duration. It does not prove a cycle existed. A slow but healthy transaction can cause a timeout, while a deadlock can be detected and resolved long before the general timeout expires.
| Condition | Circular dependency | Typical database action |
|---|---|---|
| Blocking | No | Wait for holder |
| Lock timeout | Not required | Fail when wait limit is reached |
| Deadlock | Yes | Abort a selected participant |
| Serialization failure | May be a dependency anomaly rather than lock cycle | Abort for serial correctness |
Sample Table
CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY,
balance DECIMAL(12, 2) NOT NULL
);
INSERT INTO accounts (account_id, balance)
VALUES (101, 1000.00), (102, 1000.00);
Commit the setup before opening two independent database sessions. The following sequence deliberately reverses account order.
Two-Session Deadlock Example
-- Session A
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 101;
-- Pause here. Later run:
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 102;
-- Session B
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE account_id = 102;
-- After Session A requests 102, run:
UPDATE accounts
SET balance = balance + 50
WHERE account_id = 101;
One session should receive the engine's deadlock error. Roll back any remaining failed transaction state, then commit or roll back the survivor. Syntax and transaction-abort behavior vary by database.
Making the Demonstration Deterministic
Do not start every statement at random. First let A lock 101, then let B lock 102. Next make A request 102 and verify that it waits. Finally make B request 101. This exact ordering closes the cycle.
Automated tests should coordinate sessions with barriers, latches, or test-only signaling rather than fixed sleeps. Each session must use one dedicated connection with autocommit disabled.
How Databases Detect Deadlocks
The lock manager knows which transaction owns each resource and which transaction is waiting. From that information it constructs or incrementally evaluates a wait-for graph. A directed edge from A to B means A cannot continue until B releases a resource. A cycle signals a deadlock.
Detection can run immediately when waits form, periodically, or after a short threshold, depending on the product. Distributed cycles spanning separate resource managers may be invisible to one database and require timeouts or distributed coordination.
Deadlock Victim Selection
The database chooses one or more transactions to abort so surviving transactions can proceed. Selection can consider rollback cost, transaction age, amount of work, configured priority, or internal heuristics. It is not safe to assume the newest or smallest transaction always loses.
SQL Server exposes deadlock priority as an influence; when priorities match, rollback cost can matter. Other engines use their own rules. Application correctness must not depend on a specific victim.
What Happens After Detection?
The victim receives an error and its work is rolled back according to the engine's transaction semantics. Released locks wake eligible waiters, and a survivor continues. The failed application operation is not automatically successful just because the other transaction completed.
Framework behavior matters. Some mark the transaction rollback-only, while others leave a connection in failed transaction state until an explicit rollback. Cleanup must happen before the connection returns to a pool.
Why the Complete Transaction Must Be Retried
The victim's earlier reads and writes are no longer valid. Retrying only the final statement can omit rolled-back work or reuse a decision made from stale data. A correct retry starts a fresh transaction, repeats every read and validation, performs every write, and attempts commit again.
Retries should be bounded and delayed with jitter. Persistent cycles indicate a design problem; unlimited immediate retries can amplify load and repeatedly collide.
Row-Level Deadlocks
The common example involves transactions updating the same rows in opposite order. Row-level deadlocks also arise when updates touch secondary index entries in different sequences. The SQL may name one row, while index maintenance adds more lockable resources.
Table and Schema Deadlocks
Deadlocks can involve table modes or metadata protection. One session may hold data locks and request a schema change while another holds a schema-related lock and requests data access. Deployment and maintenance processes should therefore be included in concurrency analysis, not treated as separate from application traffic.
Read-to-Write Conversion Deadlocks
Two transactions can each acquire compatible shared locks while reading and then both request exclusive conversion for an update. Each conversion waits for the other's shared lock. Some engines use update-lock modes or explicit locking-read syntax to choose one potential updater earlier.
MVCC can reduce this pattern for ordinary reads, but write conflicts and other cycles remain possible.
Key-Range Deadlocks
Serializable isolation and locking reads can protect key ranges, gaps, or next-key intervals to prevent phantoms. Transactions that scan overlapping ranges in different directions can then deadlock even when they began with different existing rows. Suitable indexes narrow those ranges and make acquisition order more predictable.
Foreign-Key Deadlocks
Inserting or deleting a child row may require checks or locks involving its parent. Concurrent parent updates, child inserts, cascades, and deletes can create cycles across tables. Index foreign-key columns where recommended by the database and include referential operations in the resource-order plan.
Triggers and Cascades
A trigger can update audit, inventory, summary, or relationship tables that are absent from the original statement. Cascades can touch many related rows. Deadlock graphs often reveal these hidden paths. Keep trigger behavior focused, document the resources it accesses, and use the same ordering convention as application code.
Indexes and Execution Plans
Missing or unsuitable indexes can expand a targeted operation into a scan that locks many keys or ranges. Two statements may also choose different access paths and visit the same rows in different orders. Statistics and parameter values can change a plan, explaining why a deadlock appears only under certain production inputs.
Do not add an index blindly. Capture the deadlock resources and actual plans, then verify that the proposed index reduces visited data and maintains a stable access pattern without excessive write cost.
Consistent Resource Ordering
If every transfer locks the smaller account ID before the larger ID, two-account transactions cannot form the simple reversed-order cycle. The rule must apply across services, stored procedures, batch jobs, triggers, and administrative tools.
-- Normalize order before entering the transaction.
first_id = MIN(source_id, destination_id)
second_id = MAX(source_id, destination_id)
Ordering does not eliminate every possible deadlock, but it removes a major class by breaking circular wait.
Keep Transactions Short
Begin the transaction only when required data is ready. Avoid user input, sleeps, file processing, large object serialization, and remote HTTP calls while locks are held. Commit promptly and guarantee rollback on every failure path.
Short duration reduces overlap, blocking, deadlock probability, old-version retention, and recovery cost simultaneously.
Use Atomic SQL
Replace read-then-write sequences with conditional statements when possible. For inventory, one update can both verify and decrement stock:
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = ?
AND available_quantity > 0;
An affected-row count of one means the claim succeeded. This design shortens the transaction and avoids a separate locking read, although surrounding multi-resource workflows can still deadlock.
Choose Isolation Deliberately
Higher isolation can introduce range locks, retain read protection longer, or create more serialization failures. Lower isolation may reduce some waits but cannot replace required correctness. Select isolation from the business invariant and test its actual implementation on the production database.
Avoid Unnecessary Explicit Locks
Locking hints, table locks, and broad FOR UPDATE queries can turn local contention into system-wide blocking. Use explicit protection only for a documented invariant, with a selective predicate and predictable ordering. Prefer database constraints and atomic writes when they express the rule directly.
Batch Large Updates
A single transaction updating millions of rows retains many resources and overlaps with more work. Deterministic batches reduce lock footprint and rollback cost. Choose stable key boundaries, commit between batches when the business operation permits, and make restart behavior explicit.
Retry Strategy
- Recognize only documented deadlock or serialization error codes.
- Roll back and discard the failed transaction context.
- Preserve one operation-level idempotency key.
- Wait with bounded exponential backoff and jitter.
- Open a fresh transaction and repeat the complete unit.
- Stop after a small configured limit and surface the failure.
- Record attempt count and a safe correlation identifier.
Only retry operations designed to be idempotent or protected from duplicate external side effects.
Deadlocks in PostgreSQL
PostgreSQL detects lock cycles after a configurable deadlock-check delay and aborts one transaction with SQLSTATE 40P01. The transaction remains failed until rolled back. Ordinary reads use MVCC, but row locks, table locks, foreign-key work, advisory locks, and explicit locking reads can participate in cycles.
Use server logs, pg_stat_activity, pg_locks, and blocker functions to understand live waits. Logging lock waits can add context around recurring patterns.
Deadlocks in MySQL InnoDB
InnoDB automatically detects many transactional lock cycles and commonly returns error 1213 with SQLSTATE 40001 to a victim. Record, gap, and next-key locks mean predicates and indexes strongly affect the resource graph.
SHOW ENGINE INNODB STATUS reports the latest detected deadlock, while Performance Schema and InnoDB transaction views support broader analysis. Enabling all-deadlock logging can help during investigation but should be managed for log volume.
Deadlocks in SQL Server
SQL Server's lock monitor detects cycles and returns error 1205 to the victim. Deadlock priority can influence selection; rollback cost can matter when priorities match. Key, page, object, exchange, metadata, and other resources may appear in a graph.
The built-in system health Extended Events session captures deadlock information. A deadlock XML graph identifies processes, resources, owners, waiters, modes, and the victim, making it the central artifact for diagnosis.
Deadlocks in Oracle
Oracle detects a cycle and reports ORA-00060. The error is commonly accompanied by diagnostic trace information describing the participants and resources. Row locks, unindexed foreign keys in relevant designs, application ordering, and autonomous or triggered work can contribute.
Handle transaction state according to Oracle's documented statement and transaction behavior, and use diagnostic views plus trace files rather than inferring the cycle from application timing alone.
SQLite and Busy Conditions
SQLite has coarse file-oriented concurrency and only one writer at a time. Many conflicts surface as SQLITE_BUSY or SQLITE_LOCKED rather than a server-style row-lock deadlock victim. WAL mode improves reader-writer overlap but does not create multiple concurrent writers.
Keep writes short, choose transaction modes deliberately, configure a bounded busy timeout, and distinguish database busy handling from deadlocks involving external application resources.
Error Comparison
| Database | Typical signal | Primary diagnostic source |
|---|---|---|
| PostgreSQL | SQLSTATE 40P01 | Server logs and lock/activity views |
| MySQL InnoDB | Error 1213, SQLSTATE 40001 | InnoDB status, logs, Performance Schema |
| SQL Server | Error 1205 | Extended Events deadlock XML |
| Oracle | ORA-00060 | Trace and dynamic performance views |
| SQLite | BUSY or LOCKED conditions | Application timing and connection state |
Always verify codes against the exact driver and database version used by the application.
Analyzing a Deadlock Graph
- Identify the victim and every surviving participant.
- List each resource, owner mode, waiter mode, and object.
- Reconstruct the resource acquisition order for each path.
- Map SQL statements to procedures, endpoints, jobs, or triggers.
- Inspect actual plans and indexes for broad scans or opposite order.
- Find the smallest change that breaks the cycle.
- Verify the fix under deterministic concurrency and production-like load.
The error text alone rarely contains enough information. Preserve graph or trace artifacts with sensitive values redacted.
Banking and Account Transfers
Transfers deadlock when different requests debit and credit accounts in opposite order. Normalize account ordering, use atomic balance checks, and keep external payment calls outside the lock-holding database phase. The debit and credit still belong in one database transaction.
Orders and Inventory
An order transaction may lock the order and then inventory, while replenishment locks inventory and then related order allocations. Establish one cross-workflow order, narrow inventory predicates with indexes, and use conditional updates. Constraints remain the final defense against negative inventory or duplicate allocation.
Booking and Enrollment
Ticket, appointment, and course-capacity workflows often touch a capacity record plus a reservation row. Pre-created slots or a guarded counter can provide an explicit resource and consistent sequence. A unique constraint on the assigned seat or time slot prevents duplicate final state after retries.
Payroll and Batch Processing
Payroll jobs can overlap with employee corrections or benefits updates. Process employees in a stable key order, use bounded batches, and schedule incompatible bulk work deliberately. One giant transaction increases overlap and makes victim rollback expensive.
Handling Deadlocks in JDBC
for (int attempt = 1; attempt <= maxAttempts; attempt++) {
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try {
executeBusinessTransaction(connection, operationId);
connection.commit();
return;
} catch (SQLException error) {
connection.rollback();
if (!isRetryableDeadlock(error) || attempt == maxAttempts) {
throw error;
}
}
}
sleepWithJitter(attempt);
}
isRetryableDeadlock must inspect vendor error codes and SQLState deliberately. Do not retry every SQLException. The operation ID should remain stable across attempts so external effects and durable requests remain idempotent.
Connection Pools and Transaction Cleanup
A pooled connection survives longer than one business request, so failed transaction state must never leak to the next borrower. The retry boundary should own the connection or transaction context, roll back in a guaranteed failure path, close statements and result sets, and return the connection only after the framework or pool can restore autocommit, isolation, read-only state, and session settings. Reusing the same failed context without cleanup can produce secondary errors that hide the original deadlock.
Framework annotations also need careful boundaries. A retry placed inside one already-failed declarative transaction does not create a fresh snapshot or release the old transaction's state. Put retry orchestration outside the transaction method so every attempt starts a new transaction. Test proxy behavior, nested propagation, asynchronous execution, and exception translation with the actual framework configuration rather than assuming an annotation creates the intended boundary.
Deadlock Observability
Application logs should record the database family, vendor error code, SQLState, retry attempt, transaction name, operation identifier, elapsed time, and a safe correlation ID. Avoid recording passwords, tokens, full personal data, or unrestricted bind values. Database-side graphs and traces should be retained long enough to correlate with the application event, because a victim log alone describes only one participant in the cycle.
Useful service metrics include deadlocks per transaction, retries per successful operation, exhausted retries, rollback duration, connection-pool wait time, and latency percentiles. Tag metrics by operation or workload rather than high-cardinality user identifiers. Alerting should focus on sustained rate, business failures, or latency impact; one correctly retried deadlock is evidence that concurrency control worked, while a rising pattern indicates an access-order or hot-resource problem.
Prevention, Avoidance, and Detection
Prevention structurally breaks a necessary condition, commonly through consistent ordering or eliminating hold-and-wait with atomic SQL. Avoidance decides whether an acquisition is safe based on current state, which general-purpose databases rarely expose as a complete application strategy. Detection permits cycles, finds them, and aborts a victim.
Production systems normally combine prevention for common paths, database detection for residual cycles, and application retries for selected victims.
Deadlock Starvation
If the same low-priority or low-cost operation repeatedly becomes a victim, it may never finish under sustained contention. Bounded backoff, jitter, fair queueing, workload partitioning, and removing hot spots help. Monitor retry exhaustion and attempts per operation, not only total deadlock count.
Deadlocks and MVCC
MVCC reduces reader-writer blocking by serving older committed versions, but writers still coordinate modifications, uniqueness, foreign keys, and metadata. Two writers can deadlock, and explicit locking reads can introduce dependencies. MVCC changes the shape of contention; it does not eliminate cycles.
Serializable Isolation
Lock-based Serializable can introduce range locks and more opportunities for lock cycles. Serializable snapshot systems can abort transactions because of dangerous dependency patterns even without a conventional lock deadlock. Both outcomes may require a complete retry, but diagnostics and prevention differ.
Distributed Systems
A database sees only resources managed inside its scope. If a transaction holds a database lock while waiting for a message, remote service, distributed mutex, or another database whose operation waits back on the first service, no single lock manager may see the entire cycle.
Avoid holding local transactions across remote calls. Use sagas, outbox patterns, durable state machines, idempotent consumers, timeouts, and a documented global resource order where cross-system coordination is unavoidable.
Performance Impact
A deadlock abort consumes work already performed, delays survivors, triggers retries, and can increase load. Yet raw count alone lacks context: ten short retried deadlocks may be less harmful than one minute-long blocker. Track deadlock rate, rollback cost, retry success, latency percentiles, transaction age, and business failure rate together.
Testing Deadlocks
Use two dedicated sessions and exact barriers to produce the known cycle. Assert that one participant receives the expected vendor signal, the victim is rolled back, the survivor can complete, and the final data preserves the business invariant. Then run the application retry and verify exactly one logical outcome.
Repeat under the production schema, indexes, isolation, triggers, and connection-pool configuration. A simplified table proves the concept but may not reproduce the production resource graph.
Deadlock Test Cases
- Two rows updated in opposite order.
- Three transactions forming a three-resource cycle.
- Read-to-write conversion where the engine supports it.
- Foreign-key parent and child operations.
- Trigger-driven access to another table.
- Range-lock interaction at Serializable.
- Batch updates with overlapping key intervals.
- Victim retry with one stable idempotency key.
- Retry exhaustion under a persistent cycle.
- Connection-pool cleanup after the victim error.
Common Deadlock Mistakes
- Calling every lock wait a deadlock.
- Raising timeouts instead of examining the cycle.
- Retrying only the failed SQL statement.
- Using unlimited immediate retries.
- Assuming one transaction will always be the victim.
- Ignoring triggers, foreign keys, and secondary indexes.
- Holding transactions open during network calls.
- Adding broad locking hints without graph evidence.
- Testing with sleeps instead of deterministic barriers.
- Returning a failed transaction connection to the pool.
Deadlock Prevention Best Practices
- Define and enforce one resource order across all code paths.
- Keep transaction scope as short as correctness allows.
- Prefer atomic conditional SQL and constraints.
- Index predicates and foreign-key access appropriately.
- Process bulk work in stable, bounded batches.
- Choose isolation from the business invariant.
- Capture deadlock graphs or traces automatically.
- Retry complete transactions with bounded jitter.
- Make logical operations idempotent across attempts.
- Load-test hot paths after query or index changes.
Interview Questions and Answers
What is a deadlock?
It is a circular wait in which transactions hold incompatible resources needed by one another and cannot progress without database intervention.
How does a database resolve a deadlock?
It detects a cycle, chooses a victim, rolls back that transaction according to product semantics, releases its locks, and lets survivors continue.
How is blocking different?
Blocking is a one-way wait whose holder can continue independently. A deadlock is a closed dependency cycle.
What is the best prevention technique?
Consistent resource ordering removes circular wait for the covered workflow. Short transactions, proper indexes, atomic writes, and batching further reduce risk.
Why retry the whole transaction?
The victim's previous work was rolled back and its earlier decisions may be stale. Only a fresh transaction can re-establish a valid outcome.
Does MVCC eliminate deadlocks?
No. It reduces many read-write waits, but write locks, constraints, metadata, and explicit locking operations can still form cycles.
Practical Exercises
- Reproduce the two-account deadlock in two sessions.
- Change both transactions to ascending account order.
- Capture the native deadlock graph or trace.
- Remove a useful index and compare the resource graph.
- Add a foreign-key operation to the scenario.
- Implement bounded JDBC retry by vendor code.
- Verify one logical result after a retried operation.
- Measure deadlock and latency changes under load.
Quick Reference
| Concept | Meaning |
|---|---|
| Wait-for graph | Transactions and directed waiting dependencies |
| Cycle | Proof of a deadlock in the dependency graph |
| Victim | Transaction aborted to break the cycle |
| Blocking | Non-circular wait behind a holder |
| Timeout | Wait exceeded a configured duration |
| Resource order | Consistent sequence that prevents common cycles |
| Retry | Fresh execution of the complete transaction |
| Idempotency | Repeated attempts produce one logical effect |
Production Investigation Workflow
- Preserve the graph, trace, codes, and transaction context.
- Identify every participant and resource in the cycle.
- Map statements to application paths and hidden database work.
- Reconstruct acquisition order and transaction duration.
- Inspect actual plans, indexes, isolation, and row counts.
- Choose a fix that breaks the cycle or shortens overlap.
- Add deterministic regression and retry tests.
- Load-test with representative contention.
- Deploy with deadlock, retry, and latency monitoring.
- Confirm business outcomes, not only lower error counts.
Key Takeaway
A SQL deadlock is a circular resource dependency, not simply a slow lock wait. Databases resolve it by aborting a victim, so applications must treat selected deadlock errors as transaction-level failures. Accurate diagnosis comes from a deadlock graph or trace that shows owners, waiters, modes, resources, and acquisition order.
Prevent common cycles with consistent ordering, short transactions, atomic SQL, suitable indexes, bounded batches, and deliberate isolation. Preserve correctness with constraints and complete idempotent retries. Residual deadlocks can then remain a controlled concurrency event rather than an unpredictable production failure.