Two transactions need the same records. One waits while the other finishes, and then continues. That is ordinary blocking. A deadlock is different: each transaction holds something the other needs, forming a cycle of waiting that cannot resolve simply by waiting longer.
Databases commonly detect such a cycle and abort one participant so the others can continue. Your application therefore needs both sensible transaction design and a way to handle a retryable failure. This guide explains the sequence with two sessions and two rows. Run concurrency experiments only in a disposable database; exact error messages and locking behavior depend on the engine and configuration.
What locks protect
Locks coordinate conflicting operations on database resources. Depending on the engine and operation, resources may include rows, index entries, key ranges, pages, tables or schema objects. “Row lock” is a helpful starting idea but not a complete description of every database lock.
Readers and writers interact differently under different isolation and snapshot settings. A read may use a snapshot, acquire shared locks or wait for a writer. An update may lock index entries as well as the row being changed. A broad scan in a modifying statement can therefore create contention beyond the few rows the application expected to affect.
Lock duration is often related to the transaction boundary. A transaction that updates one record and then waits for a remote HTTP service can hold resources throughout that wait. Moving avoidable external work outside the locked section reduces the time other transactions must wait.
Blocking alone is not necessarily a defect. Waiting can preserve a required rule. Investigate whether the wait is unusually long, whether the transaction is unnecessarily broad and whether the application handles timeout and retry behavior correctly.
Prevent cycles with consistent resource order
When several transactions need the same set of resources, acquire them in a consistent order. For account transfers, determine the lower account ID and lock or update it before the higher ID in every transfer path. Opposite transfer directions should still follow the same locking order.
This reduces a common deadlock pattern, but it is not a guarantee that the database can never deadlock. Foreign keys, triggers, additional indexes and other application paths may touch resources you did not consider. Treat deadlock handling as a normal reliability requirement even after improving the main sequence.
Keep transactions short and avoid changing more rows than necessary. Suitable indexes can reduce the work and lock footprint of a modifying query. However, additional indexes also create maintenance work, and an index alone does not repair an inconsistent resource order.
Avoid solving the problem by silently lowering isolation or ignoring errors. The original transaction may exist to protect balances, stock or uniqueness. A change that removes waiting but permits an invalid business outcome trades a visible operational issue for incorrect data.
Diagnose and retry the complete operation
Use the engine's lock and deadlock diagnostics to identify the participating statements and resources. MySQL InnoDB, PostgreSQL and SQL Server provide different facilities. Capture enough context to connect the statements with an application operation while excluding sensitive data from shared logs.
After a deadlock victim error, the transaction state depends on the engine. Explicitly reset or roll back as required before retrying. Retry the complete logical unit of work with a bounded attempt count and a delay policy, often with jitter to reduce repeated collisions. Do not rerun only the final statement using observations from an aborted transaction.
External actions require separate care. If a transaction path already sent an email or called a payment provider, rerunning that code can repeat the external action. Use idempotency keys and a designed coordination pattern for operations that cross the database boundary.
The MySQL InnoDB deadlock reference describes its detection and application implications. Check your engine's documentation for victim handling, lock timeout distinctions and diagnostic facilities.
Practice: remove the opposite ordering
Rewrite the two-session experiment so both sessions update account 1 before account 2. Repeat it in a test environment. The second session should commonly wait at the first contested resource instead of obtaining account 2 and completing a cycle. After the first commits, the waiting transaction can proceed.
Record the wait separately from a deadlock error. A successful result still involves contention, because both transactions modify the same accounts. If the wait remains too long, inspect what happens between the first update and commit.
Then add application retry handling in pseudocode: start transaction, perform both updates, commit, catch a recognized retryable database error, roll back or reset, delay, and start a fresh attempt. Limit retries and return a clear failure when the limit is reached. A loop that retries forever can overload the system during a persistent problem.
SQL Deadlocks and Locking Examples
Each example shows the code, the output it produces and what each part does.
A two-row deadlock sequence
Prepare this table once in a test PostgreSQL or MySQL InnoDB database. Use BEGIN TRANSACTION for the SQL Server spelling. SQLite has different writer coordination and will not reproduce this same row-lock cycle.
In session A, begin and change account 1. In session B, begin and change account 2. Then let A request account 2 and B request account 1. Follow the order manually in two connections.
Clean up by explicitly rolling back both sessions after the experiment. Do not treat this combined listing as one script to run in a single connection: two independently open transactions are essential to the scenario.
CREATE TABLE lock_accounts (
account_id INTEGER PRIMARY KEY,
balance INTEGER NOT NULL
);
INSERT INTO lock_accounts VALUES (1, 100), (2, 100);-- Session A, first step
BEGIN;
UPDATE lock_accounts SET balance = balance + 1 WHERE account_id = 1;
-- Session B, first step in the other connection
BEGIN;
UPDATE lock_accounts SET balance = balance + 1 WHERE account_id = 2;
-- Session A, second step
UPDATE lock_accounts SET balance = balance + 1 WHERE account_id = 2;
-- Session B, second step
UPDATE lock_accounts SET balance = balance + 1 WHERE account_id = 1;The first second-step request can wait for the other transaction.
Once the waiting cycle forms, the engine can choose a deadlock victim.
One session receives a deadlock error; the exact message and victim vary.SQL Deadlocks and Locking FAQ
Is every lock wait a deadlock?
No. A wait can end normally when another transaction releases its resources. A deadlock contains a cycle of dependencies.
Which transaction becomes the victim?
That is engine-dependent. Do not assume the newer transaction or a particular account always loses.
Will a larger timeout fix a deadlock?
Waiting longer does not break a circular dependency. Detection, aborting a participant and safe retry are the relevant mechanisms.
How can I find out what caused a deadlock?
Check the database's deadlock report: PostgreSQL writes details to its log, MySQL shows the latest one in SHOW ENGINE INNODB STATUS, and SQL Server records deadlock graphs in its system health session. They show which statements and rows each transaction was waiting for.
Do indexes help prevent deadlocks?
Often, yes. Without a suitable index, an UPDATE or DELETE may scan and lock many more rows than it changes, increasing the chance that two transactions collide. Indexing the filtered columns keeps locks small and transactions short.
SQL Deadlocks and Locking Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Two transfer paths update the same account pair in opposite orders. Which change reduces a common deadlock pattern?
SQL Deadlocks and Locking Quiz
2 questions to check what you learned.
What distinguishes a deadlock from ordinary blocking?
- Any query lasting more than one second
- Every use of a transaction
- A cycle where transactions wait on resources held by one another
What should a deadlock retry usually repeat?
- The complete logical transaction with fresh reads
- Only the failed final statement using stale values
- Every external payment without an idempotency policy