149 lines
10 KiB
Markdown
149 lines
10 KiB
Markdown
# Transactions, Isolation, and Locking
|
||
|
||
Concurrency bugs pass every single-user test. The defense is knowing which anomaly your isolation level still allows and closing that one hole explicitly.
|
||
|
||
Contents: Isolation Levels · Anomalies · Engine Defaults · Explicit Locks · Lock Ordering · Deadlocks · Retry Loops · Optimistic Locking · Idempotency · Advisory Locks · Long Transactions · Transaction Boundaries
|
||
|
||
## Isolation Levels and What They Still Allow
|
||
|
||
| Level | Dirty read | Non-repeatable read | Phantom | Write skew |
|
||
|---|---|---|---|---|
|
||
| READ UNCOMMITTED | Possible (not in PostgreSQL — it behaves as READ COMMITTED) | Possible | Possible | Possible |
|
||
| READ COMMITTED | No | Possible | Possible | Possible |
|
||
| REPEATABLE READ | No | No | No in PostgreSQL and InnoDB (MVCC snapshot); allowed by the standard | Possible |
|
||
| SERIALIZABLE | No | No | No | No |
|
||
|
||
Each statement in READ COMMITTED takes a fresh snapshot: a multi-statement report can see two different states of the database and produce totals that never existed. Reports that must be internally consistent run in REPEATABLE READ.
|
||
|
||
## The Anomalies, With Their Real Shapes
|
||
|
||
- **Lost update**: two sessions read `balance = 100`, both write `balance - 10`, the result is 90 instead of 80. Read-modify-write in application code always has this bug unless locked or made atomic.
|
||
- **Non-repeatable read**: the same `SELECT` in one transaction returns different values because another committed in between. Breaks "check then act".
|
||
- **Phantom**: a range query returns new rows on re-execution. Breaks "count then enforce a maximum".
|
||
- **Write skew**: two transactions each read a set, each verifies an invariant, each writes a *different* row, and together they break the invariant. The classic case: two doctors both cancel their on-call shift because each sees the other still on call. No level below SERIALIZABLE prevents it — and it is the anomaly people assume REPEATABLE READ covers.
|
||
|
||
## Engine Defaults (know yours before reasoning)
|
||
|
||
| Engine | Default level | Note |
|
||
|---|---|---|
|
||
| PostgreSQL | READ COMMITTED | SERIALIZABLE uses SSI: no extra locks, but transactions abort with `40001` |
|
||
| MySQL / InnoDB | REPEATABLE READ | Uses next-key (gap) locks, so it blocks phantoms by locking ranges — more deadlocks than PostgreSQL at the same level |
|
||
| MariaDB | REPEATABLE READ | As InnoDB |
|
||
| SQLite | SERIALIZABLE | One writer at a time; `PRAGMA journal_mode=WAL` lets readers proceed during a write |
|
||
| SQL Server | READ COMMITTED (lock-based) | Readers block writers unless `READ_COMMITTED_SNAPSHOT ON` — turning it on is usually the single biggest concurrency win on SQL Server |
|
||
|
||
## Explicit Row Locks
|
||
|
||
```sql
|
||
-- Lock the rows you are about to modify; other writers wait
|
||
SELECT * FROM inventory WHERE product_id = 5 FOR UPDATE;
|
||
|
||
-- Weaker: prevents deletion/key change, allows concurrent non-key updates
|
||
SELECT * FROM users WHERE id = 5 FOR SHARE; -- FOR KEY SHARE in PostgreSQL
|
||
|
||
-- Fail immediately instead of queueing (interactive paths, health checks)
|
||
SELECT * FROM jobs WHERE id = 5 FOR UPDATE NOWAIT;
|
||
|
||
-- Skip contended rows (queues; each worker gets a disjoint set)
|
||
SELECT id FROM jobs WHERE status = 'pending'
|
||
ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;
|
||
```
|
||
|
||
- `FOR UPDATE` locks rows the query *returns*. In READ COMMITTED, a row modified after your snapshot is re-read at the new version — your `WHERE` may no longer be true for it. Re-check the condition after acquiring the lock.
|
||
- `FOR UPDATE` combined with `LEFT JOIN` locks rows from the outer table too unless you scope it (`FOR UPDATE OF t`).
|
||
- `SKIP LOCKED` changes semantics, not just performance: the query is no longer "the oldest pending job", it is "the oldest one nobody else holds". That is correct for queues and wrong for reports.
|
||
- Row locks are released only at COMMIT or ROLLBACK — never at the end of the statement.
|
||
|
||
## Lock Ordering (the deadlock prevention that actually works)
|
||
|
||
Two transactions locking `{1,2}` and `{2,1}` deadlock; both locking `{1,2}` queue. Impose a total order everywhere rows are locked together:
|
||
|
||
```sql
|
||
SELECT * FROM accounts WHERE id IN (:a, :b) ORDER BY id FOR UPDATE;
|
||
```
|
||
|
||
The order must be consistent across *code paths*, not just within one function — a transfer routine and a batch-settlement job locking the same two accounts in different orders is the standard production deadlock.
|
||
|
||
Additional ordering hazards:
|
||
|
||
- Parent/child: inserting a child takes a lock on the parent row for FK validation. A job that updates parents and another that inserts children touch the same rows in opposite order.
|
||
- Index maintenance: two inserts with no row in common can still contend on the same index page or, in InnoDB, the same gap.
|
||
- Upserts: `INSERT ... ON CONFLICT` may take locks in key order determined by the values, so batches should be sorted by key before insert.
|
||
|
||
## Reading a Deadlock Report
|
||
|
||
- PostgreSQL logs both statements and the lock each was waiting on. The victim is chosen by cost; it is not necessarily the guilty one.
|
||
- MySQL: `SHOW ENGINE INNODB STATUS`, section `LATEST DETECTED DEADLOCK` — it holds only the most recent one, so capture it immediately.
|
||
- The two statements shown are where the transactions *waited*, not where they took their first lock. Read the whole transaction, not the reported line.
|
||
- Deadlock frequency rising with load and no shared rows → gap locks (InnoDB REPEATABLE READ) or missing indexes forcing wide range locks. An index that narrows the scan narrows the locks.
|
||
|
||
## The Retry Loop (non-optional at SERIALIZABLE)
|
||
|
||
Serialization failures and deadlocks are expected outcomes, not exceptions to log and drop.
|
||
|
||
```
|
||
attempt = 0
|
||
while attempt < 3:
|
||
begin
|
||
try:
|
||
do work; commit; break
|
||
except sqlstate in ('40001', '40P01'): # serialization failure, deadlock
|
||
rollback
|
||
attempt += 1
|
||
sleep(random jitter, e.g. 50ms × 2^attempt ± 25%)
|
||
except other:
|
||
rollback; raise
|
||
```
|
||
|
||
- Retry only the whole transaction. Retrying one statement inside a rolled-back transaction executes against nothing.
|
||
- The work must be idempotent or wholly inside the transaction — a retry that re-sends an email is worse than the original failure.
|
||
- Cap attempts (3 is a reasonable default; a house retry cap is a Thresholds preference) and jitter the backoff; synchronized retries recreate the same collision.
|
||
- Without this loop, SERIALIZABLE is strictly worse than REPEATABLE READ: same cost, plus failures you do not handle.
|
||
|
||
## Optimistic Locking (no database lock at all)
|
||
|
||
```sql
|
||
UPDATE documents
|
||
SET body = :body, version = version + 1
|
||
WHERE id = :id AND version = :expected_version;
|
||
-- 0 rows affected = someone else won; re-read and re-apply or surface a conflict
|
||
```
|
||
|
||
- Correct choice when conflicts are rare and the "transaction" spans a user thinking (an edit form open for minutes). Never hold a database lock across user time (SKILL.md rule 5).
|
||
- The application must check the affected-row count. Ignoring it discards the user's edit with no error — the most common implementation bug.
|
||
- A `updated_at` timestamp works as the version only if its resolution exceeds the update rate; an integer counter has no such failure.
|
||
|
||
## Idempotency and Exactly-Once Writes
|
||
|
||
- Give externally triggered writes a client-supplied idempotency key with a unique constraint: the second attempt violates the constraint, and the handler returns the first result.
|
||
- `INSERT ... ON CONFLICT DO NOTHING RETURNING id` returns no row when the insert was a no-op — fetch the existing row explicitly rather than treating the empty result as failure.
|
||
- Database transactions do not extend to external systems. Commit the database first and drive the external effect from a durable outbox row, or accept a window where one succeeded and the other did not.
|
||
- Retries of a batch must be safe at any prefix: chunk the work and record progress in the same transaction as the work.
|
||
|
||
## Advisory / Application Locks
|
||
|
||
```sql
|
||
SELECT pg_advisory_xact_lock(hashtext('nightly-rollup')); -- released at commit
|
||
SELECT GET_LOCK('nightly-rollup', 10); -- MySQL, with timeout
|
||
```
|
||
|
||
- Use for "only one worker runs this job", cron singletons, and serializing a migration across instances — cases where no row represents the resource.
|
||
- Prefer the transaction-scoped variant (`pg_advisory_xact_lock`). Session-scoped locks survive commit and leak when a connection is returned to a pool still holding one.
|
||
- Advisory locks live in a flat namespace of integers: hash a descriptive string and keep the list of names in one place, or two features end up sharing a lock.
|
||
- Under a transaction-mode pooler, session-level advisory locks are unusable.
|
||
|
||
## Long Transactions Are a Systemic Cost
|
||
|
||
- They hold row locks, block DDL, and in PostgreSQL pin dead tuples so nothing can be vacuumed database-wide — one forgotten `BEGIN` bloats every table.
|
||
- `idle in transaction` is worse than a long-running query: it holds everything while doing nothing. Set `idle_in_transaction_session_timeout` on the application role.
|
||
- The monitoring threshold is the canonical one: investigate any transaction open past 1 minute.
|
||
- A transaction that spans an HTTP call inherits that service's worst-case latency as its lock duration.
|
||
|
||
## Where Transaction Boundaries Belong
|
||
|
||
- One transaction per unit of business meaning. Framework "transaction per request" middleware makes every slow template render part of the transaction.
|
||
- Do not open a transaction to run a single statement — it is already atomic.
|
||
- Read-only work does not need a transaction unless it needs a consistent multi-statement snapshot; then declare it (`SET TRANSACTION READ ONLY` also lets the engine skip work).
|
||
- Nested transactions do not exist in most engines: an inner `BEGIN` is ignored or errors. Partial rollback needs savepoints, and a savepoint per loop iteration has real cost in PostgreSQL (each consumes a subtransaction slot; past 64 open subtransactions per session, other backends pay a lookup penalty).
|
||
- DDL inside a transaction is safe in PostgreSQL, SQLite, and SQL Server; MySQL commits implicitly (SKILL.md Traps).
|