Two transfers wait for each other

The ticket example made one update wait for another. A transaction lock records which transaction is allowed to access a protected item in a particular way. Here we use exclusive locks: only the holder may change the locked account.

Two accounts, 17 and 22, participate in opposite transfers. Each transaction needs both accounts and holds its acquired locks until it commits or rolls back. What happens if they acquire them in opposite orders?

Follow the wait cycle

Transaction A
Holds account 17
Needs account 22
Waits for B

Transaction B
Holds account 22
Needs account 17
Waits for A

A cannot finish until B releases its lock, and B cannot finish until A releases its lock. Waiting alone cannot resolve the cycle.

This is deadlock. The engine can represent waiting relationships as a directed graph: an arrow from A to B means A waits for B. A cycle exposes a circular dependency.

A deadlock detector can choose a transaction to abort, undo its tentative effects under the recovery rules, and release its locks. The other transaction can then proceed. Retrying the aborted transaction is new work and must use fresh observations.

Prevent this cycle with one order

If both transfers request account 17 before account 22, only one can acquire 17 first. The other waits without holding 22. The first transaction can obtain 22, finish, and release both locks.

The rule is based on account identity, not transfer direction. “Source account first” recreates the opposite orders because each transfer has a different source. A consistent ordering prevents this particular class of cycle; the full engine must account for every protected resource involved.

Locks need compatibility rules. A shared lock can allow multiple readers while excluding conflicting writers; an exclusive lock blocks incompatible access. The engine also needs a lock lifetime protocol. Acquiring a lock briefly and releasing it before a dependent decision completes may not provide the desired isolation.

Protect more than existing rows when necessary

A request such as “there are no bookings in this time range” depends on absence, not just on rows already found. Locking the existing rows alone may not stop another transaction inserting a new booking into that range. Serializable execution may require range protection or another mechanism that detects the conflict. Row locks are not a universal substitute for an isolation design.

Retry one transfer

The detector aborts B. Should B retry using balances it read before the abort?

Discard the abandoned attempt's assumptions

No. A may have changed those balances before B’s new attempt runs. B should start a new transaction and read under the new attempt’s rules. Reusing stale application-side values can defeat the protection even though lock acquisition succeeds.

Locks coordinate access by making conflicting work wait or fail. The report example next shows why consistent reading is a related but distinct question.

PostgreSQL’s locking documentation discusses deadlocks and consistent lock acquisition order. Its real lock modes are richer than our two-account model.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter