The idea: what one rollback does to the others

A transaction can end in two ways: COMMIT makes its writes permanent, ROLLBACK (an abort) undoes them as if it never ran. While transactions run at the same time, one may read data that another has written but not yet committed. If the writer then rolls back, the reader has used a value that never existed. This page asks three questions about a schedule with commits and rollbacks in it: can the damage still be repaired (recoverable), does it stay inside one transaction (cascadeless), and is plain undo always right (strict)?

Four nested boxes. The outer box is all schedules, holding an example where T2 commits before T1, from which it read. Inside it the recoverable schedules, with a dirty read and T1 committing first. Inside that the cascadeless ones, with a blind write over uncommitted data. Innermost the strict schedules, where every read and write waits for the last writer's commit.
Every strict schedule is cascadeless, and every cascadeless schedule is recoverable, but not the other way round.

The canvas shows the schedule as a grid (one row per transaction, time goes right) with each transaction's state, the items A and B with the undo log, the list of who read whose value, and the three classes, checked after every step.

Reads-from and dirty reads

The notation: r1(A) means T1 reads A, w2(B) means T2 writes B, c1 means T1 commits and a1 means T1 rolls back. The items start at A = 100 and B = 0. Each transaction remembers the last value it read, v. T1 writes v + 10, T2 writes v × 2, and T3 writes 50 without reading (a blind write). So when T2 reads A = 110 and then writes B, B becomes 220: data flows from one item to another.

T2 reads from T1 when T2 reads a value that T1 wrote. If T1 has not committed at that moment, it is a dirty read. Every write also leaves an undo record with the item's before image (its value just before the write). ROLLBACK copies the before images back, newest first.

Recoverable: commit after the ones you read from

A schedule is recoverable when every transaction commits only after every transaction it read from has committed. Dirty reads are allowed; committing too early is not.

In Demo: dirty read, T1 commits first, T2 reads A = 110 from T1 before T1 commits and writes B = 220. T1 commits, then T2 commits. Had T1 rolled back instead, T2 would still have been active and could have been rolled back too. In Demo: T2 commits, then T1 aborts, T2 commits first. Then T1 rolls back and A goes back to 100, but T2's B = 220 is committed, and durability forbids undoing a committed transaction. B now holds a value computed from an A = 110 that never existed. The schedule is not recoverable: no rollback can repair it.

Cascadeless: read only committed data

A recoverable schedule can still be costly. If T1 rolls back, every active transaction that read from T1 must roll back, then every one that read from those, and so on. This is a cascading rollback. In Demo: dirty reads, T1 aborts, T2 read A from T1 and T3 read B from T2. One ROLLBACK of T1 ends all three; the page then undoes B and A from the undo log, newest first.

A schedule is cascadeless (it avoids cascading aborts) when every read sees committed data only. Then a rollback never reaches another transaction, and the commit order cannot go wrong either, so every cascadeless schedule is recoverable.

One start: T1 writes A = 110 and T2 reads it, a dirty read, then writes B = 220. Three endings side by side. First: T1 commits, then T2 commits; recoverable. Second: T1 rolls back while T2 is active, so T2 rolls back too; a cascade, A = 100 and B = 0. Third: T2 commits, then T1 rolls back; B = 220 stays, not recoverable.
The same dirty read can end well, end in a cascade, or end in damage no rollback can repair.

Strict: no reads or writes of uncommitted data

A schedule is strict when no transaction reads or writes an item while another transaction's write of that item is uncommitted. Strict adds one rule to cascadeless: no overwriting of uncommitted data. That rule is what makes undo by before image always right.

Demo: blind write over uncommitted data shows why. T1 writes A = 110 (before image 100). T3 writes A = 50 over it without reading, and commits. No one read dirty data, so the schedule is cascadeless. Then T1 rolls back: undo copies T1's before image A = 100 back, and T3's committed 50 is gone. The undo was right for T1 but wrong for the schedule.

A time line for item A: 100 at the start, 110 after T1's write with the before image 100 in the undo log, 50 after T3's blind write and commit, and 100 again after T1's rollback copies its before image back, so T3's committed 50 is lost.
When a write lands on uncommitted data, the first writer's rollback wipes out the second writer's committed value.
classrulewhat it prevents
recoverablecommit after every transaction you read from has committeda committed result built on rolled-back data
cascadelessread only committed dataone rollback forcing others to roll back
strictread or write an item only after its last writer has committed or rolled backundo wiping out another transaction's write

Strict 2PL gives strict schedules

Locks are the usual way to get a strict schedule. Under strict two-phase locking a transaction takes an exclusive (X) lock before it writes and keeps every X lock until it commits or rolls back. Readers take shared locks. So no other transaction can read or write the item between the write and the end of the writer: exactly the strict rule. Basic 2PL, which may release locks before the end, does not give this. See two-phase locking and deadlock detection for the lock modes and the waits.

These classes are about aborts, not about order. A strict schedule can still fail to be serializable, and a serializable schedule can still be unrecoverable. The test for the order is on the conflict serializability page.

What MariaDB InnoDB does

InnoDB locks the rows a transaction changes with X row locks and keeps them until COMMIT or ROLLBACK, at every isolation level. So it never lets one transaction write a row that another has changed but not committed: the second writer waits (for up to innodb_lock_wait_timeout, 50 seconds by default, then ERROR 1205). Because writes never land on uncommitted data, ROLLBACK can simply apply the undo log. If the waits form a cycle, InnoDB rolls one transaction back with ERROR 1213 (40001) (see deadlock detection).

Reads depend on the isolation level, set with SET TRANSACTION ISOLATION LEVEL …:

levelplain SELECTdirty read?
READ UNCOMMITTEDno lock, may see the newest, uncommitted rowyes
READ COMMITTEDno lock, a fresh snapshot per statement, built from the undo logno
REPEATABLE READ (the default)no lock, one snapshot for the whole transactionno
SERIALIZABLEa shared lock, like LOCK IN SHARE MODE (when autocommit is off)no

Demo: InnoDB READ UNCOMMITTED runs r1(A) w1(A) r2(A) w2(B) w3(A) a1 c2 c3. T2's read sees T1's uncommitted 110. T3's write of A waits for T1's lock; when T1 rolls back, A = 100 again, then T3 gets the lock and writes 50. InnoDB does not track reads, so it does not roll T2 back: T2 commits B = 220, built from the 110 that never existed. At READ UNCOMMITTED, avoiding that is the application's job. Demo: InnoDB READ COMMITTED, same operations runs the same list: T2 now reads the last committed A = 100 from the undo log, writes B = 200, and every class holds. How the snapshots work is on MVCC and isolation levels and InnoDB isolation: snapshot reads vs current reads.

Strictly, a snapshot read is outside the one-copy model of these classes: T2 reads an older version of A while T1's newer one exists. The page counts it as a read of committed data, which is what the textbook rules protect.

See also Validation-Based Optimistic Concurrency Control, which avoids dirty reads by keeping writes in a private copy until a validation step lets them in.

What the page leaves out

Crash recovery: after a crash the same undo records roll back unfinished transactions, and the redo log brings back committed ones (see write-ahead logging and recovery). Shared locks: InnoDB's plain reads below SERIALIZABLE take none, and the page does not model them. Deadlock victims: the page rolls back the transaction that closes the cycle, while InnoDB tries to pick the smaller transaction (fewer rows changed). Savepoints (ROLLBACK TO SAVEPOINT), lock wait timeouts, gap and next-key locks, undo purge, and optimistic schemes that validate at commit instead of locking. MariaDB has no setting that rolls back the readers of a rolled-back transaction: no InnoDB level produces a cascade.

On the mind map

Open the Databases & Storage mind mapDatabases& StorageSchema and storage: how rows land on diskSchema and storageIndexes: find rows without a full scanIndexesRunning a query: parse, plan, executeRunning a queryCost-based optimizer: estimate rows, pick cheapest planCost-based optimizerQuery rewrites: same answer, simpler queryQuery rewritesTransactions: ACID: isolation and recoveryTransactionsApps and connections: the client side of the databaseApps and connectionsMongoDB: documents, replica sets, shardsMongoDBMVCC and isolationMVCC and isolationreaders see a snapshot of row versionsTwo-phase lockingTwo-phase lockinggrow locks, then release; detect cyclesConflict serializabilityConflict serializabilityCycle in precedence graph = not serializableRecoverable schedulesCommit only after those you read fromWait-Die and Wound-WaitWait-Die and Wound-WaitAge decides who waits and who rolls backMultiple Granularity LockingMultiple Granularity LockingIntention locks on every ancestorOptimistic vs pessimisticOptimistic vs pessimisticversion check vs SELECT FOR UPDATEValidation-based OCCValidation-based OCCCheck read/write sets at commit, else abortWAL and ARIESWAL and ARIESlog first; redo all, then undo losersTimestamp orderingTimestamp orderingolder timestamp wins; late writes abortOpen the Databases & Storage mind mapDatabases & StorageACID: isolation and recoveryTransactionsMVCC and isolationMVCC and isolationreaders see a snapshot of row versionsTwo-phase lockingTwo-phase lockinggrow locks, then release; detect cyclesConflict serializabilityConflict serializabilityCycle in precedence graph = not serializableRecoverable schedulesCommit only after those you read fromWait-Die and Wound-WaitWait-Die and Wound-WaitAge decides who waits and who rolls backMultiple Granularity LockingMultiple Granularity LockingIntention locks on every ancestorOptimistic vs pessimisticOptimistic vs pessimisticversion check vs SELECT FOR UPDATEValidation-based OCCValidation-based OCCCheck read/write sets at commit, else abortWAL and ARIESWAL and ARIESlog first; redo all, then undo losersTimestamp orderingTimestamp orderingolder timestamp wins; late writes abort
This page on the Databases & Storage mind map · 55 pages in 8 branches · open it to see the whole map and test yourself in recall mode.