Chapter

Locks, Deadlocks and Safe Retries

Part D · Correct Changes|3,821 words|about 17 min read|Volume D

24.0 What this chapter gives you#

A lock is a coordination mechanism, not a synonym for failure. Waiting can be the correct way to protect a record; waiting forever or repeating the wrong operation is not.
This chapter separates lock scope, compatibility, blocking, deadlock, timeouts and retries. You will draw a wait graph, choose a consistent acquisition order and identify effects that must not be repeated blindly.
Lock names and error codes are implementation-specific. PostgreSQL 17 examples are documented illustrations. The supplied SQLite exercises use fresh teaching databases and do not establish PostgreSQL lock behaviour.

24.1 What is locked#

24.1.1 PLAIN — in simple words#

A lock reserves a kind of access to a resource while an operation is in progress. The resource might be a row, a table or an application-defined coordination key.
Saying “the database is locked” hides the important details. Which operation is waiting, which resource does it need, who currently holds conflicting access, and when should that holder finish?
A lock is effective only within the protocol that honours it. An application-defined lock does not magically stop an unrelated writer that ignores that agreement.

24.1.2 PLAIN — a picture in your head#

A staff member places a sign on a particular filing drawer while correcting its contents. Other staff follow the drawer-access rule until the sign is removed.
Locking the drawer is different from closing the entire room. A room closure affects far more work.
Where the comparison breaks: database locks are not all exclusive signs, and normal readers may use older visible row versions without waiting for a writer. Some lock names also describe tables even when they contain the word “row.”

24.1.3 PLAIN — a worked example#

Request A is updating product LOCK-PEN. Request B wants to update the same product. Request C is reading an unrelated product. A useful diagnosis identifies whether B conflicts with A and whether C needs a conflicting resource at all.
A table-wide schema change may have a broader lock requirement than the ordinary row update. It can therefore wait behind activity that would not block another ordinary row change.
Record the actual lock mode and object identifier in an engine investigation. Do not infer a table lock from a slow screen or infer a row lock solely from a statement’s WHERE clause.
The example names are synthetic; it does not assert a particular observed server lock trace.

24.1.4 PLAIN — what is really happening inside#

The engine checks requested access against existing incompatible locks. It may grant the request, queue it, time it out or later select a transaction for abort.
Locks can be acquired implicitly by SQL statements or explicitly through locking clauses. A statement may acquire more than one kind of lock.
The business resource and engine resource need not match one-to-one. Protecting “remaining branch capacity” requires identifying the concrete record or protocol representing that capacity.

24.1.5 TECHNICAL — the engineer’s version#

PostgreSQL 17 has table-level and row-level lock modes. Names such as ROW EXCLUSIVE in the table-lock family still denote table-level locks; the name alone does not establish row granularity. [S118]
PostgreSQL advisory locks coordinate application-defined resources. Their usefulness depends on every relevant participant following the convention; they are not substitutes for ordinary data constraints or authorization. [S118]
SQLite’s database-level writer coordination differs substantially from PostgreSQL row-locking patterns. Describe its transaction and journal mode explicitly rather than translating a PostgreSQL lock diagram unchanged. [S54]

24.1.6 WORDS — remember these#

Lock scope: the resource access being coordinated — the row, table, key or other engine/application object covered by a lock. Lock holder: the participant currently retaining protected access — the session or transaction owning a granted lock. Advisory lock: a coordination agreement writers must choose to follow — an application-defined locking facility not automatically tied to every relevant data mutation.

24.2 Lock modes and duration#

24.2.1 PLAIN — in simple words#

Not every lock excludes every other lock. A compatibility rule determines which kinds of access may coexist.
Duration matters as much as scope. A brief conflict can be harmless; a transaction left open while someone answers a phone can hold resources long after useful database work stops.
Know what releases a lock. Transaction completion, statement completion, session termination and explicit release are different boundaries.

24.2.2 PLAIN — a picture in your head#

A reading room may allow several readers at once but stop new visitors during a structural repair. The rules depend on what each person is doing, not just whether someone is present.
A repair permit that lasts until the repair finishes is different from a building key retained by a staff member all day.
Where the comparison breaks: an engine’s compatibility matrix is exact and product-specific. Everyday “reader” and “writer” labels are not precise enough to reproduce all lock modes.

24.2.3 PLAIN — a worked example#

In PostgreSQL 17, an ordinary SELECT obtains ACCESS SHARE on its referenced table. ACCESS EXCLUSIVE conflicts with it; most other table-lock modes do not conflict with ACCESS SHARE.
Thus a schema operation requiring ACCESS EXCLUSIVE can be delayed by a long-running reader, even when that reader is not changing any row.
Conversely, a row locked for an update does not necessarily block an ordinary MVCC SELECT from reading an appropriate visible version. “A writer exists” is not enough to predict every reader’s wait. [S118]
The practical response is to inspect the actual requested and held modes, not to memorize that all readers either always block or never block writers.

24.2.4 PLAIN — what is really happening inside#

Transaction-scoped locks generally remain until the transaction ends, subject to documented savepoint and implementation behaviour.
Application code can accidentally lengthen that interval through network calls, slow loops, user interaction or an abandoned transaction in a pooled connection.
Session-level advisory locks have different lifetime rules from transaction-level advisory locks. Returning a connection to a pool without clearing session state can pass unwanted coordination state to another logical request.

24.2.5 TECHNICAL — the engineer’s version#

PostgreSQL distinguishes session-level advisory locks from transaction-level advisory locks. Session-level advisory locks do not follow ordinary transaction rollback semantics; transaction-level advisory locks are released at transaction end. [S118]
Define a maximum intended transaction duration and observe time spent idle inside transactions separately from active query work. A short individual query does not imply a short lock-holding transaction.
Savepoint rollback can release locks acquired after that savepoint under PostgreSQL’s documented rules. Do not extrapolate this into a claim that any arbitrary external resource acquired in the same code block is also released. [S118] [S121]

24.2.6 WORDS — remember these#

Lock compatibility: which access modes can coexist — the engine’s conflict relation between requested and held lock modes. Lock lifetime: how long protected access remains held — the release boundary determined by transaction, statement, session or explicit protocol. Idle in transaction: a transaction remains open without active useful work — a state that can retain locks and snapshots while the application waits elsewhere.

24.3 Blocking versus deadlock#

24.3.1 PLAIN — in simple words#

Blocking means one operation is waiting for access held incompatibly by another. The holder may finish normally and release the resource.
A deadlock is a cycle of waits: each participant needs something held by another participant in the cycle, so none can finish without intervention.
A long wait is not automatically a deadlock. It may be an overloaded server, a slow holder, an abandoned transaction or an external dependency.

24.3.2 PLAIN — a picture in your head#

Mira waits for Dev to finish using the label printer. Dev is printing and will finish. That is blocking.
Now Mira holds the label printer and needs Dev’s scanner, while Dev holds the scanner and needs Mira’s printer. Neither releases the held device until acquiring the other. That is a deadlock in this model.
Where the comparison breaks: databases can detect some lock cycles and abort a participant. A cycle spanning outside services may not be visible to one database’s detector.

24.3.3 PLAIN — a worked example#

Let A hold resource X and request Y. Let B hold Y and request X. Draw an arrow from the waiter to the holder: A → B and B → A.
The arrows form a cycle. Neither can obtain its requested resource while both retain their current locks under the stated protocol.
Compare A → B → C with no arrow returning from C. That chain is blocking, not a cycle. C may complete and release the chain.
The companion graph exercise checks directed cycles in a small synthetic wait graph. It is not a full model of an engine’s lock manager, lock upgrades or distributed deadlock detection.

24.3.4 PLAIN — what is really happening inside#

A database may inspect waits and choose a victim transaction to abort. Releasing that transaction’s locks lets another participant continue.
The application must handle the victim’s failure even when its business request was otherwise valid. It should not assume that the first or newest request is always chosen.
A statement timeout or lock timeout can terminate waiting without proving that a cycle existed. Preserve the actual error classification in logs and retry decisions.

24.3.5 TECHNICAL — the engineer’s version#

A wait-for graph represents transactions as vertices and incompatible-resource waits as directed edges. A cycle is the key condition in the simplified single-resource ownership model used here.
PostgreSQL automatically detects deadlocks and aborts one involved transaction. Its documentation advises against relying on a predictable choice of victim. [S118]
A client-side deadline, server statement timeout, lock timeout and detected deadlock are different events. Keep their error codes and transaction-state consequences separate instead of collapsing them into “database busy.”

24.3.6 WORDS — remember these#

Blocking: waiting for conflicting access to be released — a resource wait that need not contain a dependency cycle. Deadlock: a cycle of participants waiting on one another — a condition requiring some participant to release, abort or otherwise break the cycle. Wait-for graph: a map of who waits for whom — a directed graph used to reason about resource dependencies.

24.4 Detection and ordering#

24.4.1 PLAIN — in simple words#

A consistent rule for acquiring multiple resources can prevent many deadlocks. Everyone asks for them in the same agreed order instead of choosing an order independently.
This works only when the protocol is complete. Hidden trigger work, alternate code paths or lock upgrades can introduce additional dependencies.
Prevention and recovery belong together. Even a carefully ordered application should handle deadlock errors because other participants and maintenance operations may not follow the same small model.

24.4.2 PLAIN — a picture in your head#

Whenever two drawers are needed, staff open the lower-numbered drawer first. Someone may wait, but two staff following that rule cannot each hold the higher drawer while waiting for the lower one in the simple two-drawer model.
The order gives everyone a shared coordination language.
Where the comparison breaks: real statements can touch rows through plans, constraints or triggers. Sorting application IDs is not by itself a complete proof of all engine lock acquisition orders.

24.4.3 PLAIN — a worked example#

A transfer-like teaching operation needs accounts AC-02 and AC-09. A second operation needs the same pair in the opposite business direction.
Both acquire their required coordination locks in key order: AC-02, then AC-09. One may wait for AC-02 before holding AC-09, avoiding the earlier reversed-order pattern.
Business direction remains independent of lock order. The destination account can be locked first if its key sorts first; locking order does not change the meaning of the transfer.
Include complete scoped keys in the ordering rule. Ordering only by a local number while ignoring tenant or branch can create ambiguous protocol descriptions.

24.4.4 PLAIN — what is really happening inside#

A common acquisition order removes certain cycles from the allowed protocol. It is a structural argument, not merely an attempt to make collisions less likely.
Keep transactions short after acquiring locks. Holding the first lock while performing unrelated expensive work increases waiting even if no cycle forms.
During diagnosis, capture the waiting statement, holder, transaction age, lock objects and recent changes. Avoid killing arbitrary sessions without understanding the rollback and business-outcome consequences.

24.4.5 TECHNICAL — the engineer’s version#

PostgreSQL recommends acquiring locks on multiple objects in a consistent order and acquiring the most restrictive needed mode first where appropriate. This reduces common deadlock patterns but does not eliminate the need for error handling. [S118]
Document explicit locking SQL separately from the business updates. Include trigger, foreign-key and index-related interactions in a real review rather than treating them as invisible implementation magic.
A diagnostic procedure should favour bounded observation before intervention. Record which transaction was cancelled, which request it represented and how its outcome will be reconciled after rollback or connection loss.

24.4.6 WORDS — remember these#

Acquisition order: the sequence in which resources are requested — a shared ordering protocol intended to prevent cyclic waits. Lock upgrade: requesting stronger access while already holding weaker access — a mode transition that can introduce additional conflicts. Deadlock victim: the transaction selected to break a cycle — a participant aborted by the engine so other work can proceed.

24.5 Bounded retries#

24.5.1 PLAIN — in simple words#

A retry is another attempt at the same intended operation, not permission to repeat every action blindly until something works.
Some failures are temporary, such as a chosen serialization conflict. Others are permanent until the input or policy changes, such as an invalid quantity or an incompatible request-key reuse.
A retry needs a stopping rule. Without one, a failing system can spend more and more resources repeating work while the caller receives no useful outcome.

24.5.2 PLAIN — a picture in your head#

A clerk who finds the desk briefly occupied waits a little and returns. A clerk whose form lacks a required signature does not solve the problem by submitting the same unsigned form a hundred times.
The reason for failure determines the next action.
Where the comparison breaks: software cannot rely on intuition about whether a failure is temporary. It needs documented error classification, transaction cleanup, a deadline and recorded attempt outcomes.

24.5.3 PLAIN — a worked example#

Define a teaching retry policy allowing at most four total attempts within a two-second operation deadline. The first attempt is not called a retry; at most three further attempts remain.
Suggested wait ceilings might grow from 25 to 50 to 100 milliseconds, with a bounded random delay beneath each ceiling. These are invented parameters for illustrating the policy, not recommended universal production values.
Before each new attempt, check cancellation and remaining deadline. Start a clean transaction and reread the facts used in the decision.
If the deadline expires, return a defined failure or unresolved status as appropriate. Do not silently extend the caller’s deadline just because the retry counter has room left.

24.5.4 PLAIN — what is really happening inside#

Backoff reduces immediate repeated competition. Jitter spreads retry timing so a crowd does not wake and collide again in perfect synchrony.
Retrying only the failed final statement can preserve stale decisions from the previous transaction. Retry the entire dependent operation where the engine’s error contract requires it.
Record attempts separately from business requests. The same request may have several failed attempts and one committed outcome; those counts answer different operational questions.

24.5.5 TECHNICAL — the engineer’s version#

PostgreSQL SQLSTATE 40001 requires retrying the complete transaction, including application decision logic. 40P01 deadlock detection can also justify a full retry. Uniqueness failures need context: some arise from competing decisions, while others are persistent conflicts. [S120]
A robust retry wrapper takes a monotonic deadline, maximum attempts, an allowlist of retryable failure classes and an operation that owns its transaction. Cleanup must complete before a new attempt starts.
A lost connection around commit creates a different problem from a confirmed abort. Use request identity and reconciliation to resolve uncertain completion rather than assuming the next attempt starts from an uncommitted world. [S126]

24.5.6 WORDS — remember these#

Backoff: wait longer before another attempt — a retry-spacing strategy that reduces repeated contention or overload. Jitter: deliberately vary the waiting time — bounded randomness used to avoid synchronized retries. Retry budget: the allowed additional effort — limits on attempts, elapsed time or resources spent resolving transient failures.

24.6 Avoiding repeated external effects#

24.6.1 PLAIN — in simple words#

Database rollback does not recall an email, unprint a receipt or reverse an already accepted external instruction.
A retryable transaction that performs such an effect midway can cause it again when the transaction restarts. Even moving the effect after commit leaves a crash gap before sending it.
The design needs a durable connection between the accepted business action and the external work still to be attempted. An outbox records that intent, but delivery and consumer behaviour still need their own rules.

24.6.2 PLAIN — a picture in your head#

Mira writes “send this confirmation” into the accepted reservation record before a messenger takes responsibility for delivery. Another messenger can later find unfinished deliveries.
A messenger who delivered the letter but failed to mark the list may cause another delivery attempt. The list solves forgetting, not every duplicate.
Where the comparison breaks: a recipient’s system can sometimes deduplicate by a stable identifier, but a real person’s inbox or an arbitrary external service may not offer the exact atomicity required by your application.

24.6.3 PLAIN — a worked example#

Attempt A sends a confirmation, then encounters a serialization failure before committing its reservation. A full retry sends the confirmation again. Two messages now describe a reservation that committed only on the second attempt, or perhaps never committed at all.
Instead, record the reservation and an outbox row in one transaction. If that transaction aborts, neither accepted reservation nor accepted delivery intent remains.
A dispatcher later sends the outbox message. If it crashes after sending but before marking completion, it may send again. Give the message a stable identity and define the receiver’s duplicate-handling contract.
Chapter 26 demonstrates a local inbox record and database effect committed together. That bounded design does not prove exactly-once email delivery.

24.6.4 PLAIN — what is really happening inside#

A transaction boundary ends at the participating storage system. An HTTP call to another service is not automatically enlisted in that transaction.
The outbox transforms an immediate external side effect into durable local intent. Delivery becomes a separate state machine with attempts, acknowledgements, retries and unresolved outcomes.
A retry framework should therefore receive an operation designed for repetition, not wrap arbitrary business code and hope rollback covers everything it did.

24.6.5 TECHNICAL — the engineer’s version#

The transactional outbox pattern records business changes and message intent in the same local transaction. Dispatch can still produce duplicate messages, so consumers must handle the delivery semantics explicitly. [S125]
Provider-supported idempotency keys are useful only under the provider’s actual scope, payload-equivalence and retention contract. A key header with no such contract is just an uninterpreted string. [S126]
Tests should inject failures before commit, after commit, before send and after send but before acknowledgement recording. State which components are models and which are actual processes; a simulated crash point is not a power-loss experiment.

24.6.6 WORDS — remember these#

External effect: a change outside the local transaction — an email, print, remote API action or other operation not undone by ordinary database rollback. Outbox: a durable list of external work implied by accepted changes — message-intent records committed atomically with local business state. Delivery gap: uncertainty between an external action and its recorded acknowledgement — a failure window that can require replay or reconciliation.

24.97 Practice and worked answers#

Read a lock name. PostgreSQL reports ROW EXCLUSIVE in its table-lock family. Answer: this is a table-level mode despite its name. Determine scope from the documented lock family and object.
Explain a waiting schema change. A long SELECT holds ACCESS SHARE while a change needs ACCESS EXCLUSIVE. Answer: those modes conflict; a reader can delay the broader operation without changing rows.
Find a cycle. A → B, B → C, C → A. Answer: the graph contains a cycle. A → B → C with no return edge is only a chain in this simplified model.
Separate a timeout. A client gives up after two seconds. Answer: elapsed time alone does not prove deadlock. Inspect the actual failure class and holder state.
Choose an order. Opposite-direction operations need AC-02 and AC-09. Answer: both should follow the same resource-acquisition order, independent of business direction, while reviewing all additional implicit locks.
Count attempts. A maximum of four attempts succeeds on the fourth. Answer: one original attempt and three retries occurred. Record four attempts and one completed business operation.
Classify an invalid request. Quantity is negative. Answer: reject or correct the input under the contract; contention backoff cannot make it valid.
Find the external-effect gap. A dispatcher sends then crashes before marking the outbox row delivered. Answer: replay may duplicate delivery. Stable message identity and receiver-side handling are still needed.

24.98 Common wrong ideas#

Wrong: any lock is a database failure. Right: coordination often requires temporary incompatible access.
Wrong: all readers block all writers. Right: compatibility and MVCC behaviour depend on the operation and engine.
Wrong: every lock ends at COMMIT. Right: session-scoped advisory locks have different lifetime rules.
Wrong: a long wait proves a deadlock. Right: a deadlock requires the relevant dependency cycle, not merely elapsed time.
Wrong: sorting two IDs proves the whole application deadlock-free. Right: implicit locks and alternate paths still matter.
Wrong: retry every error. Right: distinguish transient conflicts, permanent rejection and unknown completion.
Wrong: retrying only the last statement always preserves the business operation. Right: dependent reads and decisions may need to run again.
Wrong: an outbox guarantees exactly-once external delivery. Right: it records local intent atomically; delivery duplicates remain a separate problem.

24.99 Chapter summary in 20 lines#

A lock coordinates access to a specified resource.
Diagnose the waiter, holder, resource, mode and expected release boundary.
Lock names do not always reveal their granularity.
Compatibility matrices decide which modes can coexist.
A broad schema lock can wait behind ordinary reading activity.
Row locks need not block ordinary MVCC reads of visible versions.
Transaction duration controls how long many locks remain relevant.
Idle transactions can retain resources without doing useful work.
Session-level advisory locks differ from transaction-scoped locks.
Blocking is a wait; deadlock is a cycle of waits under the relevant model.
A timeout alone does not establish a deadlock.
A detector may abort a victim so other transactions can proceed.
Consistent resource-acquisition order prevents common cyclic patterns.
Include implicit locks and all mutation paths in a real protocol review.
Retry only failures classified as retryable under the actual contract.
Repeat dependent reads and decisions inside a clean transaction.
Bound retries by attempts, a monotonic deadline and cancellation.
Backoff and jitter can reduce synchronized repeated contention.
Database rollback does not undo ordinary external effects.
Atomic outbox intent still requires a delivery and duplicate-handling protocol.

Return to contents