Locking & deadlocks

Shared and exclusive locks

A database lock controls whether concurrent transactions may use the same resource in conflicting ways. Two common names are shared lock and exclusive lock. Under SQL Server's pessimistic concurrency control, multiple transactions may hold shared locks to read a resource. Another transaction cannot acquire an exclusive lock on that resource while those shared locks remain. An exclusive lock protects a modification from another concurrent modification.

Those names are useful vocabulary, but they are not a portable promise that every ordinary SELECT takes and holds the same lock. PostgreSQL uses MVCC for ordinary reads, and SQL Server changes read behavior when row versioning is used. Start with the conflict: which resource does a transaction hold, and which incompatible access is another transaction requesting?

Wrong "A shared lock means exactly one reader owns the row."

Right Shared locks are compatible with other shared locks on the same resource. They conflict with an exclusive change to that resource while they remain held.

Row locks and table locks change the blast radius

Lock granularity is the size of the protected resource. A row-level lock can let transactions change different rows concurrently. A table-level lock covers a much larger resource, so a conflicting transaction may have to wait even when it wants a different row.

Smaller granularity is not free. SQL Server's documentation says its engine normally chooses locking granularity dynamically and warns that forcing a locking level can significantly reduce concurrent access. PostgreSQL also has separate table-level and row-level lock facilities. Its table lock mode named ROW EXCLUSIVE is still a table-level mode, despite the historical name.

Wrong "Row locks are always better because they block less data."

Right Row locks can permit more concurrency. The engine may choose a broader granularity when the access pattern or lock-management cost favors it.

Blocking is not yet a deadlock

Suppose transaction A holds an exclusive lock on account 1 and transaction B requests a conflicting lock on account 1. B waits. A can still commit, release its lock, and let B continue. This is ordinary blocking.

A deadlock needs a cycle. This pair of transactions can create one:

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- Transaction B, running concurrently
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;
UPDATE accounts SET balance = balance + 20 WHERE id = 1;

If A locks account 1 and B locks account 2, A waits for B while B waits for A. Neither transaction can reach COMMIT and release what the other needs.

Wrong "A query that waits long enough becomes a deadlock."

Right Duration does not define a deadlock. The defining condition is a cycle of dependencies.

Prevent the opposite order

Both paths should acquire the account rows in the same order. Transfer direction should not decide lock order. A stable key should:

BEGIN;

-- Both transfer paths request the lower ID first.
SELECT id, balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT id, balance FROM accounts WHERE id = 2 FOR UPDATE;

UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

COMMIT;

MySQL's InnoDB documentation recommends using the same order of operations when transactions update multiple rows or tables. It also requires applications to handle a deadlock victim by retrying the transaction. Ordering reduces deadlocks; recovery code still has to expect them.

Compatibility matters more than the lock name

For the common shared (S) and exclusive (X) vocabulary, ask whether two requested modes are compatible on the same resource:

This compact table matches SQL Server's pessimistic locking behavior. It is not a complete compatibility table for every database. SQL Server also has update and intent modes. PostgreSQL exposes several table and row modes with their own conflict matrices.

An update lock in SQL Server addresses a particular conversion problem. If two transactions retain shared locks on one resource and both later request exclusive locks, each conversion can wait for the other's shared lock. SQL Server permits only one update lock on a resource, then converts it to exclusive when the update happens.

Wrong "Shared locks cannot participate in a deadlock because readers are compatible."

Right Two retained shared locks can be compatible initially and later deadlock when both transactions try to convert to exclusive locks.

A row operation can involve more than one lock level

SQL Server uses intent locks higher in its resource hierarchy. For example, an intent exclusive lock at the table level signals exclusive locks on lower-level resources. The engine can reject an incompatible table lock without checking every row lock first.

This is why UPDATE one row and lock only one row are not interchangeable descriptions. PostgreSQL data-changing commands acquire a table-level ROW EXCLUSIVE mode on the target table, in addition to row-level effects. MySQL InnoDB locking reads also lock associated index entries for the rows they encounter.

Avoid forcing table locks as a generic performance fix. SQL Server normally chooses granularity dynamically and documents that table-only locking on a heavily used table can create a bottleneck.

Pessimistic locking makes the conflict explicit

Use a locking read when the transaction must prevent another transaction from changing rows before your later write. In MySQL 8.4, SELECT ... FOR SHARE lets other sessions read selected rows but prevents modification until the transaction ends. SELECT ... FOR UPDATE locks selected rows and associated index entries as an update would. Both release their locks at commit or rollback.

Here is the protected read-then-write shape:

START TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;

UPDATE accounts
SET balance = balance - 25
WHERE id = 42;

COMMIT;

An ordinary MVCC read has different semantics. InnoDB documents that a consistent read ignores locks on records that are visible to its read view. The locking read asks to coordinate with current writers, so it can wait.

Wrong "MVCC makes SELECT FOR UPDATE a non-blocking snapshot read."

Right MVCC supports versioned reads, while FOR UPDATE explicitly requests locks for coordination with changes.

Ordering and retry solve different parts

For transactions that need several resources, define one total order and use it in every code path. Sorting the identifiers before requesting locks makes the rule executable:

const accountIds = [sourceAccountId, destinationAccountId].sort(
  (left, right) => left - right,
);

await db.transaction(async (tx) => {
  for (const accountId of accountIds) {
    await tx.query(
      "SELECT id FROM accounts WHERE id = ? FOR UPDATE",
      [accountId],
    );
  }

  await applyTransfer(tx, sourceAccountId, destinationAccountId, amount);
});

Ordering prevents the known opposite-order cycle. It does not justify omitting error handling. InnoDB automatically detects deadlocks when detection is enabled and rolls back a victim. Its error-handling documentation requires retrying the whole transaction after that rollback, not the final statement in isolation.

Read the wait cycle, not the slowest statement

A deadlock report describes owners, waiters, and resources. Model each blocked transaction as depending on the transaction that owns the incompatible resource. A cycle in those dependencies is the evidence of deadlock.

Detection policy belongs to the engine. InnoDB has automatic deadlock detection enabled by default and rolls back a victim. SQL Server's lock monitor searches dependencies for a cycle, chooses a victim, rolls back its transaction, and returns an error to the application. SQL Server can consider deadlock priority and rollback cost. These details should not be generalized into one universal victim algorithm.

Wrong "The transaction that started last is always the victim."

Right Victim selection is engine-specific. Build recovery around the documented deadlock error, not an assumed winner.

A deadlock and a lock timeout are also different diagnoses. A timeout says a request waited beyond a configured limit. It does not prove that the dependencies formed a cycle. InnoDB can use its lock-wait timeout to recover when deadlock detection is disabled, but that configuration does not redefine every timed-out wait as a detected deadlock.

Lock footprint follows access paths

In MySQL 8.4 InnoDB, a locking read, UPDATE, or DELETE generally locks each index record scanned by the statement. InnoDB records scanned index ranges rather than the exact WHERE predicate. Its deadlock guidance recommends indexes on columns used by SELECT ... FOR UPDATE and UPDATE ... WHERE statements.

This gives a concrete review task. Check the access path as well as the number of rows returned:

CREATE INDEX orders_customer_status
  ON orders (customer_id, status);

START TRANSACTION;

SELECT id
FROM orders
WHERE customer_id = 17 AND status = 'pending'
FOR UPDATE;

-- Change the rows selected and locked above.
UPDATE orders
SET status = 'processing'
WHERE customer_id = 17 AND status = 'pending';

COMMIT;

The index recommendation is not a promise that this statement locks only the rows it returns. InnoDB documents record, range, and gap behavior whose details depend on the statement and configuration. The safe claim is narrower: reducing the scanned index range can reduce the set of index records considered for locking.

Wrong "A selective WHERE clause proves that only the matching rows are locked."

Right For InnoDB, inspect the index range scanned. The written predicate alone does not describe the lock footprint.

MVCC narrows contention but does not replace locks

PostgreSQL's MVCC model lets each SQL statement see a database snapshot. Its ordinary reads do not conflict with row writes, so readers do not block writers and writers do not block readers. PostgreSQL still provides table and row locks for cases where snapshot visibility is insufficient for application coordination.

The distinction is semantic:

-- Versioned read: observes rows visible to the statement's snapshot.
SELECT balance FROM accounts WHERE id = 42;

-- Locking read: coordinates with transactions changing this row.
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;

MVCC removes many reader-writer waits. Writers can still contend for the same rows, and transactions can still acquire write locks in a cycle. MySQL's InnoDB deadlock documentation gives opposite-order updates as a direct deadlock case even though InnoDB uses multiversion consistent reads.

Wrong "Choosing MVCC instead of pessimistic locking eliminates deadlocks."

Right Ordinary versioned reads and explicit pessimistic locks can coexist. Write conflicts and locking reads still need ordering, deadlock detection, and retry handling.

Make the ordering contract cover every path

"Lock consistently" is incomplete unless the order is stable for batches, transfers, background jobs, and maintenance code. Put the order in a shared boundary that sorts before any lock is taken:

async function lockAccounts(tx: Transaction, ids: number[]) {
  const orderedIds = [...new Set(ids)].sort((left, right) => left - right);

  for (const id of orderedIds) {
    await tx.query(
      "SELECT id FROM accounts WHERE id = ? FOR UPDATE",
      [id],
    );
  }
}

Keep the transaction short after the first lock. InnoDB's guidance recommends small transactions that do not remain open for long periods. It also says applications must remain prepared to reissue a transaction after a deadlock rollback. A bounded retry handles the victim; the deadlock report and access paths explain what should be redesigned when cycles are frequent.