Concurrency problems in SQL databases can sometimes be confusing because the queries can look correct on their own while still blocking each other when they run at the same time. Two transactions can read valid rows, update valid rows, and follow valid SQL rules, yet still end up waiting in a loop. The database cannot allow that loop to continue forever, so it has to detect the cycle, pick a transaction to stop, roll that transaction back, and let the other transaction move forward. Deadlock detection is the part of the database engine that finds that loop and breaks it before the whole system gets stuck behind waiting locks.
How Deadlocks Form
Before detection can happen, we need a lock situation that cannot finish on its own. During a transaction, the database protects rows and related resources while a statement reads or changes data. Most waits are temporary, and they finish as soon as the blocking transaction commits or rolls back. A deadlock forms when those waits close into a loop, so every transaction in the loop is waiting for a resource held by another transaction in the same loop.
Lock Waits
During a transaction, the database may hold locks on rows, index entries, pages, tables, or other resources tied to the statement. An UPDATE normally needs exclusive access to the row it changes. DELETE needs similar protection for the row being removed. Reads can also participate in blocking, depending on the database engine, isolation level, and statement type. SQL Server, PostgreSQL, MySQL InnoDB, Oracle, and other engines do not all expose the same lock names, but the shared idea is that one transaction owns protected access to something while a conflicting transaction has to wait.
Normal blocking is part of transaction behavior. If one transaction updates a row and has not committed yet, a second transaction trying to update that same row may wait. That wait alone does not mean a deadlock happened because the first transaction still has a way to finish. After it commits or rolls back, the waiting transaction can continue.
This pair of sessions shows normal blocking rather than a deadlock:
-- Session 1
BEGIN TRANSACTION;
UPDATE product_inventory
SET quantity_on_hand = quantity_on_hand - 1
WHERE product_id = 300;
-- Session 1 has not committed yet.-- Session 2
BEGIN TRANSACTION;
UPDATE product_inventory
SET quantity_on_hand = quantity_on_hand - 2
WHERE product_id = 300;
-- This waits until Session 1 commits or rolls back.We can read the first session as the blocker and the second session as the waiter. Session 2 cannot change product_id = 300 while session 1 still holds the conflicting lock, but session 1 does not need anything from session 2. The wait has a natural exit because session 1 can reach the end of its transaction and release the row.
Deadlock risk starts when a transaction keeps going after it has taken one lock and then asks for a second lock that another transaction already owns. If that other transaction also asks for something the first transaction owns, the waiting is no longer one-way. The wait has turned into a circle.
The next pair of sessions updates the same rows in opposite order. The rows are valid, the updates are valid, and the SQL syntax is valid. The problem comes from the order in which the locks are taken.
-- Session 1
BEGIN TRANSACTION;
UPDATE warehouse_bins
SET reserved_units = reserved_units + 5
WHERE bin_id = 10;
UPDATE warehouse_bins
SET reserved_units = reserved_units - 5
WHERE bin_id = 20;
COMMIT;-- Session 2
BEGIN TRANSACTION;
UPDATE warehouse_bins
SET reserved_units = reserved_units + 3
WHERE bin_id = 20;
UPDATE warehouse_bins
SET reserved_units = reserved_units - 3
WHERE bin_id = 10;
COMMIT;We can walk through the timing step by step. Session 1 reaches bin_id = 10 first, so it holds that row lock. Session 2 reaches bin_id = 20 first, so it holds that row lock. Then session 1 asks for bin_id = 20, which session 2 already holds. Session 2 asks for bin_id = 10, which session 1 already holds. Neither transaction can reach COMMIT because both are waiting on the other transaction’s first lock.
That timing detail is why deadlocks can be hard to reproduce. Running session 1 by itself succeeds, and running session 2 by itself also succeeds. Running both back-to-back can succeed too. The failure appears only when both transactions overlap after their first update and before either transaction can finish.
Lock waits can also grow from statements that touch more data than the developer expected. An update with a broad search condition can scan a wider part of the table, which can mean more locked rows or more locked index entries during the statement. The wider the touched range, the more chances there are for a concurrent transaction to touch the same area at the same time.
BEGIN TRANSACTION;
UPDATE shipment_queue
SET status = 'READY'
WHERE region_code = 'WI'
AND status = 'PENDING';
COMMIT;Now we may intend to change only pending shipments for Wisconsin, but the rows touched along the way depend on the table, indexes, data distribution, and query plan. If the database scans a large part of shipment_queue, the statement can interact with more rows than the final changed row count suggests. The transaction can still be valid while carrying a wider lock footprint.
Transaction length also affects how long other sessions must wait. If a transaction opens, updates a row, pauses while the application does extra processing, and then commits later, the database keeps the lock for that full transaction window. The row lock does not disappear just because the UPDATE statement finished.
BEGIN TRANSACTION;
UPDATE customer_credits
SET available_credit = available_credit - 25
WHERE customer_id = 7001;
-- The transaction remains open here.
-- Any conflicting update to this row must wait.
COMMIT;We reduce the chance of lock overlap when the transaction reaches COMMIT quickly. Longer open transactions leave more time for concurrent requests to collide with the same rows or related resources. That does not create a deadlock by itself, but it makes the bad timing easier to hit under load.
The Wait Cycle
Cycle formation is the point where ordinary blocking turns into a deadlock. Waiting for a lock is not enough. The important part is the closed loop. Transaction 1 waits for transaction 2, while transaction 2 waits for transaction 1, or a longer chain eventually circles back to the transaction that started the wait.
The two-account transfer case gives us a compact way to read the mechanics because both transactions touch the same pair of rows in reverse order.
-- Session 1
BEGIN TRANSACTION;
UPDATE account_balances
SET balance = balance - 50
WHERE account_id = 101;
UPDATE account_balances
SET balance = balance + 50
WHERE account_id = 202;
COMMIT;-- Session 2
BEGIN TRANSACTION;
UPDATE account_balances
SET balance = balance - 30
WHERE account_id = 202;
UPDATE account_balances
SET balance = balance + 30
WHERE account_id = 101;
COMMIT;We start with session 1 owning the lock on account 101, then asking for account 202. At the same time, session 2 owns the lock on account 202, then asks for account 101. Neither transaction can reach COMMIT because both are stuck on the second UPDATE. The wait has closed back on itself.
The same type of loop can form across tables. Rows do not need to be in the same table for a deadlock to happen. What matters is that transactions take conflicting resources in a conflicting order.
-- Session 1
BEGIN TRANSACTION;
UPDATE invoices
SET payment_status = 'REVIEW'
WHERE invoice_id = 8001;
UPDATE customer_notes
SET last_reviewed_at = CURRENT_TIMESTAMP
WHERE customer_id = 9001;
COMMIT;-- Session 2
BEGIN TRANSACTION;
UPDATE customer_notes
SET last_reviewed_at = CURRENT_TIMESTAMP
WHERE customer_id = 9001;
UPDATE invoices
SET payment_status = 'REVIEW'
WHERE invoice_id = 8001;
COMMIT;We now have the same cycle, but the resources live in two tables. Session 1 reaches invoices first and then asks for customer_notes. Session 2 reaches customer_notes first and then asks for invoices. If they overlap at the wrong time, each session waits for the other session’s first lock.
Longer cycles can involve three transactions. That version can be harder to read from application logs because no single pair tells the full story. We have to follow the wait chain until it reaches the starting transaction again.
-- Session 1
BEGIN TRANSACTION;
UPDATE route_stops
SET status = 'LOADING'
WHERE stop_id = 11;
UPDATE route_stops
SET status = 'CHECKED'
WHERE stop_id = 22;
COMMIT;-- Session 3
BEGIN TRANSACTION;
UPDATE route_stops
SET status = ‘LOADING’
WHERE stop_id = 33;
UPDATE route_stops
SET status = ‘CHECKED’
WHERE stop_id = 11;
COMMIT;-- Session 3
BEGIN TRANSACTION;
UPDATE route_stops
SET status = 'LOADING'
WHERE stop_id = 33;
UPDATE route_stops
SET status = 'CHECKED'
WHERE stop_id = 11;
COMMIT;We can follow the chain through the held rows. Session 1 holds stop 11 and waits for stop 22. Session 2 holds stop 22 and waits for stop 33. Session 3 holds stop 33 and waits for stop 11. No transaction in that circle can release its first lock because each transaction is blocked before it reaches the end.
Deadlocks are not limited to rows. Some engines can include table locks, page locks, metadata locks, index records, gaps between index records, worker resources, or memory-related resources in a deadlock. The details vary by engine and storage layer, but the shared condition is a circular dependency. One transaction owns a resource and requests another resource, while a transaction somewhere else in the same chain does the same in a conflicting order.
Index access can be part of the story because the database may lock index records while it searches and changes rows. We may write a statement that targets a small set of final rows, but the lock sequence still depends on how those rows are found. Two transactions can reach related data through different access routes and end up taking locks in different orders.
Deadlocks are usually a concurrency problem rather than a syntax problem. The SQL can be well-formed and the data can be valid. The failure comes from overlapping transactions, held locks, and a resource order that lets the waiting chain circle back on itself.
How Detection Responds
Detection starts after a lock wait has reached a point where the database needs to decide if the wait can still finish naturally. Most waits are temporary, so the engine does not treat every blocked statement as a deadlock right away. The important question is not only who is waiting, but who they are waiting on, and whether that chain loops back to a transaction already inside the chain.
The Wait Graph
Inside the database, lock ownership and lock waiting are tracked as part of normal transaction control. When a transaction holds a lock, the database knows which session or transaction owns it. When a second transaction requests a conflicting lock, the database can connect the waiter to the blocker. That connection is the basic building block of a wait graph. We can read the graph as a set of relationships. Transaction 1 is waiting for transaction 2. Transaction 2 is waiting for transaction 3. If transaction 3 is waiting for transaction 1, the chain has circled back. That closed chain is the deadlock. The exact internal structure differs by database engine, but the idea is the same across SQL Server, PostgreSQL, MySQL InnoDB, and other systems that detect deadlocks.
SQL Server has a deadlock monitor that searches for cycles among waiting tasks. PostgreSQL waits for deadlock_timeout before checking for a deadlock, because most lock waits finish without needing a deadlock search. InnoDB performs deadlock detection by default for transaction locks. These engines do not all run the same internal routine, but they are all looking for a circular wait that cannot finish on its own.
We can represent a small wait chain with a plain table to make the idea make a bit more sense:
-- Conceptual view of a wait chain
-- This is not a portable system table query, just a visual.
transaction_id | waiting_for_transaction_id
---------------|----------------------------
101 | 202
202 | 303
303 | 101Reading those rows, we can follow the chain from 101 to 202, then from 202 to 303, and then from 303 back to 101. No transaction in that group can move forward without another transaction in the same group moving first. The database has to break the cycle.
A normal wait would not close the loop:
-- Conceptual view of a normal wait
-- This wait can finish when transaction 202 commits or rolls back.
transaction_id | waiting_for_transaction_id
---------------|----------------------------
101 | 202Transaction 101 is blocked, but transaction 202 can still finish. That means the wait has an exit. The database may let the waiter sit for a while because nothing about this relationship proves that progress is impossible.
Deadlock detection has to separate those two cases. Long waits can still be valid if the blocker is still able to commit or roll back. Deadlocks are different because every transaction in the cycle is blocked by another transaction in that same cycle. Time alone does not define the deadlock; the cycle does.
Victim Choice
After a deadlock is found, the database has to stop one transaction in the cycle. That stopped transaction is commonly called the victim. The database rolls it back so its locks are released, then the remaining transaction can continue. Rolling back a transaction is safer than letting both transactions stay blocked forever, and it keeps the database from accepting half-finished changes.
Victim choice depends on the database engine. SQL Server allows a session to set DEADLOCK_PRIORITY, which affects how likely that session is to be stopped if it becomes part of a deadlock. Lower priority sessions are more likely to lose. If the competing sessions have the same priority, SQL Server compares rollback cost. That cost is tied to the amount of log written by the transaction. If priority and cost are tied, the engine can choose a victim from the tied transactions.
SQL Server code can set priority before the transaction begins:
SET DEADLOCK_PRIORITY LOW;
BEGIN TRANSACTION;
UPDATE billing_batches
SET status = 'PROCESSING'
WHERE batch_id = 8100;
UPDATE billing_batch_items
SET status = 'PROCESSING'
WHERE batch_id = 8100;
COMMIT;We would use a lower priority for a background or retry-friendly operation where losing a deadlock is less harmful than stopping a customer-facing transaction. The setting does not prevent a deadlock. It only affects who gets rolled back if the session becomes part of one.
InnoDB follows a different rule. It tends to choose a small transaction to roll back, where small is based on rows inserted, updated, or deleted. That makes rollback cheaper and frees locks with less undo effort. PostgreSQL aborts one transaction involved in the deadlock after detecting the cycle. Application code should not rely on a fixed victim in PostgreSQL because the practical recovery plan is to treat the deadlock error as retryable when the operation can safely run again.
The application sees the rollback as an error from the database. SQL Server commonly reports error 1205. PostgreSQL reports SQLSTATE 40P01 for deadlock detection. MySQL with InnoDB commonly reports error 1213 with SQLSTATE 40001.
Retry logic belongs outside the transaction that failed. We need to roll back the failed transaction, start a new transaction, and run the unit again only when the operation is safe to repeat. Payment capture, inventory changes, or email sending need more care than a status refresh or queue claim because retrying the database statement can repeat external effects when those effects were not separated from the transaction.
This Java-style outline keeps the database retry boundary around the transaction itself:
int attempts = 0;
int maxAttempts = 3;
while (attempts < maxAttempts) {
attempts++;
try {
connection.setAutoCommit(false);
updateBatchStatus(connection, 8100L);
insertBatchEvent(connection, 8100L);
connection.commit();
break;
} catch (SQLException ex) {
connection.rollback();
if (!isDeadlock(ex) || attempts == maxAttempts) {
throw ex;
}
try {
Thread.sleep(100L * attempts);
} catch (InterruptedException interrupted) {
Thread.currentThread().interrupt();
throw ex;
}
} finally {
connection.setAutoCommit(true);
}
}The retry starts a fresh transaction after rollback. Reusing the failed transaction would not make sense because the database has already ended it as the victim. The small delay also helps avoid every retry colliding again at the same instant.
Query Order
Reducing repeat deadlocks usually starts with the order of data access. If two parts of the application touch the same resources in different orders, they can block each other in a circle. If they touch those resources in the same order, one transaction may wait, but the wait is less likely to become circular. We can use account transfers as the common case. If transfers can update two accounts in either direction, the application should not update from-account first in some places and to-account first in other places. We can sort the account ids and update the lower id first every time.
BEGIN TRANSACTION;
UPDATE account_balances
SET balance = balance - 75
WHERE account_id = 120;
UPDATE account_balances
SET balance = balance + 75
WHERE account_id = 450;
COMMIT;If a reverse transfer runs at the same time, it should still lock account 120 first and account 450 second. That does not mean both transactions run without waiting. It means the wait is more likely to be one-way because they ask for the shared resources in the same order.
The same rule applies across tables. If one workflow updates customers, then orders, then order_events, related workflows should avoid touching those tables in a different order for the same business operation. Centralizing that order in a stored procedure or a shared service method can help keep the transaction sequence consistent across the application.
A stored procedure can make the order explicit:
CREATE PROCEDURE mark_order_for_review
@customer_id BIGINT,
@order_id BIGINT
AS
BEGIN
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE customers
SET review_required = 1
WHERE customer_id = @customer_id;
UPDATE orders
SET status = 'REVIEW'
WHERE order_id = @order_id
AND customer_id = @customer_id;
INSERT INTO order_events (order_id, event_name)
VALUES (@order_id, 'REVIEW_REQUESTED');
COMMIT;
END;We can read the procedure as a contract for lock order. Any caller that uses it follows the same sequence. The database may still choose execution plans internally, and different engines have different locking details, but keeping the logical order consistent removes a common source of circular waits.
Bulk updates need the same care. If two jobs process the same set of ids in random or incoming file order, they may take locks in different sequences. Sorting the ids before running the update can lower the chance of two transactions meeting the same rows in reverse order.
BEGIN TRANSACTION;
UPDATE shipment_tasks
SET status = 'CLAIMED'
WHERE task_id IN (
SELECT task_id
FROM pending_task_claims
WHERE worker_name = 'Alex'
);
COMMIT;That style depends on how the engine chooses to access rows. For high-contention queues or batch claims, the safer plan is to claim a small, ordered batch and commit quickly. The exact SQL differs by database engine, but the transaction rule stays the same. Keep the lock sequence stable and keep the amount of locked data small.
Index Coverage
Indexes can reduce deadlock repeats because they influence how much data a statement has to touch before it finds the rows it needs. Without a helpful index, the database may scan a wider part of a table. That wider scan can hold or request more locks, interact with more concurrent transactions, and keep the transaction active longer than expected.
We can start with a statement that updates pending invoices for one customer:
UPDATE invoices
SET status = 'PAID'
WHERE customer_id = 1001
AND status = 'OPEN';If the table has no useful index for customer_id and status, the database may need to inspect a large number of rows to find the matching invoices. The final changed rows may be small, but the search can still bring the statement into contact with much more data.
The optimizer can get a narrower route to the target rows with a matching index:
CREATE INDEX idx_invoices_customer_status
ON invoices (customer_id, status);With that index available, the database has a better chance of reaching the intended rows through the filtered values. The index does not guarantee that deadlocks disappear, and the optimizer still decides the final plan, but the lock footprint can shrink when the engine can find rows without scanning large unrelated ranges.
Index coverage also affects delete and update statements that filter by status, date, tenant, account, or queue state. Busy queue tables are a common place where missing or poorly matched indexes can create wide scans. When several workers poll and update the same queue table, broad scans raise the chance that two transactions will cross paths.
UPDATE job_queue
SET status = 'RUNNING',
locked_at = CURRENT_TIMESTAMP
WHERE status = 'READY'
AND queue_name = 'email';The queue lookup can search a smaller area with a supporting index:
CREATE INDEX idx_job_queue_lookup
ON job_queue (queue_name, status, created_at);The column order should match the way the application filters and claims jobs. For a queue that filters by queue_name and status, then reads older jobs first, an index beginning with those filter columns and then created_at can make the claim query more targeted. The best index still depends on the database engine, table size, data distribution, and query plan, so the plan should be checked in the target database.
Shorter transactions belong with index coverage because both reduce the time locks are held. If a transaction does database changes and then waits on an external call before committing, every conflicting transaction has to wait longer. Keeping outside calls away from the open transaction cuts down the lock window.
BEGIN TRANSACTION;
UPDATE support_tickets
SET status = 'ASSIGNED',
assigned_to = 'Kaitlyn'
WHERE ticket_id = 6100;
INSERT INTO support_ticket_events (ticket_id, event_name)
VALUES (6100, 'ASSIGNED');
COMMIT;After the commit, the application can send a notification or call another service if that step is part of the workflow. The database transaction stays focused on the data changes that must commit as a unit.
Isolation level also changes lock behavior. Higher isolation levels can hold certain locks longer or add locking that lower levels avoid. That does not make higher isolation wrong. It means the isolation level should match the data rule the transaction needs to protect. Some engines also offer row-versioning behavior that can reduce read-write blocking for certain workloads, while write-write conflicts still need transaction control.
Index coverage, transaction length, access order, and retry handling all affect deadlock frequency from different angles. Better indexes narrow the search. Short transactions reduce lock time. Stable query order lowers circular waits. Retry handling lets the application recover when a deadlock still happens.
Conclusion
Deadlock detection comes down to finding a lock cycle that cannot finish on its own. The database tracks which transaction holds a resource, which transaction is waiting, and where that wait chain circles back. After the cycle is found, one transaction is rolled back so its locks are released and the remaining transaction can continue. Repeat deadlocks usually point to transaction mechanics that need tightening, such as inconsistent row order, wide scans from missing indexes, long open transactions, or retry logic that does not restart from a fresh transaction boundary.
