Chapter 06 · Data Modification, Transactions, Isolation, Locks, and Deadlocks
Isolation Levels, Consistent Reads, Locking Reads, MVCC, and Concurrency Anomalies
Observe MariaDB/InnoDB MVCC, consistent snapshots, current locking reads and isolation-level differences with two-session timelines and modern snapshot-isolation behavior.
Learning outcomes
Two dispatchers can read and change the same work order at nearly the same time. A plain SELECT may legitimately return an older committed version from a transaction snapshot while an UPDATE needs to act on current lockable state. Without a mental model for multi-version concurrency control (MVCC), developers interpret this as “MariaDB caching stale data” and try to solve a transaction problem with cache flushes or stronger locks everywhere.
InnoDB keeps multiple row versions so consistent reads can see
an appropriate snapshot while writers create newer committed
versions. Isolation level controls snapshot lifetime and locking
rules. On the 12.3.2 course baseline, InnoDB’s default isolation
level is REPEATABLE READ, and
innodb_snapshot_isolation is ON by default on
modern supported versions; that setting adds write/write
conflict detection within InnoDB and is specifically relevant
when a transaction attempts to lock data not represented in its
read view. Always verify effective settings on the exact target
series.
Explain MVCC, row versions, snapshots/consistent reads and current/locking reads in practical terms.
Demonstrate READ COMMITTED versus REPEATABLE READ with two sessions and a visible timeline.
Relate dirty, nonrepeatable and phantom phenomena to actual SQL rather than definitions alone.
Use FOR UPDATE or LOCK IN SHARE MODE only inside an effective transaction and understand their current-read role.
Recognize the 12.3.2 snapshot-isolation setting as version-sensitive behavior rather than a portable assumption.
Open two MariaDB clients connected to
servicehub_tx_lab. Label them Session A and
Session B. Keep every transaction disposable: COMMIT or
ROLLBACK before moving to the next scenario, and reset changed
rows if needed.
1. Inspect the isolation contract first
SELECT @@autocommit, @@transaction_isolation, @@innodb_snapshot_isolation;SHOW VARIABLES LIKE 'transaction_isolation';SHOW VARIABLES LIKE 'innodb_snapshot_isolation';REPEATABLE READ is the default InnoDB isolation level documented by MariaDB. A default is not a reason to hard-code an assumption: sessions can change isolation, connectors can issue SET commands, and upgrades can change surrounding InnoDB behavior. Store the effective values with test evidence when reproducing a concurrency bug.
2. MVCC means readers can use a versioned snapshot
A consistent read is a plain InnoDB SELECT that does not request a row lock. Under MVCC, it can read a version that was committed before its snapshot even if a newer committed version now exists physically. This allows readers and writers to overlap more than a design that locks every row for every read.
| Concept | Practical meaning |
|---|---|
| Row version | A logical row may have older versions reachable through InnoDB undo information while transactions still need them. |
| Read view / snapshot | The visibility boundary deciding which committed versions a consistent read may see. |
| Consistent read | Plain SELECT reading according to the transaction’s snapshot/isolation rules. |
| Current/locking read | A read such as SELECT ... FOR UPDATE that must inspect lockable current state rather than merely reuse an old consistent-read result. |
| Undo | Information supporting rollback and older-version reconstruction; long transactions can keep history relevant longer. |
MVCC is not “no locks.” UPDATE, DELETE, INSERT duplicate checks, foreign keys and locking reads still acquire locks. Chapter 07 studies undo, redo, purge and history in storage detail; here, MVCC is the visibility model needed to reason about concurrent SQL.
3. REPEATABLE READ: one transaction, stable consistent snapshot
MariaDB documents REPEATABLE READ consistent reads as using the
snapshot established by the first consistent read in the
transaction. The following timeline makes that visible. Reset
work order 1001 to estimated_cost=120.00 first.
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;SELECT estimated_cost FROM work_orders WHERE work_order_id=1001;-- Keep Session A open.UPDATE work_ordersSET estimated_cost=150.00, version_no=version_no+1WHERE work_order_id=1001;COMMIT;SELECT estimated_cost FROM work_orders WHERE work_order_id=1001;SELECT estimated_cost FROM work_orders WHERE work_order_id=1001;COMMIT;SELECT estimated_cost FROM work_orders WHERE work_order_id=1001;Inside Session A’s original REPEATABLE READ transaction, the second plain SELECT should remain consistent with its snapshot. After COMMIT, a new statement/transaction can see the committed value from Session B. This is not stale cache behavior; it is the isolation contract.
4. READ COMMITTED establishes fresher consistent-read snapshots
Under READ COMMITTED, each consistent read sees a snapshot appropriate to that statement, so a second SELECT in the same transaction can observe another transaction’s intervening commit. This makes nonrepeatable reads possible by design.
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;SELECT estimated_cost FROM work_orders WHERE work_order_id=1002;-- Keep open.UPDATE work_ordersSET estimated_cost=205.00, version_no=version_no+1WHERE work_order_id=1002;COMMIT;SELECT estimated_cost FROM work_orders WHERE work_order_id=1002;COMMIT;READ COMMITTED can reduce some gap-locking behavior and improve concurrency for certain workloads, but it changes the application’s visibility contract. Do not choose an isolation level from a generic “performance ranking.” Choose it from invariants and test the exact workload, lock behavior and retry policy.
5. Dirty, nonrepeatable and phantom phenomena
| Phenomenon | What it means | MariaDB/InnoDB reasoning |
|---|---|---|
| Dirty read | Reading data another transaction has not committed. | Possible under READ UNCOMMITTED; avoid building correctness on uncommitted state. |
| Nonrepeatable read | Re-reading one row yields a newer committed value. | Expected possibility under READ COMMITTED; consistent reads are statement snapshots. |
| Phantom | Repeating a predicate yields additional/removal rows that now match. | Snapshot reads and locking range reads handle this differently; REPEATABLE READ consistent snapshots remain stable, while range locking can prevent inserts into covered gaps. |
| Lost-update style race | Two actors read old state and both write derived state. | Prevent with locking reads, atomic conditional UPDATE, version columns/optimistic checks, or other explicit concurrency control—not by assuming a SELECT reserves the row. |
An anomaly table is only useful when connected to business
invariants. For example, “two dispatchers must not both claim
the same queued work order” can be encoded atomically with
UPDATE ... WHERE status='queued' and a row-count
check, or with a transaction that locks the row before deciding.
The right technique depends on the workflow.
6. Locking reads are not ordinary snapshot reads
SELECT ... FOR UPDATE asks InnoDB to lock selected
records for an intended write;
LOCK IN SHARE MODE takes shared locks that allow
reads but block conflicting modifications. MariaDB documentation
notes that, for InnoDB, these clauses matter when autocommit is
disabled or the SELECT is enclosed in an explicit transaction. A
standalone autocommit SELECT does not provide a useful lock
lifetime for an application workflow.
START TRANSACTION;SELECT work_order_id,status,technician_idFROM work_ordersWHERE work_order_id=1003FOR UPDATE;UPDATE work_ordersSET technician_id=104, status='open', version_no=version_no+1WHERE work_order_id=1003 AND status='queued';COMMIT;
Locking reads act on current lockable state, not merely on the
old snapshot used by a prior plain SELECT. On modern MariaDB
with innodb_snapshot_isolation=ON, write/write
conflict detection can also reject an attempt to lock a record
inconsistent with the current read view and roll back the
transaction. Treat such conflicts as concurrency outcomes
requiring transaction-level retry/decision logic, not as reasons
to disable isolation blindly.
When the business rule can be expressed in one conditional
UPDATE, that is often clearer than SELECT-then-UPDATE.
Example:
UPDATE work_orders SET ... WHERE work_order_id=1003 AND
status='queued'; success is ROW_COUNT()=1. Fewer round trips
can reduce race windows.
7. SERIALIZABLE and why “strongest” is not automatically best
At SERIALIZABLE isolation, MariaDB documents plain SELECT statements as behaving like locking shared reads. That can make concurrent schedules behave more like serial execution, but it also increases blocking and the chance that applications must handle waits or conflicts. Use stronger isolation when the invariant needs it; do not enable it globally as a substitute for understanding transaction scope and indexes.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;START TRANSACTION;SELECT * FROM work_orders WHERE customer_id=1;ROLLBACK;Isolation choice also interacts with replication, Galera certification and application retry semantics. This single-node lesson deliberately separates core InnoDB behavior from cluster-level conflict handling; later chapters introduce those topologies explicitly.
8. Verification lab and knowledge check
- Record the baseline isolation and snapshot-isolation settings.
- Run the REPEATABLE READ two-session timeline and capture the values seen before/after Session B commits.
- Run the READ COMMITTED timeline and compare the second SELECT.
- Reset rows after each scenario.
- Use FOR UPDATE inside an explicit transaction and show a second session waiting or failing with NOWAIT.
-
Rewrite a SELECT-then-UPDATE workflow as one conditional
UPDATE and verify
ROW_COUNT(). - Document which isolation level your application test assumes and why.
Check your understanding
- What is the difference between a consistent read and a locking/current read?
- Why can REPEATABLE READ return an older committed value inside an open transaction?
- Why can READ COMMITTED show a different value on the second SELECT?
- Does FOR UPDATE provide a useful application lock when used as an isolated autocommit SELECT?
- Why should innodb_snapshot_isolation be recorded with the server version?
Review the answers
A consistent read follows an MVCC snapshot; a locking/current read acquires locks and reasons about current lockable state. REPEATABLE READ reuses the transaction snapshot for consistent reads, while READ COMMITTED can establish a fresher statement snapshot. FOR UPDATE needs an effective transaction lifetime to protect a later decision. innodb_snapshot_isolation is version-sensitive InnoDB behavior affecting conflict detection, so reproducible concurrency tests must record it rather than assuming old defaults.
The correct isolation level is the weakest level that still preserves your explicit invariants under tested concurrency—not the weakest one that passes single-user tests and not the strongest one available. Pair isolation with short transactions, appropriate indexes, atomic predicates and bounded retry handling.
9. Summary and bridge
InnoDB MVCC separates visibility from locking. Plain consistent reads can use historical committed versions; REPEATABLE READ keeps a stable snapshot across consistent reads, while READ COMMITTED can observe intervening commits. Locking reads acquire current-state locks and need real transaction scope. SERIALIZABLE strengthens behavior at a concurrency cost. Modern MariaDB’s snapshot-isolation conflict setting is another version-sensitive part of that contract.
The next lesson moves from visibility to the exact locks that make writes and DDL wait: metadata locks, record locks, gap locks and next-key locks. You will also see how an index can change not just query speed but the set of index records/ranges that InnoDB must lock.