Transactions: The Safety Net That Makes Concurrent Databases Sane

A transaction either fully commits (all writes visible) or fully aborts (no writes visible). If the database crashes mid-transaction, it rolls back any partial writes on recovery. The name is unfortunate — "abortability" would be more accurate.

The database starts in a valid state and ends in a valid state. This is actually a property of the application — the application must define what "valid" means using constraints and invariants. Consistency is not really a database guarantee.

Concurrently executing transactions are isolated from each other — as if they executed serially. The actual isolation level varies significantly (see table below). This is the dimension that really matters for concurrent systems.

Once a transaction commits, its data is safe even if the database crashes immediately after. Achieved via write-ahead logging, fsync to disk, or replication to multiple nodes. Durability is a spectrum — more replication = more durable.

Writes by transactions that were in-progress (not yet committed) when this transaction started are invisible

Writes by transactions with a higher transaction ID than this transaction's start snapshot are invisible

Writes by aborted transactions are always invisible (garbage collector removes them later)

All other writes — by committed transactions with IDs lower than this snapshot — are visible

Transaction B overwrites a value that transaction A has written but not yet committed. A then aborts, leaving B's state inconsistent.

Solution: Hold a row-level lock on writes until commit/abort

Transaction B reads data written by transaction A before A commits. If A aborts, B has seen invalid data that was rolled back.

Solution: Database keeps the old committed value; reads see the old value until commit

Two concurrent read-modify-write cycles — the second overwrites the first without seeing it. One update is silently discarded.

Solution: Atomic operations, explicit locking (SELECT FOR UPDATE), or automatic detection + abort

Transaction reads the same row twice and sees different values because a concurrent transaction committed between the reads.

Solution: MVCC — transaction reads from a frozen snapshot taken at its start

Two transactions each read the same objects, make a decision based on the read, then write to different objects — violating a multi-object constraint.

Solution: Serializable isolation (SSI, 2PL) or explicit row locking (SELECT FOR UPDATE)

A transaction executes a search query, then a concurrent transaction inserts/deletes rows matching that search condition, changing the result of the query.

Solution: Predicate locks or index-range locks (2PL), SSI detects conflicts at commit time

Atomic Database Operations

Most databases provide atomic increment/update operations (e.g., UPDATE counter SET value = value + 1). These are concurrency-safe because the database serializes them. Best when the operation can be expressed as a single statement. ORMs often accidentally bypass these.

Explicit Locking (FOR UPDATE)

Use SELECT ... FOR UPDATE to lock the rows you intend to modify. Other transactions must wait until you commit. Required when the update logic cannot be expressed as a single atomic operation (e.g., game move validation). Risk: easy to forget a lock, introducing subtle race conditions.

Automatic Detection

PostgreSQL, Oracle, and SQL Server's snapshot isolation automatically detect lost updates and abort the offending transaction. Application must retry aborted transactions. MySQL/InnoDB's repeatable read does not detect lost updates — it silently allows them.

Compare-and-Set (CAS)

Only update if the value matches what you last read: UPDATE ... WHERE value = [old_value]. If another transaction changed the value, the update has no effect and must be retried. Note: under MVCC, the WHERE clause may be evaluated against a snapshot that doesn't see the concurrent change — implementation-dependent.

Actual Serial Execution

Examples: VoltDB/H-Store, Redis (single-threaded), Datomic

Two-Phase Locking (2PL)

Examples: MySQL InnoDB serializable, IBM Db2 repeatable read, old SQL Server serializable

Serializable Snapshot Isolation (SSI)

Examples: PostgreSQL (since 9.1), CockroachDB, YugabyteDB

Transactions are Safety Nets, Not Performance Killers

The instinct to disable transactions for speed is usually wrong. The real performance enemies are long-running transactions and cross-shard coordination — not transactions themselves. Modern SSI (PostgreSQL) has very low overhead at typical contention levels.

ACID is Not a Spectrum — Isolation Is

"We use ACID" often means nothing more than read-committed isolation. The four ACID properties (Atomicity, Consistency, Isolation, Durability) are real, but isolation is the dimension that actually varies. Always ask: which isolation level?

MVCC: Readers and Writers Never Block Each Other

Snapshot isolation's key insight: maintain multiple versions of each row, let each transaction read from its own consistent snapshot. Writes create new versions. Garbage collection removes versions no longer visible to any active transaction.

Write Skew is the Hardest Anomaly to Reason About

Unlike lost updates (two writes to the same object), write skew involves two transactions reading the same set of objects and writing to different objects. The constraint violation is invisible to both transactions individually — it emerges from their interaction.

SSI vs 2PL: Optimistic vs Pessimistic Concurrency

2PL assumes conflicts will happen and prevents them upfront (pessimistic). SSI assumes conflicts are rare and detects them at commit (optimistic). Under low contention, SSI wins. Under high contention with many aborts, 2PL may be preferable.

Serial Execution is Underrated

VoltDB proved that a single-threaded serializable database can outperform a multi-threaded one under OLTP workloads when data fits in RAM and transactions are short. The coordination overhead of concurrency can exceed its benefits.