--- title: "What Actually Happens Inside a Database Transaction" author: "Alysaa Lissy" published: "2026-11-13T08:32:25.125Z" updated: "2026-11-14T07:55:55.316Z" url: "https://zyvop.com/what-actually-happens-inside-a-database-transaction-gtd1d" canonical: "https://zyvop.com/what-actually-happens-inside-a-database-transaction-gtd1d" tags: [] --- # What Actually Happens Inside a Database Transaction Explore what happens between BEGIN and COMMIT: write-ahead logs, row versions, snapshots, isolation levels, and how write skew slips past ACID. ![Cover image]() > **TL;DR** > > - A transaction promises **ACID**, but the **I** (isolation) is a **dial, not a switch**. Most databases default to a level _below_ serializable. > > - Commit is **durable** because the database flushes a sequential **write-ahead log**, not because it rewrites your tables. > > - Most modern databases isolate transactions with **MVCC**: updates create new **row versions**, and each reader sees a **snapshot**. > > - Know your anomalies: **dirty read, non-repeatable read, phantom, lost update, write skew**. The same level name (like "repeatable read") **behaves differently** in different databases. > > - Choose the weakest level that is _correct_, make multi-row rules explicit with **constraints, locks or a one-row guard**, and **retry** when the database says no. > Here is a bug that passes every test you wrote. A hospital's scheduling app has one rule: **at least one doctor must be on call**. Tonight, Alice and Bob are both on call, both feel unwell, and both tap "Leave shift" at the same moment. Each request runs in its own transaction. Each one first checks that more than one doctor is on call (true: two!), then marks its own doctor as off. Both transactions were correct. Both committed. **Nobody is on call.** No unit test catches this, because no single transaction is wrong. The bug lives _between_ two transactions, in the part of ACID most of us never read closely: the **I**. This post opens the box between `BEGIN` and `COMMIT` so you can see why that happens, which databases allow it, and what to do about it. * * * ## 1\. ACID, honestly You have heard the acronym. Here is the version that says who does the work: | | Promise | How the database does it | Whose job | | --- | --- | --- | --- | | **Atomicity** | All of a transaction's changes happen, or none do | Postgres never undoes changes in place: a rolled-back transaction's row versions just stay invisible until `VACUUM` reclaims them. Other engines keep an undo log. | The database | | **Consistency** | Declared rules hold before and after | Primary keys, foreign keys, `CHECK`, unique and exclusion constraints | **You.** The database only enforces rules you declare. | | **Isolation** | Concurrent transactions do not interfere with each other | Snapshots (MVCC) and/or locks | **You choose how much.** | | **Durability** | Committed data survives a crash | A write-ahead log flushed at commit | The database | Notice that two of the four letters (C and I) hand you a decision. The doctor bug is a failure of _consistency_ that isolation, at its default setting, does not catch. * * * ## 2\. Durability: what COMMIT actually waits for When you `COMMIT`, the database does **not** write your changed table pages to disk. That would mean many random writes scattered across files. Instead it relies on the **write-ahead log (WAL)**, whose central rule is that changes to data files must be written only _after_ a log record describing the change has been flushed to permanent storage. ```mermaid sequenceDiagram participant C as Client participant S as Server process participant M as Buffer cache in memory participant W as WAL on disk participant D as Data files on disk C->>S: BEGIN C->>S: UPDATE accounts SET balance = balance - 30 S->>M: change the row in memory S->>W: append a log record describing the change C->>S: COMMIT S->>W: flush the log to disk (fsync) S-->>C: COMMIT acknowledged, the change is now durable Note over M,D: Later, in the background, a checkpoint writes the changed data pages to the data files ``` Why this design wins: - **Commit is one sequential flush.** The log is written sequentially, so syncing it costs far less than flushing every scattered data page the transaction touched. - **Group commit.** Under load, a single `fsync` of the log can commit many concurrent transactions at once. - **Crash recovery is replay.** After a crash, the database starts from the last checkpoint and _redoes_ the logged changes that had not yet reached the data files. So "durable" really means "the log record reached stable storage." (Postgres even lets you trade this guarantee for lower latency with asynchronous commit, accepting that a crash may lose the most recent commits.) * * * ## 3\. Isolation, part 1: row versions and snapshots The next question is what happens when two transactions touch the same data at the same time. The classic answer was locks: readers wait for writers. Most modern databases (Postgres, Oracle, MySQL's InnoDB, and others) use **multiversion concurrency control (MVCC)** instead. The idea is simple: **never overwrite a row. Create a new version.** ```mermaid flowchart LR V1["Version 1
balance 100
created by tx 90
replaced by tx 101"] --> V2["Version 2
balance 70
created by tx 101
replaced by tx 140"] V2 --> V3["Version 3
balance 45
created by tx 140
current"] R1(["Reader with an old snapshot
(tx 101 had not committed yet)"]) -.->|sees| V1 R2(["Reader with a fresh snapshot
(tx 140 has committed)"]) -.->|sees| V3 ``` Every `UPDATE` marks the old version as replaced and writes a new one. When a reader starts, the database takes a **snapshot**: essentially, a record of which transactions had committed at that instant. A row version is visible to you if the transaction that created it is in your snapshot, and the one that replaced it is not. Postgres exposes the bookkeeping as hidden columns, so you can look at it yourself: ```sql SELECT xmin, xmax, * FROM accounts WHERE id = 1; -- xmin: the transaction that created this version -- xmax: the transaction that replaced or deleted it (0 while the version is current) ``` This buys a lot: **readers do not block writers, and writers do not block readers.** Row-level locks only block other _writers_ (and lockers) of the _same row_. It also has a cost. Old versions pile up until `VACUUM` removes them, and a transaction that stays open **pins its snapshot**, so versions it might still need cannot be cleaned up. We will come back to that in section 8. * * * ## 4\. Isolation, part 2: the dial Isolation levels decide _which snapshot you get_ and _what happens on conflict_. In Postgres: | Level | When the snapshot is taken | Dirty read | Non-repeatable read | Phantom read | Serialization anomaly (e.g. write skew) | | --- | --- | --- | --- | --- | --- | | **Read Committed** (default) | At the start of **each statement** | No | **Possible** | **Possible** | **Possible** | | **Repeatable Read** | Once, at the **transaction's first statement** | No | No | No (in Postgres) | **Possible** | | **Serializable** | Once, plus dependency tracking | No | No | No | No | (Postgres accepts `READ UNCOMMITTED` but treats it exactly like Read Committed, so dirty reads never happen there.) ### The standard is not enough The SQL standard defines its levels using only three phenomena: dirty reads, non-repeatable reads and phantoms. A famous 1995 paper, _A Critique of ANSI SQL Isolation Levels_, showed that those definitions are ambiguous and miss important anomalies, including **lost update** and **write skew**. It also defined **snapshot isolation**, which sits strictly between Read Committed and Serializable and, as we are about to see, still allows write skew. ### Same name, different behavior Because the standard is loose, every database fills in the names its own way. Martin Kleppmann's _Hermitage_ project tests this directly, and his summary is blunt: PostgreSQL, MySQL and SQL Server all offer a level called "repeatable read," and it means something different in each. Here is what matters: | Database | Default | What to know | | --- | --- | --- | | **PostgreSQL** | Read Committed | "Repeatable Read" is snapshot isolation. A concurrent update to a row you also changed aborts you with error `40001`. Write skew is still possible. "Serializable" detects the rest and aborts a transaction when needed. | | **MySQL (InnoDB)** | Repeatable Read | Plain `SELECT`s read a snapshot fixed by the transaction's first read, but `UPDATE`, `DELETE` and locking reads act on the **latest committed data**. Hermitage's tests show lost updates remain possible at this level. | | **SQLite** | Serializable | It serializes the writes: one writer at a time. In WAL mode, readers see a stable snapshot. | And **Oracle's** `SERIALIZABLE` is actually snapshot isolation, so it permits write skew. The lesson: **a level's name tells you very little. Read your database's documentation, and test the anomaly you care about.** * * * ## 5\. The anomalies, on a timeline Each timeline below shows two transactions interleaving. The "alt" branches show what different isolation levels do at the same moment. ### 5.1 Dirty read A transaction reads a change that another transaction has not committed, and may never commit. ```mermaid sequenceDiagram participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: BEGIN A->>DB: UPDATE balance = 0 (it was 100) B->>DB: SELECT balance DB-->>B: 0, A's uncommitted change Note over B: B refuses a refund because the balance is 0 A->>DB: ROLLBACK Note over DB: The balance is 100 again. The 0 never really existed. ``` **Allowed at:** `READ UNCOMMITTED`, in databases that really implement it. Postgres does not, so it cannot happen there. ### 5.2 Non-repeatable read You read the same row twice inside one transaction and get different answers, because someone committed in between. ```mermaid sequenceDiagram participant A as Transaction A participant DB as Database participant B as Transaction B A->>DB: BEGIN A->>DB: SELECT balance DB-->>A: 100 B->>DB: UPDATE balance = 50 and COMMIT A->>DB: SELECT balance again alt READ COMMITTED DB-->>A: 50 (a new snapshot for every statement) else REPEATABLE READ or SERIALIZABLE DB-->>A: 100 (one snapshot for the whole transaction) end ``` This is why a report that runs several queries at Read Committed can add up to a total that never existed at any single moment. ### 5.3 Phantom read The same, but for a _set_ of rows: you re-run a query with a condition and rows have appeared or vanished. ```sql -- Session A, READ COMMITTED SELECT count(*) FROM bookings WHERE room = 7 AND day = '2026-12-01'; -- 0 -- Session B inserts a booking for room 7 on that day, and commits SELECT count(*) FROM bookings WHERE room = 7 AND day = '2026-12-01'; -- now 1 ``` The SQL standard allows phantoms at Repeatable Read, but Postgres's snapshots prevent them. A phantom matters most when it feeds a _decision_, which brings us to write skew. ### 5.4 Lost update Two transactions read the same value, each computes a new one, and the second write silently erases the first. ```mermaid sequenceDiagram participant A as App A participant DB as Database participant B as App B A->>DB: SELECT balance (reads 100) B->>DB: SELECT balance (reads 100) A->>DB: UPDATE balance = 150 and COMMIT (100 plus 50) B->>DB: UPDATE balance = 120 (100 plus 20) alt READ COMMITTED Note over DB: B's write succeeds. Final balance is 120 and A's +50 is lost. else REPEATABLE READ in Postgres DB-->>B: ERROR could not serialize access due to concurrent update Note over B: B retries from the top and now reads 150 end ``` Three standard fixes, from simplest to most general: ```sql -- Broken: read-modify-write in application code SELECT balance FROM accounts WHERE id = 1; -- the app computes balance + 50 UPDATE accounts SET balance = 150 WHERE id = 1; -- overwrites whatever happened in between -- Fix 1: let the database do the arithmetic, atomically UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- Fix 2: lock the row before you read it SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- Fix 3: optimistic check with a version column UPDATE accounts SET balance = $1, version = version + 1 WHERE id = 1 AND version = $2; -- 0 rows updated means someone beat you: re-read and retry ``` Remember the table in section 4: on MySQL's default level the second `UPDATE` would just succeed, so Fix 1, 2 or 3 is not optional there. ### 5.5 Write skew This is the doctors' bug. Two transactions read an overlapping set of rows, then each updates a _different_ row, based on what it read. There is no write-write conflict, so nothing stops them, yet together they break a rule that spans the rows. ```mermaid sequenceDiagram participant A as Alice's session participant DB as Database participant B as Bob's session A->>DB: BEGIN B->>DB: BEGIN A->>DB: SELECT count(*) WHERE on_call DB-->>A: 2 B->>DB: SELECT count(*) WHERE on_call DB-->>B: 2 Note over A,B: Both reason that with 2 on call, one of them can safely leave A->>DB: UPDATE on_call = false WHERE name = alice B->>DB: UPDATE on_call = false WHERE name = bob A->>DB: COMMIT B->>DB: COMMIT alt REPEATABLE READ (snapshot isolation) Note over DB: Different rows, so no write-write conflict. Both commit. Zero doctors on call. else SERIALIZABLE DB-->>B: ERROR could not serialize access (40001), one transaction is aborted Note over DB: After a retry, the second doctor sees 1 on call and stays. end ``` In SQL: ```sql -- Both sessions run this at REPEATABLE READ (table: doctors(name text primary key, on_call boolean)) BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM doctors WHERE on_call; -- both sessions see 2 -- application: "2 is more than 1, so it is safe for me to leave" UPDATE doctors SET on_call = false WHERE name = 'alice'; -- Bob's session updates 'bob' instead COMMIT; -- both commit successfully ``` Three ways to fix it, and they are worth knowing because **each fits a different situation**: ```sql -- Fix 1: Serializable. The database detects the dangerous pattern and aborts one transaction. BEGIN ISOLATION LEVEL SERIALIZABLE; -- ...same statements... and retry on SQLSTATE 40001 -- Fix 2: lock the rows your decision depends on (at the default Read Committed level). BEGIN; SELECT name FROM doctors WHERE on_call FOR UPDATE; -- the second session waits here -- Once the first commits, Postgres re-checks the condition on the rows it waited for: -- a row that no longer matches (on_call is now false) is skipped, so the second session -- sees only 1 doctor on call and refuses to leave. -- (At Repeatable Read, the second session would get a serialization error instead, and retry.) -- Fix 3: turn the rule into a one-row guard that both transactions must update. UPDATE shift_coverage SET on_call_count = on_call_count - 1 WHERE shift = 'tonight' AND on_call_count > 1; -- 0 rows updated means: you cannot leave ``` Fix 3 is the one to reach for when you can: _materializing the conflict_ into one row forces ordinary row locking to serialize the two transactions, with no special isolation level needed. > **A real-database footnote.** SQLite behaves differently from all of this. Running the exact write-skew scenario on two real SQLite connections, the second writer fails with "database is locked" because SQLite serializes writers. After an application retry it sees one doctor on call and stays. Serializable-by-construction is simple, at the cost of one writer at a time. * * * ## 6\. Serializable, and the retry contract **Serializable** means the outcome of any set of concurrent transactions equals _some_ one-at-a-time execution of them. Postgres implements it as _serializable snapshot isolation_ (SSI): transactions run on snapshots without extra blocking, while the database watches for the read/write dependency patterns that can produce a non-serial outcome, and aborts one transaction when it finds one. It errs on the side of caution, so it can abort a transaction that would have been fine. That gives Serializable a **contract with your code**: it will sometimes say no, and your code must say "OK, again." A failed transaction is not a bug, it is the mechanism. Postgres reports it as SQLSTATE `40001` (`serialization_failure`), and a deadlock as `40P01`. Wrap every transaction that runs at Repeatable Read or Serializable in a retry loop: ```javascript const sleep = (ms) => new Promise((r) => setTimeout(r, ms)); async function withRetry(work, { attempts = 5 } = {}) { for (let attempt = 1; ; attempt++) { try { return await db.tx(work); // the WHOLE transaction runs again on a retry } catch (err) { const retryable = err.code === '40001' || err.code === '40P01'; // serialization failure, deadlock if (!retryable || attempt >= attempts) throw err; await sleep(Math.random() * 10 * 2 ** attempt); // jittered exponential backoff } } } ``` Two rules make retries safe: 1. **The transaction body must be re-runnable.** It will be executed more than once, so it should read fresh data each time and avoid side effects. 2. **Keep side effects that leave the database out of the transaction.** If the body sends an email or calls a payment API, a retry sends it twice. Write the intent to the database inside the transaction (an outbox row, for instance) and perform the external action after commit. * * * ## 7\. Locks and guards: the other half of the toolbox Isolation levels are one tool. Often the sharpest fix is more explicit. **Row locks.** Postgres takes an exclusive row lock automatically when you update or delete a row, and holds it until the transaction ends. `SELECT ... FOR UPDATE` takes the same lock without modifying the row, so you can read, decide, and write without anyone changing the row in between. Row locks block only writers and lockers of that row, never plain readers. **Deadlocks.** Locks can form a cycle: transaction 1 holds row A and wants row B, while transaction 2 holds B and wants A. Neither can ever proceed. Postgres notices after a wait (`deadlock_timeout`, one second by default), aborts one transaction with `40P01`, and lets the other finish. The cure is a convention, not a setting: **always lock rows in the same order** (say, lowest id first), keep transactions short, and retry on `40P01`. ```sql -- Always touch rows in the same order (lowest id first), whichever way the money moves. -- Moving 100 from account 1 to account 2: UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Moving 100 from account 2 to account 1 still updates id 1 first, with the signs swapped: UPDATE accounts SET balance = balance + 100 WHERE id = 1; UPDATE accounts SET balance = balance - 100 WHERE id = 2; ``` **Constraints beat isolation** whenever the rule can be declared. A unique constraint stops duplicate usernames at _any_ isolation level. An exclusion constraint stops double booking: ```sql -- assumes bookings(room int, during tstzrange) CREATE EXTENSION IF NOT EXISTS btree_gist; ALTER TABLE bookings ADD CONSTRAINT no_double_booking EXCLUDE USING gist (room WITH =, during WITH &&); ``` Putting it together: | Problem | Best tool | | --- | --- | | Counter or balance update | Atomic `UPDATE x = x + n` | | Read-modify-write on one row | `SELECT ... FOR UPDATE`, a version column, or Repeatable Read plus retry | | Uniqueness, no double booking | A unique or exclusion constraint | | Rule across several rows you read, then change (write skew) | Serializable plus retry, or lock the rows you read, or a one-row guard | | A consistent view across several queries (report, export) | Repeatable Read, read-only | * * * ## 8\. Long transactions are expensive Everything above assumes transactions are short. A transaction that lingers costs you in three ways: - **It holds locks**, so every writer that touches those rows waits behind it, and deadlock chances grow. - **It pins its snapshot**, so `VACUUM` cannot reclaim old row versions, and tables bloat. - **It may be doing nothing at all.** A connection that opened a transaction and then went quiet shows up as `idle in transaction`, usually because the application is waiting on something slow (a network call, a user). Defend with timeouts, so a stuck transaction dies instead of lingering: ```sql SET lock_timeout = '2s'; -- stop waiting for a lock after 2 seconds SET statement_timeout = '30s'; -- abort statements that run too long SET idle_in_transaction_session_timeout = '60s'; -- kill sessions that sit open doing nothing ``` And never hold a transaction open across a network call to another service. Do the slow thing first, then open the transaction, write, and commit. * * * ## 9\. Build a toy MVCC and watch the anomalies happen Reading about snapshots is one thing. Here is a small model (about 70 lines) you can run with plain Node.js. It is **not** Postgres: it has no locks, no WAL and no disk, and its conflict checks work at the level of whole keys. But it implements the same core idea: versioned rows, snapshots, and per-level rules. ```javascript class Store { constructor() { this.clock = 0; // advances by one on every commit this.versions = new Map(); // key -> [{ value, begin, end }], newest last this.active = new Set(); // open transactions (needed only to demo dirty reads) } seed(entries) { this.clock++; for (const [k, v] of Object.entries(entries)) this.versions.set(k, [{ value: v, begin: this.clock, end: Infinity }]); } begin(level) { return new Tx(this, level); } visible(key, snap) { // the version a given snapshot is allowed to see return (this.versions.get(key) || []).find((v) => v.begin <= snap && snap < v.end); } lastCommit(key) { const vs = this.versions.get(key) || []; return vs.length ? vs[vs.length - 1].begin : 0; } } class Tx { constructor(store, level) { this.s = store; this.level = level; this.snapshot = store.clock; // kept for the whole transaction at repeatable read and serializable this.writes = new Map(); // buffered: invisible to everyone else until commit this.readKeys = new Set(); store.active.add(this); } get snap() { return this.level === 'read committed' ? this.s.clock : this.snapshot; } // RC: fresh each statement read(key) { if (this.writes.has(key)) return this.writes.get(key); // you always see your own writes if (this.level === 'read uncommitted') // peek at other transactions' pending writes for (const t of this.s.active) if (t !== this && t.writes.has(key)) return t.writes.get(key); this.readKeys.add(key); const v = this.s.visible(key, this.snap); return v && v.value; } scan(pred) { // like SELECT ... WHERE pred return [...this.s.versions.keys()].filter((k) => { const val = this.read(k); return val !== undefined && pred(val); }); } write(key, value) { this.writes.set(key, value); } rollback() { this.s.active.delete(this); } commit() { const s = this.s; s.active.delete(this); if (this.level === 'repeatable read' || this.level === 'serializable') for (const k of this.writes.keys()) // first committer wins if (s.lastCommit(k) > this.snapshot) throw failure('concurrent update'); if (this.level === 'serializable') for (const k of this.readKeys) // something I based a decision on changed if (s.lastCommit(k) > this.snapshot) throw failure('a row I read was changed'); s.clock++; for (const [k, value] of this.writes) { const vs = s.versions.get(k) || []; if (vs.length) vs[vs.length - 1].end = s.clock; // retire the old version vs.push({ value, begin: s.clock, end: Infinity }); // add the new one s.versions.set(k, vs); } } } function failure(why) { const e = new Error('could not serialize access: ' + why); e.code = '40001'; return e; } ``` Now replay two of our timelines against it: ```javascript function nonRepeatableRead(level) { const s = new Store(); s.seed({ balance: 100 }); const a = s.begin(level); const first = a.read('balance'); const b = s.begin(level); b.write('balance', 50); b.commit(); return [first, a.read('balance')]; } function onCall(level) { // the doctors' write skew const s = new Store(); s.seed({ alice: true, bob: true }); const a = s.begin(level), b = s.begin(level); const seenByA = a.scan((v) => v === true).length; const seenByB = b.scan((v) => v === true).length; if (seenByA >= 2) a.write('alice', false); if (seenByB >= 2) b.write('bob', false); const out = []; for (const [name, t] of [['alice', a], ['bob', b]]) { try { t.commit(); out.push(`${name} committed`); } catch (e) { out.push(`${name} aborted (${e.code})`); } } out.push(`${s.begin('read committed').scan((v) => v === true).length} on call`); return out; } console.log(nonRepeatableRead('read committed')); // [ 100, 50 ] console.log(nonRepeatableRead('repeatable read')); // [ 100, 100 ] console.log(onCall('repeatable read')); // [ 'alice committed', 'bob committed', '0 on call' ] console.log(onCall('serializable')); // [ 'alice committed', 'bob aborted (40001)', '1 on call' ] ``` Try the others yourself: have a `read uncommitted` reader peek at a pending write and then roll it back (a dirty read), scan a set before and after another transaction inserts a key (a phantom), or let two `read committed` transactions each compute `balance + n` from the same starting value (a lost update). The serializable check here is deliberately simple: abort if anything you read was changed after your snapshot. Postgres's real algorithm is smarter, and aborts fewer transactions. * * * ## 10\. Which level should you use? ```mermaid flowchart TD A["A transaction reads data, then writes based on what it read"] --> B{"Is every statement atomic on its own?
(UPDATE x = x + 1, constraints guard the rules)"} B -->|Yes| RC["Read Committed, the default, is usually enough"] B -->|No| C{"Is it read-only but needs one consistent view
across several queries? (report, export)"} C -->|Yes| RR["Repeatable Read, read-only"] C -->|No| D{"Does a rule span several rows
that you read and then change?"} D -->|Yes| SER["Serializable plus retries,
or lock the rows you read,
or a one-row guard"] D -->|No| LOCK["Lock the one row (FOR UPDATE)
or use a version column"] ``` The pattern behind the flowchart: **start with the default, make each statement atomic, declare rules as constraints, and add stronger isolation or locks only where a specific anomaly can actually hurt you.** Then _test_ that anomaly, because (as Hermitage shows) the name of a level is a weak guide. * * * ## 11\. The mental model on one screen 1. **COMMIT waits for the log, not the tables.** Durability comes from a sequential write-ahead log flushed to disk; data pages follow later. 2. **MVCC means updates create versions and readers see snapshots.** Readers do not block writers, but old versions must be cleaned up. 3. **Isolation is a dial.** Read Committed takes a new snapshot per statement; Repeatable Read takes one per transaction; Serializable adds detection of dangerous patterns. 4. **The classic anomalies are dirty read, non-repeatable read, phantom, lost update and write skew.** Know which your level allows _in your database_. 5. **Names do not transfer.** "Repeatable read" differs between Postgres and MySQL; Oracle's "serializable" is snapshot isolation; SQLite serializes writers. 6. **Prefer explicit protection:** atomic updates, constraints, `FOR UPDATE`, version columns, one-row guards. Reach for Serializable when rules span rows. 7. **When the database says no, retry.** Serialization failures and deadlocks are part of the contract. Keep transactions short and side-effect free. * * * ## Further reading - [PostgreSQL docs: Transaction Isolation](). The authoritative description of Read Committed, Repeatable Read and Serializable, including the serialization failure behavior. - [PostgreSQL docs: Reliability and the Write-Ahead Log](). Why only the log needs flushing at commit, and how recovery works. - [PostgreSQL docs: Explicit Locking](). Row-level locks, `FOR UPDATE`, and deadlocks. - [MySQL docs: InnoDB Transaction Isolation Levels](). How consistent reads and locking reads differ at Repeatable Read. - [SQLite docs: Isolation In SQLite](). Serializable by serializing writers, and snapshot reads in WAL mode. - [Hermitage](), Martin Kleppmann's test suite for isolation anomalies across databases, and his post [Hermitage: Testing the "I" in ACID](). - [A Critique of ANSI SQL Isolation Levels](), Berenson, Bernstein, Gray, Melton, O'Neil and O'Neil (SIGMOD 1995). The paper that defined snapshot isolation and exposed the gaps in the standard. - [Jepsen: Consistency Models](). A map of how isolation and consistency models relate. - _Designing Data-Intensive Applications_ by Martin Kleppmann, chapter 7 (transactions), for the clearest book-length treatment of everything in this post. If this post helped, send it to the teammate who just wrote `if (count > 1) { leaveShift() }` and called it safe.