MySQL / Reliable applications and analytical queries / Lesson 7

InnoDB transactions and isolation

Understand what commits together, what a snapshot sees, and why a correct single-session script can fail under concurrency.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Define atomicity, durability, isolation, and transaction boundaries.
Compare REPEATABLE READ and READ COMMITTED using a two-session timeline.

A transaction is a business boundary

Use the original InnoDB shop fixture. A purchase may require changing stock and adding an order with lines. Atomicity means these writes commit together or are rolled back together. Isolation coordinates concurrent transactions; durability concerns the persistence of committed results under the configured storage and logging guarantees. A transaction cannot roll back an email or an external payment, so those effects need their own reliable coordination design.

Undo a deliberate portion

Autocommit normally gives each standalone statement its own transaction. START TRANSACTION creates an explicit boundary lasting until COMMIT or ROLLBACK. A savepoint can undo later DML while retaining earlier work, but it is not a nested transaction. Always know who owns transaction completion: mixing framework-owned transactions with routines that commit internally can break an application's atomicity assumptions.

sql Partial undo, followed by complete undo

START TRANSACTION;
UPDATE products SET stock=stock-1 WHERE id=1 AND stock>=1;
SAVEPOINT after_notebook;
UPDATE products SET stock=stock-1 WHERE id=3 AND stock>=1;
ROLLBACK TO SAVEPOINT after_notebook;
SELECT id,stock FROM products WHERE id IN (1,3) ORDER BY id;
ROLLBACK;

Expected output

id | stock
1 | 19
3 | 5

The query sees notebook stock 19 and bag stock 5: the second decrease was undone. The final ROLLBACK restores notebook stock 20 too. Releasing or rolling back to a savepoint does not commit the earlier notebook change.

Read a snapshot, not a frozen world

InnoDB defaults to REPEATABLE READ. Ordinary consistent reads in one transaction normally share the snapshot established by its first consistent read. READ COMMITTED establishes a new snapshot for each consistent read. Neither description says that concurrent writers stop. Your own writes remain visible to you, and locking reads use current locking semantics rather than simply reusing a historical snapshot.

sql A two-session snapshot timeline

-- Session A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT stock FROM products WHERE id=1; -- 20
-- Session B, after the first read in A
START TRANSACTION;
UPDATE products SET stock=19 WHERE id=1;
COMMIT;
-- Session A, after B commits
SELECT stock FROM products WHERE id=1; -- still 20
ROLLBACK;
-- Session B: restore this disposable fixture
UPDATE products SET stock=20 WHERE id=1;

Expected output

REPEATABLE READ: A reads 20, then 20.
READ COMMITTED variation: A reads 20, then 19.

Run the labeled statements only in a disposable two-session lab. A's second ordinary read remains 20 under REPEATABLE READ even after B commits 19. Rehearsing with READ COMMITTED instead makes the second read 19. The final restore is a separate committed write, not part of A's rollback.

Snapshots do not enforce every invariant

Two sessions can both read available capacity and independently decide to reserve it. Use a guarded UPDATE, an appropriate locking read, a unique constraint, or another explicit serialization mechanism. Choosing SERIALIZABLE may increase blocking and deadlocks; it is not a substitute for understanding the invariant. Keep transactions short and avoid waiting for a user or remote service while holding database locks.

Treat failures as state transitions

A deadlock normally rolls back the victim transaction; a lock wait timeout normally rolls back the waiting statement under the default configuration. The application should explicitly roll back an aborted business attempt, then retry only a complete idempotent unit when appropriate. A lost connection during COMMIT creates an uncertain outcome: reconnecting and repeating blindly can duplicate work. A durable request key makes that ambiguity reconcilable.

Common errors and fixes

ROLLBACK does not remove an earlier standalone update.

Why it happens: Autocommit already committed that statement before the explicit transaction began.
How to fix it: Use an audited compensating write if appropriate; rehearse inside START TRANSACTION.
Prevent it next time: Make boundaries visible and inspect connection settings.

ERROR 1568 (25001) changing isolation inside a transaction.

Why it happens: SET TRANSACTION attempted to change characteristics after the transaction started.
How to fix it: Finish the current transaction and set the next transaction's isolation first.
Prevent it next time: Configure transaction characteristics before the first business statement.

Stock is oversold despite repeatable reads.

Why it happens: Independent snapshot decisions were mistaken for exclusive reservations.
How to fix it: Perform a guarded atomic update and verify affected rows.
Prevent it next time: State the invariant and its concurrency enforcement mechanism explicitly.

Check your understanding

1. Can A see B's newly committed row during its existing REPEATABLE READ snapshot?

Show worked solution

An ordinary consistent read using the existing snapshot does not see B's later commit. Finishing the transaction and starting a new one obtains a fresh view. Do not generalize that answer to locking reads or to A's own writes, which follow different visibility rules.

2. Design the database boundary for stock reservation and order insertion.