Chapter 13 · Transactions and Concurrency
Deadlocks, Retries, and Idempotent Operations
Correct concurrent software assumes some transactions will be aborted. The engineering task is to avoid predictable deadlocks, retry transient failures safely, and prevent duplicate business effects.
Learning outcomes
Design for aborts and repeated delivery
Identify a deadlock as a cycle in the wait-for relationship.
Reduce deadlock probability through consistent access order, short transactions, and focused locking.
Classify retryable, permanent, and ambiguous failures.
Implement bounded exponential backoff with jitter and a maximum attempt count.
Use idempotency keys and durable operation records to prevent duplicate business effects.
Deadlock is a cycle, not simply a wait
A wait becomes a deadlock when transactions form a cycle: each holds a resource needed by the next, so none can proceed.
Deadlock detection resolves the cycle by aborting at least one transaction. The application must handle that abort.
A classic deadlock schedule
Session A Session B--------- ---------BEGIN; BEGIN;UPDATE account WHERE id = 1; UPDATE account WHERE id = 2;UPDATE account WHERE id = 2; waits UPDATE account WHERE id = 1; waitsThe database detects the cycle and aborts one transaction.SQLite’s usual single-writer model means this exact row-lock cycle is not its common failure mode; competing writers more often receive or wait on SQLITE_BUSY. Server databases with row-level locking can form the cycle directly.
Prevent predictable deadlocks
Acquire resources consistently
Sort account IDs, order IDs, or other lock keys before touching them.
Keep transactions brief
Do not wait for users, remote services, or long computation while holding locks.
Lock only required rows
Broad scans and unnecessary explicit locks increase the conflict surface.
Make predicates selective
Efficient access paths reduce rows examined and locks retained.
Capture deadlock evidence
Log operation identity, statements, lock targets, attempt number, and database error code.
Assume a victim can be aborted
Re-run the whole transaction, not only the statement that happened to fail.
Consistent access order
BEGIN;SELECT account_id, balance_centsFROM accountWHERE account_id IN (1, 2)ORDER BY account_idFOR UPDATE;-- Both transfer directions lock account 1 before account 2.UPDATE accountSET balance_cents = balance_cents - 2500WHERE account_id = 1 AND balance_cents >= 2500;UPDATE accountSET balance_cents = balance_cents + 2500WHERE account_id = 2;COMMIT;The business direction of the transfer must not determine lock order. Sort lock keys independently, then apply debit and credit semantics.
Classify failures before retrying
| Failure class | Examples | Action |
|---|---|---|
| Transient and retryable | Deadlock victim, serialization failure, bounded lock timeout, SQLite busy. | Rollback, wait with jitter, and retry the complete transaction within limits. |
| Permanent input or rule failure | Unique violation, CHECK failure, invalid foreign key, insufficient funds. | Do not blindly retry; return or repair the business error. |
| Ambiguous outcome | Connection drops during COMMIT or response is lost after commit. | Look up the durable idempotency record before attempting the operation again. |
| Infrastructure failure | Disk full, corruption, repeated unavailable primary. | Stop local retries, alert, and use service-level recovery policy. |
Bounded exponential backoff with jitter
Jₖ is random jitter. It prevents many clients from retrying at exactly the same intervals. Bound both the delay and the number of attempts so failures remain visible.
attempt = 0while attempt < max_attempts: begin transaction try: run the complete logical operation commit return success catch retryable_database_error: rollback if possible sleep(min(max_delay, base_delay * 2^attempt) + random_jitter) attempt += 1 catch permanent_business_error: rollback return failureraise retry_exhaustedIdempotency means the same request has one business effect
Network delivery is frequently at least once: the client can resend after timeout. A stable request key lets the server distinguish a retry from a new operation.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS operation_result;DROP TABLE IF EXISTS transfer_request;DROP TABLE IF EXISTS transfer_event;DROP TABLE IF EXISTS account;CREATE TABLE account ( account_id INTEGER PRIMARY KEY, owner_name TEXT NOT NULL, balance_cents INTEGER NOT NULL CHECK (balance_cents >= 0)) STRICT;CREATE TABLE transfer_request ( request_key TEXT PRIMARY KEY, payload_hash TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('started','committed','failed')), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at TEXT) STRICT;CREATE TABLE transfer_event ( transfer_id TEXT PRIMARY KEY, request_key TEXT NOT NULL UNIQUE REFERENCES transfer_request(request_key), from_account INTEGER NOT NULL REFERENCES account(account_id), to_account INTEGER NOT NULL REFERENCES account(account_id), amount_cents INTEGER NOT NULL CHECK (amount_cents > 0), CHECK (from_account <> to_account)) STRICT;CREATE TABLE operation_result ( request_key TEXT PRIMARY KEY REFERENCES transfer_request(request_key), result_json TEXT NOT NULL, stored_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO account VALUES (1, 'Ava Chen', 12500), (2, 'Liam Ortiz', 7500);Claim the request before applying effects
BEGIN IMMEDIATE;INSERT INTO transfer_request (request_key, payload_hash, status)VALUES ('req-transfer-9001', 'sha256:from1-to2-2500', 'started')ON CONFLICT (request_key) DO NOTHING;SELECT changes() AS request_claimed;-- The application proceeds only when request_claimed = 1.-- If it is 0, load the existing request and verify payload_hash.ROLLBACK;Using the same key with a different payload is a conflict, not a valid retry. Store or recompute a canonical payload hash and compare it.
Apply the transfer and store its result atomically
BEGIN IMMEDIATE;INSERT INTO transfer_request (request_key, payload_hash, status)VALUES ('req-transfer-9001', 'sha256:from1-to2-2500', 'started')ON CONFLICT (request_key) DO NOTHING;-- Application assertion: changes() must equal 1 before continuing.UPDATE accountSET balance_cents = balance_cents - 2500WHERE account_id = 1 AND balance_cents >= 2500;UPDATE accountSET balance_cents = balance_cents + 2500WHERE account_id = 2;INSERT INTO transfer_event (transfer_id, request_key, from_account, to_account, amount_cents)VALUES ('tr-9001', 'req-transfer-9001', 1, 2, 2500);INSERT INTO operation_result (request_key, result_json)VALUES ('req-transfer-9001', '{"transfer_id":"tr-9001","status":"committed"}');UPDATE transfer_requestSET status = 'committed', completed_at = CURRENT_TIMESTAMPWHERE request_key = 'req-transfer-9001';COMMIT;On a repeated request, the unique request key prevents a second claim. The service returns the stored result instead of reapplying the debit and credit.
Handle an ambiguous commit response
SELECT r.request_key, r.payload_hash, r.status, o.result_json, e.transfer_idFROM transfer_request AS rLEFT JOIN operation_result AS o ON o.request_key = r.request_keyLEFT JOIN transfer_event AS e ON e.request_key = r.request_keyWHERE r.request_key = 'req-transfer-9001';If the row says committed, return the stored result. If it is started after a crash, recovery policy must decide whether to resume, mark failed, or reconcile from the event and account state.
SQLite busy handling
PRAGMA journal_mode = WAL;PRAGMA busy_timeout = 3000;BEGIN IMMEDIATE;-- Perform a short, idempotency-protected write workflow.COMMIT;A busy timeout is only the first wait. Application-level retries should remain bounded, instrumented, and aware of whether the request was already committed.
Chapter 13 checkpoint
Design the recovery path
- What graph property defines a deadlock?
- Why must a deadlock retry restart the whole transaction?
- Which failures should not be blindly retried?
- Why is random jitter added to exponential backoff?
- How does an idempotency key resolve an uncertain COMMIT response?
Review the answers
A deadlock is a cycle in the wait-for graph. The aborted transaction has lost all of its work and earlier reads may no longer be valid, so retry the whole logical unit. Constraint and business-rule failures require correction, not blind retry. Jitter prevents synchronized retry storms. The durable request row and stored result reveal whether the operation already committed, preventing duplicate effects.
Summary and references
- Deadlocks are expected concurrency events; consistent lock order and short transactions reduce them.
- Retry only classified transient failures and retry the complete transaction.
- Use bounded exponential backoff with jitter and observability.
- Idempotency keys turn repeated delivery into one durable business effect.
- Ambiguous outcomes are resolved by querying durable operation state, not by guessing.
Chapter 14 continues with indexes and query execution: B-tree structure, composite and covering indexes, selectivity, query plans, and evidence-based tuning.