Chapter 13 · Transactions and Concurrency
Isolation Levels, Locks, and MVCC
Isolation is a policy choice about which concurrent histories a database accepts. Locks coordinate access directly; MVCC lets readers work from versions; practical systems combine both.
Learning outcomes
Select a concurrency policy consciously
Compare the four standard isolation levels and the anomalies each level may permit.
Explain the roles of shared, exclusive, row, predicate, and intent-style locks conceptually.
Describe how MVCC provides snapshots without making writes conflict-free.
Configure SQLite DEFERRED, IMMEDIATE, WAL, and busy-timeout behavior appropriately.
Recognize when PostgreSQL serialization failures require a whole-transaction retry.
The standard isolation ladder
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Typical interpretation |
|---|---|---|---|---|
| READ UNCOMMITTED | May be allowed | May be allowed | May be allowed | Minimum visibility restriction; implementations may still provide stronger behavior. |
| READ COMMITTED | Prevented | May occur | May occur | Each statement reads committed data, often from a fresh statement snapshot. |
| REPEATABLE READ | Prevented | Prevented | May be allowed by the SQL standard | A transaction keeps a stable view; some products, including PostgreSQL, prevent phantoms at this level. |
| SERIALIZABLE | Prevented | Prevented | Prevented | Only histories equivalent to a serial order may commit; the database may abort transactions to enforce this. |
Isolation labels are a starting point. Locking strategy, MVCC implementation, predicate protection, and default settings differ across engines.
PostgreSQL transaction modes
BEGIN TRANSACTIONISOLATION LEVEL READ COMMITTED;SELECT balance_centsFROM accountWHERE account_id = 1;COMMIT;BEGIN TRANSACTIONISOLATION LEVEL SERIALIZABLEREAD WRITE;-- Read predicates, validate rules, and write.-- The commit may fail with a serialization error.COMMIT;At PostgreSQL serializable isolation, a transaction can be aborted even when no statement is syntactically wrong. The application must retry the complete logical transaction from the beginning.
Locks coordinate conflicting operations
Shared/read lock
Several compatible readers may coexist, but a conflicting writer waits.
Exclusive/write lock
Protects a change from incompatible concurrent access.
Fine-grained lock
Limits conflict to selected rows, though indexes and tables may also receive locks.
Predicate protection
Prevents inserts or changes that would create phantoms in a protected key range.
Acquisition discipline
Consistent lock order reduces deadlock risk.
Hold duration
Locks generally remain until transaction end, so shorter transactions improve concurrency.
MVCC: readers choose versions
Multiversion concurrency control stores enough version information for a transaction to read a snapshot while newer versions may be created concurrently. Conceptually, a row version is visible when its creator is visible and its deleting transaction is not visible to the snapshot.
MVCC separates read visibility from physical overwriting, but writers can still conflict and old versions require cleanup.
MVCC tradeoffs
| Benefit | Cost or operational consequence |
|---|---|
| Readers often avoid blocking writers | Old row versions accumulate until no relevant snapshot needs them. |
| Statements or transactions receive consistent snapshots | Long transactions delay cleanup and may retain obsolete versions. |
| Rollback can discard uncommitted versions | Write-write conflicts still require locking, validation, or aborts. |
| Serializable behavior can be implemented by conflict detection | Applications must handle serialization failures correctly. |
SQLite transaction modes
| Mode | When write ownership is requested | Use case |
|---|---|---|
| BEGIN / BEGIN DEFERRED | Not until the first write statement | Optimistic work that may remain read-only. A later upgrade can fail if another writer owns the database. |
| BEGIN IMMEDIATE | At transaction start | Write workflows that prefer to discover writer contention before doing substantial work. |
| BEGIN EXCLUSIVE | At transaction start with stronger exclusion in rollback-journal mode | Specialized maintenance; in WAL mode IMMEDIATE and EXCLUSIVE behave similarly for writer acquisition. |
PRAGMA journal_mode = WAL;PRAGMA busy_timeout = 5000;PRAGMA foreign_keys = ON;BEGIN IMMEDIATE;UPDATE accountSET balance_cents = balance_cents - 100, version_no = version_no + 1WHERE account_id = 1 AND balance_cents >= 100;SELECT changes() AS guarded_rows;COMMIT;busy_timeout asks SQLite to wait for a lock for a bounded interval instead of immediately returning SQLITE_BUSY. It does not replace retry limits, observability, or correct transaction boundaries.
Lock reads only when the decision requires it
BEGIN;SELECT account_id, balance_centsFROM accountWHERE account_id IN (1, 2)ORDER BY account_idFOR UPDATE;UPDATE accountSET balance_cents = balance_cents - 2500WHERE account_id = 1;UPDATE accountSET balance_cents = balance_cents + 2500WHERE account_id = 2;COMMIT;The explicit ordering is part of deadlock prevention. Avoid locking a large result set “just in case”; lock the rows required for the immediate decision.
Pick isolation from invariants
| Requirement | Possible technique |
|---|---|
| Prevent reading uncommitted state | READ COMMITTED or stronger; normal SQLite isolation already provides this. |
| Repeat a consistent analytical read | Repeatable transaction snapshot or a database snapshot/export mechanism. |
| Prevent duplicate natural keys | UNIQUE constraint, regardless of read isolation. |
| Enforce capacity across a predicate | Serializable isolation, explicit locking of the governing row/range, or atomic guarded update. |
| Avoid lost updates | Atomic UPDATE, optimistic version predicate, SELECT FOR UPDATE, or serializable isolation. |
| Keep write latency predictable in SQLite | Short BEGIN IMMEDIATE transactions, WAL where appropriate, and bounded busy handling. |
Checkpoint
Choose the mechanism
- Why can SERIALIZABLE transactions fail even when every SQL statement is valid?
- Does MVCC eliminate writer conflicts?
- When is BEGIN IMMEDIATE preferable to BEGIN DEFERRED in SQLite?
- Why can a long read transaction harm MVCC maintenance?
- Which mechanism should enforce uniqueness: a prior SELECT or a UNIQUE constraint?
Review the answers
Serializable execution may abort a transaction whose concurrent history cannot be safely ordered. MVCC separates reader visibility but writers still conflict. BEGIN IMMEDIATE detects writer contention before the workflow performs significant work. Long snapshots retain old versions. Uniqueness belongs in a UNIQUE constraint; a prior SELECT alone races.
Summary and references
- Isolation levels define accepted visibility and concurrency histories.
- Locks protect conflicting resources; MVCC provides versioned snapshots.
- Serializable isolation can require application retries.
- SQLite permits many readers but one writer per database file and offers DEFERRED, IMMEDIATE, and EXCLUSIVE transaction modes.
- Choose the mechanism from the invariant, not from habit.