ACID Isolation Locking Lab
Simulate a checkout race to see how ACID isolation, row locks, table locks, MVCC snapshots, range locks, and serializable retries keep database state correct.
Continue with the guided workspace
Use the paired workspace to connect this guide's mental model with live component state, visible signals, and the outcome each step or scenario is meant to prove.
ACID is easiest to understand when a real business invariant is at stake. In this lab, two buyers race to buy the last unit of sku=42. The database has to produce a result that is both fast enough for checkout and correct enough that inventory never goes below zero.
The important lesson is that “using a transaction” is not the whole answer. A transaction gives work a boundary, but isolation decides what concurrent transactions can see and whether they wait, proceed, abort, or retry. The simulator lets you compare those choices directly: no isolation oversells, a row lock queues the second buyer, a table lock is safe but broad, MVCC changes read visibility, range locks explain phantoms, and Serializable turns an unsafe race into a retry.
Mental Model
Think of isolation as a traffic controller between application intent and table state. Buyer A and Buyer B both want to update the same inventory row. The Transaction Manager accepts their operations, the Lock Manager decides whether a row/table/range conflict must wait, the MVCC Version Store decides which row version a read may see, and the WAL records the commit or abort path that becomes durable.
Correctness comes from keeping the invariant visible: stock >= 0 and “one physical unit backs one committed order.” If the simulator shows two committed orders with stock -1, the system is fast but wrong. If it shows one committed order, one rejected/retried transaction, and a flushed WAL record, the system preserved ACID behavior.
Components and Why They Exist
Buyer A and Buyer B
The buyers are concurrent application transactions. They expose the race by trying to read and update the same stock row at nearly the same time. Buyer A usually creates the first write pressure; Buyer B reveals whether the database makes the second transaction wait, read a snapshot, observe dirty data, or retry.
Transaction Manager
The Transaction Manager owns BEGIN, COMMIT, ABORT, and retry decisions. It coordinates reads, writes, lock requests, MVCC visibility, and WAL append order. Watch this component when Serializable is active: it can reject a schedule that would not be equivalent to one-at-a-time execution.
Lock Manager
The Lock Manager is where row, table, and range conflicts become visible. A row lock keeps the conflict narrow: only inventory.sku=42 waits. A broad table lock protects correctness but blocks unrelated inventory writes. A range/next-key lock protects a predicate, not only rows that already exist.
MVCC Version Store
MVCC stores committed and uncommitted row versions and applies the current visibility rule. Read Committed can see a newer committed version on a later statement. Repeatable Read keeps a stable snapshot. The weak dirty-read branch shows why exposing uncommitted versions is unsafe for checkout decisions.
Inventory and Orders Tables
The Inventory table holds the invariant. The Orders table shows whether that invariant is backed by real committed rows or broken by duplicate reservations. When the phantom scenario is active, Orders becomes the range-query example: a new matching open reservation can appear between two reads unless the predicate is protected.
WAL / Commit Log
The WAL makes the chosen outcome durable and explains why ACID is not only about locks. A bad commit can be durable too, so the isolation path must protect correctness before the log confirms the result. Abort and retry records show how a safe database backs out unsafe work.
Edge Cases and Misconceptions
- A transaction without isolation can still commit the wrong answer. Atomic commit only makes the wrong state durable if the schedule was unsafe.
- Row locks are usually the right checkout shape. They protect the exact stock row while preserving concurrency for unrelated SKUs.
- Table locks are not “more correct” for every case. They can be correct, but they often block too much work for a hot path.
- MVCC does not mean writers never conflict. Readers can avoid blocking writers, but two writers touching the same row still need conflict handling.
- Read Committed is not a stable view. It hides dirty reads but allows a transaction to see different committed values across statements.
- Phantoms are about predicates. Locking one existing row does not protect the empty space where another matching row can be inserted.
- Serializable failures are a feature, not a bug. The abort tells the application to retry instead of committing a schedule that could break invariants.
Use the ACID Isolation Locking Lab simulator to compare the outcomes under each isolation level and lock scope.
Common Questions
Why does a checkout system need isolation instead of only transactions?
A transaction gives one unit of work a begin/commit/abort boundary, but isolation decides what happens when two transactions touch the same data at the same time. Without isolation, two buyers can both read the last unit as available and both commit, producing oversold inventory. The isolation mechanism makes one buyer wait, see a safe snapshot, or retry.
What is the difference between a row lock and a table lock?
A row lock protects one specific row, such as inventory.sku=42, so another transaction trying to update that same row waits while unrelated rows can still proceed. A table lock protects the whole table at the selected lock mode, which can be correct but much broader than the conflict needs. Table locks are useful for coarse administrative or DDL-style operations, but they reduce concurrency for high-traffic checkout paths.
How does MVCC change what readers see?
MVCC keeps multiple committed row versions and gives each transaction or statement a visibility rule. Read Committed usually takes a fresh committed snapshot for each statement, so a reread can see a newer committed version. Repeatable Read keeps a stable transaction snapshot, so repeated reads see the same committed version even if another transaction commits later.
What is a dirty read?
A dirty read happens when one transaction reads another transaction's uncommitted change. If the writer later aborts, the reader acted on a value that never became real. That is why dirty reads are dangerous for business decisions such as accepting an order, showing an account balance, or shipping inventory.
What is a phantom read and why can row locks be insufficient?
A phantom read happens when a transaction repeats a range query and sees new rows that match the predicate because another transaction inserted them. Locking only the rows that already exist does not protect the gaps where new matching rows can appear. Engines use predicate locks, key-range locks, or next-key locks to protect the searched range.
Why can Serializable abort a transaction even when the SQL looks valid?
Serializable isolation has to make the outcome equivalent to running transactions one at a time. If concurrent reads and writes form a schedule that cannot be safely ordered, the database aborts one transaction with a serialization failure. The application retries, reads the new committed state, and commits only if the invariant still holds.
Does ACID make a database slow?
ACID does not automatically make a database slow, but stronger isolation and durability have real costs. Locks can make transactions wait, serializable checks can force retries, and durable commits require log flushes. The design goal is to choose the weakest isolation level and narrowest lock scope that still protects the business invariant.