تراکنش‌ها (Transactions)Transactions

The chapter revolves around five ideas:

• Real meaning of ACID: atomicity in ACID means “abortability”, not “thread-level atomicity”; consistency means the application's own invariants.
• Isolation levels and anomalies: each weak level leaves a class of race conditions behind and hands their repair to the application.
• MVCC: the basis of snapshot isolation — keeping several versions of each row and filtering them with visibility rules.
• Three roads to serializability: serial execution, two-phase locking (2PL) and serializable snapshot isolation (SSI).
• Distributed: the atomic-commit problem, the 2PC protocol, getting stuck in-doubt, and why idempotency is often better than XA.

In real data systems many things can break: the database software or hardware can die at any moment (even mid-write); the application can crash mid-operation; network cuts separate app from database or nodes from each other; concurrent clients overwrite each other; a client reads half-updated data and race conditions breed bugs.

A transaction groups reads and writes into one logical unit: conceptually all run as a single operation; either the whole transaction succeeds (commit) or it fails (abort/rollback) and the app can safely retry. Error handling gets far simpler — no more worrying about partial failure, where some operations succeed and others don't.

Transactions aren't a law of nature; they were invented to simplify the programming model. Not every app needs them, and weakening guarantees (for performance or availability) is sometimes the right trade. But without transactions, every failure scenario is your own responsibility — plus the hard testing work. (The famous UK Post Office Horizon scandal was likely rooted in missing ACID transactions in the accounting system.)

Härder and Reuter coined the acronym in 1983 to pin down fault-tolerance terms. But in practice one database's ACID differs from another's — isolation especially is ambiguous. Non-ACID systems are sometimes called BASE (Basically Available, Soft state, Eventual consistency), which is vaguer still; the only sane definition of BASE is “not ACID”!

Atomicity and isolation assume you want to change several objects (rows, documents, records) at once. Classic example: an email app keeping the unread count in a separate denormalized field. Each new mail means both inserting the row and bumping the counter. If the two aren't seen together, the user sees an unread message with a zero counter — exactly what isolation prevents, and atomicity rolls both back if an error strikes mid-way.

A multi-object transaction must know which operations belong to it; in relational databases that's usually the TCP connection (between BEGIN TRANSACTION and COMMIT), while non-relational stores often have no such thing. Even a multi-key API (like multi-put) doesn't necessarily have transactional semantics — some keys may succeed while others fail.

Atomicity and isolation also matter at the single-object level: if the network drops mid-write of a 20 KB JSON document, no corrupt half-document may be stored; a concurrent reader must not see a half-updated value. Storage engines almost always provide single-object, single-node atomicity and isolation (log for recovery, object lock for isolation). Operations like atomic increment and conditional write (compare-and-set, CAS) help too — but they aren't “transactions” in the usual sense (e.g. Cassandra's lightweight transactions only guarantee one object).

The classic “transaction states” model — the state diagram from database textbooks — is the best mental map for atomicity: every transaction starts Active and ends Committed or Aborted. The fateful moment sits exactly between “everything finished” and “not yet durable”: where the storage engine is making writes persistent on disk.

If two transactions touch disjoint data, or both are read-only, running them in parallel is safe. Trouble starts only when one reads what another concurrently changes, or both write the same thing. These race conditions rarely show up in tests (they depend on timing), which is why databases use isolation to hide concurrency from the programmer. But serializability costs, and most databases settle for weaker levels — harder to understand, breeding subtle bugs.

The most basic isolation level (default in Oracle, PostgreSQL and SQL Server) gives two guarantees:

• No dirty reads: you read only committed data; an in-flight transaction's writes stay invisible to others until commit (then appear all at once).
• No dirty writes: you overwrite only committed data; if two transactions write one row, the second waits for the first's commit/abort.

Dirty writes are usually stopped with a row-level lock held to end of transaction. For dirty reads there are two options: a read lock (bad in practice — a slow writer stalls readers) or the common approach — keeping two versions of the row: the old committed value and the in-flight transaction's new value; until commit, readers see the old one.

But read committed doesn't stop everything: the counter example (two concurrent increments) still breaks, because the second write happens after the first commits — not a dirty write. That phenomenon is lost update, covered later. A weaker level, read uncommitted, even accepts dirty reads (only dirty writes are barred).

read committed has a famous flaw: read skew. Imagine $1000 split across two $500 accounts, and a transaction moving $100 between them. Reading both balances mid-move, you might see one before the deposit ($500) and the other after the withdrawal ($400) — as if $100 evaporated! This read skew is a kind of nonrepeatable read, deemed “acceptable” under read committed.

For backups and long analytical queries that's intolerable: an hours-long backup must not mix old and new versions. The common fix is snapshot isolation: each transaction reads from a consistent snapshot — everything committed at its start — blind to others' later changes. The standard implementation is MVCC: instead of two versions, several committed versions of each row are kept, and visibility rules decide which version each transaction sees. Golden rule: readers never block writers, writers never block readers.

The confusion is rooted in the SQL standard: it doesn't know snapshot isolation at all (written against 1975's System R) and its repeatable read definition is vague and implementation-dependent. Result: nobody really knows what “repeatable read” means! Always talk about behavior, never the name.

Diagram 1 showed the data path; this one shows the “structure”: which classes exist in an MVCC engine, their fields, and how they connect — exactly what a UML class diagram shows. The right side is an “object diagram”: the same classes filled with the real accounts example.

lost update is the read-modify-write cycle: read a value, change it, write it back. Two concurrent transactions doing this lose one change (the second write covers the first). Examples: bumping a counter, updating an account balance, appending to a list inside a JSON document, or two users saving one wiki page at once.

Suppose you're writing hospital on-call scheduling: the rule is at least one doctor stays on call, but any doctor may take leave if a colleague covers. Aaliyah and Bryce both fall ill and press “I'm off call” nearly simultaneously. Each transaction first checks (2 doctors on call — fine) then updates its own record. Both commit — and now nobody is on call!

This anomaly is write skew: neither dirty write nor lost update, since the two transactions change two different objects. Best definition: a generalized lost update — a decision made on stale data whose premise is no longer true by write time.

Almost every write-skew example follows a three-step pattern:

1. A SELECT with a search condition checks whether the requirement holds (at least two doctors on call? no booking overlapping this room in this slot? this chess square empty? this username free? balance sufficient?).
2. The application decides from the result (proceed or error).
3. If proceeding, it writes (INSERT/UPDATE/DELETE) and commits.

But that very write invalidates step 2's premise: re-running the same SELECT gives a different answer. The effect one transaction's write has on another's search result is called a phantom. Snapshot isolation stops phantoms in read-only queries — but not in writing transactions.

Subtle point: in the doctors example the changed row is one the SELECT returned — so SELECT ... FOR UPDATE could lock it. But in the other examples the check tests for the absence of a row, and the write creates a new row breaking the condition; when SELECT returns nothing, FOR UPDATE has nothing to lock!

All anomalies seen so far, summarized in one table — the most interview-used table of the chapter:

Reading notes: dirty writes aren't in the table because nearly every implementation (with row locks) prevents them; the lost-updates column carries “?” because some snapshot-isolation implementations (PostgreSQL) detect them and others (MySQL/InnoDB) don't; and write skew is closed only by genuine serializability.

• Dirty reads: another client reads a not-yet-committed write — read committed and above stop them.
• Dirty writes: another client overwrites a not-yet-committed write — nearly everyone stops them.
• Read skew: a client sees parts of the database at different moments (a nonrepeatable read) — cure: snapshot isolation with MVCC.
• Phantom reads: another's write changes a transaction's search result — snapshot isolation stops its read-only version; phantoms in write skew need index-range locks and the like.
• Lost updates: two clients read-modify-write concurrently and one covers the other's change — some snapshot isolations stop it themselves, others need SELECT FOR UPDATE.
• Write skew: a transaction decides on what it read and writes, but the decision's premise is void by write time — only serializability stops it.

The unpleasant state of affairs: isolation levels are hard to grasp and inconsistent, telling whether application code is safe at a level is difficult, and no good tool finds race conditions (testing them is non-deterministic). Researchers' answer since the 1970s is simple: serializable isolation — guaranteeing the outcome equals a serial, concurrency-free execution, stopping every race. So why doesn't everyone use it? Cost. Three main techniques exist today.

The simplest cure for concurrency is removing it: one transaction at a time, on one thread. Then no conflicts exist to detect, and isolation is serializable by definition. The idea wasn't practical until the 2000s; two shifts enabled it: cheap RAM (the whole active dataset fits in memory) and short OLTP transactions with few reads/writes. Long analytical queries are read-only anyway and can run outside the serial loop on a snapshot.

But an interactive transaction (one SQL statement, read the answer, next statement…) spends most time on network round-trips with the application; with one transaction at a time, throughput collapses. So these systems reject multi-statement interactive transactions: each transaction is either a single statement or delivered whole as a stored procedure. With in-memory data and no I/O waits, single-threaded serial execution can sustain excellent throughput.

Stored procedures have a bad reputation: each vendor's proprietary language (PL/SQL, T-SQL, PL/pgSQL) grew ugly, and managing them (debugging, version control, testing, monitoring) is hard — one bad procedure can floor a shared database. But modern versions use general-purpose languages: VoltDB with Java/Groovy, Datomic with Java/Clojure, Redis with Lua, MongoDB with JavaScript. VoltDB even replicates by running the same procedure on every replica (state machine replication), so procedures must be deterministic.

Scaling out shards the data (Chapter 7): if each transaction reads and writes a single shard, every shard gets its own thread and throughput grows linearly with cores. But cross-shard transactions need lockstep coordination and are very slow (VoltDB manages ~1000 multi-shard writes per second — orders of magnitude below single-shard). Bottom line: transactions must be small and fast, the active data must fit in memory, and write throughput must fit one core — or be shardable without cross-shard coordination.

For ~30 years the only common serializability algorithm was two-phase locking (2PL). The rule is simple: concurrent reads of an object are free while nobody writes; but a write needs exclusive access — a writer blocks other writers and readers alike, and a reader blocks writers. (Exactly the opposite of snapshot isolation's slogan.)

Implementation: shared locks for reads (many concurrent readers), exclusive for writes (nobody else), with shared-to-exclusive upgrade. “Two-phase” comes from its two phases: growth (taking locks during execution) and shrinkage (releasing all at the end); after the first release, no new lock may be taken. Deadlock (mutual waiting) is detected by the database, which aborts one party so the rest proceed.

2PL's price is performance: not just lock overhead, but reduced concurrency matters more. A transaction reading a whole table (backup, analytical query, health check) must take a shared lock on the whole table: first it waits for in-flight writes, then all writers stall until its read ends — the database effectively closes for writes for a while. Latency turns unstable and deadlocks (especially at serializable) grow frequent and costly (retry and redo).

Two tools stop phantoms: predicate locks (a conceptual lock on every object matching the search condition — even ones not yet existing; but lock matching is slow) and its practical approximation, index-range locking (next-key locking): a shared lock on the index entry or index range covering the condition. Approximation sometimes locks more than needed, but overhead is far lower. Without a suitable index, the database falls back to a whole-table lock.

Are serializability and good performance inherently at odds? Apparently not: serializable snapshot isolation (SSI), introduced in 2008, gives full serializability at a small penalty over snapshot isolation. SSI is optimistic: transactions proceed unblocked and are checked at commit; if their execution wasn't serializable, they're aborted. (2PL is pessimistic by contrast: it waits whenever danger is sensed.)

SSI builds on snapshot isolation and tracks two conflict types. First, reading a stale MVCC version: if a transaction ignored another's write via visibility rules and that transaction has committed by our commit time, we decided on a “stale premise” and must abort. Why not abort immediately? The transaction may be read-only (no write skew possible) or the other write may still be uncommitted — this delay keeps long reads safe.

Second, a write affecting an earlier read: using a technique resembling index-range locks — but without blocking — it records which transactions read what data. When a write lands in that range, instead of a lock a “tripwire” fires: readers learn their view may be stale. Finally, the transaction committing later whose conflict with a committed transaction is proven gets aborted.

In a single-node transaction, the storage engine implements atomicity itself: first it writes data durably (usually to the WAL), then appends the commit record. A mid-way crash recovers from the log on restart: commit record present means committed, absent means rolled back. The decision moment is finishing the commit record's write on one disk.

But in a distributed transaction (several shards, or a global index on another node), nodes can't each be told “commit yourself”: some may succeed and others fail — constraint violations here, messages lost in the network there, crashes before writing the commit record elsewhere. If some commit and others abort, nodes diverge permanently; data committed on one node and read by others can never be taken back. This is atomic commitment: all commit, or all abort.

2PC is a blocking protocol: after voting yes, a participant can only wait for the coordinator. Even a timeout doesn't help — if Database 1 aborts unilaterally while Database 2 committed, they diverge; unilateral commit is unsafe for the same reason. And while in doubt, the transaction holds its locks: if the coordinator takes twenty minutes to return, nobody can touch that data for twenty minutes. If the coordinator's log is fully destroyed, the locks stay forever — a database restart won't fix it, since 2PC correctness demands in-doubt locks survive restart. The only exit is a sysadmin's manual decision.

XA (eXtended Architecture) is the standard for 2PC across heterogeneous tech (since 1991; in Java via JTA/JDBC/JMS). But its problems are serious: the coordinator is usually a library inside the application's own process, so an app crash loses the coordinator and its log; the database can't talk to the coordinator directly — everything flows through application code (so the app code itself is a single point of failure); and since XA must be the common floor of all systems, it has neither cross-system deadlock detection nor SSI support. Amid operational chaos, many cloud services simply don't implement distributed transactions.

By contrast, internal distributed transactions (all participants one database with identical software: CockroachDB, TiDB, Spanner, FoundationDB, YugabyteDB, or Kafka) fare far better: they still use 2PC but replicate the coordinator via consensus (automatic failover), let the coordinator talk to shards directly, replicate the shards themselves, and pair atomic commit with distributed concurrency control (deadlock detection, consistent reads).

One major use of distributed transactions: a queue message must be processed “exactly once” — the queue ack only once the message's resulting database writes commit. But 2PC isn't really needed; a simpler pattern needs only a transaction inside the database:

1. Each message has a unique id, and you keep a table of processed ids in the database. Starting processing = starting a transaction and checking the id; if duplicated, just ack and drop.
2. If new, insert the id in the same transaction, process the message (all further writes in that same transaction) and commit.
3. After a successful commit, ack the message to the queue.
4. After acking, delete the id (in a separate transaction) so the table stays small.

A mid-way crash aborts the transaction and the message is redelivered; the second attempt sees the id and only acks. Message processing has become idempotent: repetition creates no repeated side effect. (If a retry arrives even before the earlier transaction aborts, the unique constraint blocks the duplicate insert.) Stream frameworks like Kafka Streams use this same pattern for exactly-once.

If Diagram 5 was the “message flow”, this one shows each participant's “inner states” — and the crux sits exactly here: the Prepared state. Before it, a participant may abort any time; after it, it has surrendered the right to decide and must wait for the coordinator.

• Mixing up 2PL and 2PC — one is isolation, the other atomic commit.
• Defining ACID atomicity like “thread-level atomicity” — that belongs to isolation; atomicity means all-or-nothing with abort.
• Saying “consistency means replica convergence” when defining ACID — in ACID it means the application's own invariants.
• Believing “every ACID database stops all race conditions”; in practice isolation is usually weak.
• Prescribing “use ACID” for a money problem without asking the isolation level.
• Forgetting lost update happens with post-commit writes too — it isn't a dirty write.
• Saying “SELECT FOR UPDATE always fixes write skew” — with phantoms there's no row to lock.
• Saying “snapshot isolation stops every anomaly”; write skew is fully possible and lost update depends on implementation.
• Equating “repeatable read” across databases (PostgreSQL ≠ MySQL ≠ Db2).
• Equating “atomic increment” with ACID atomicity; that's thread-sense atomic.
• Forgetting the C in ACID: undeclared invariants let the database store bad data.
• Saying “2PC guarantees commit”; 2PC only gives atomicity and may abort everything or get stuck.
• Missing in-doubt: after voting yes a participant holds its locks till decided, and timeouts don't help.
• Saying “exactly-once needs distributed transactions”; idempotency with an in-database transaction suffices.
• Recommending XA for every multi-system scenario, ignoring the coordinator single point of failure and operational cost.
• Forgetting that retrying an aborted transaction without deduplication can duplicate effects, and retry under overload worsens things.
• Saying “SSI means lighter locks”; SSI has no blocking locks at all — it's optimistic.
• Believing “serial execution means a slow database”; with stored procedures and in-memory data it can be very fast — while transactions stay small.

1. Define transactions and the four ACID letters — C means the app's invariants and “abortability” is the better name for A.
2. Name the six anomalies and say at which isolation level each is possible (table below the questions).
3. Explain MVCC in three sentences: update = delete+insert, version chains, visibility rules.
4. The four lost-update remedies + which databases detect it themselves.
5. Define write skew with the doctors example and say why object locks don't help.
6. The three serializability methods + one benefit and one price each.
7. Define 2PC in six steps and mark the two points of no return; 2PL ≠ 2PC.
8. The in-doubt problem and the XA knot in two sentences, and the idempotency alternative for exactly-once in one.