Transactions
COMMIT is the moment a change becomes real
Before COMMIT, changes are private and can be rolled back. After it, everyone sees them and no crash can undo them.
A transaction groups changes into one unit. Follow a transfer. It begins, takes a hundred from A, adds a hundred to B, and commits. Throughout, other sessions see the old balances; the new ones appear for everyone at the instant of commit. If the server crashes halfway, the half-done transfer is rolled back on restart, as if it never started. If it crashes a moment after commit, the transfer survives. That single moment, commit, is what all of ACID hangs on.
Transactions · Going deeper
Savepoints, autocommit and transactions across systems
A savepoint undoes part of a transaction. Two-phase commit makes several systems commit together, at a cost.
A savepoint undoes part of a transaction. Two-phase commit makes several systems commit together, at a cost. Inside one database. Autocommit the default in most clients: each statement commits on its own. Open a transaction with BEGIN (or START TRANSACTION) to group statements. SAVEPOINT a marker inside a transaction. ROLLBACK TO SAVEPOINT s1 undoes the work after it and keeps the work before. Keep them short an open transaction holds locks and old row versions. A transaction left open over lunch blocks writers and bloats the table. Across systems. Two-phase commit (2PC) a coordinator asks every participant to prepare, then tells all to commit. Correct, but a coordinator failure can leave participants waiting, so it is rare in cloud systems. Sagas a chain of local transactions, each with a compensating action (refund the payment if the seat cannot be booked). The usual choice between microservices. Outbox pattern write the business row and an “event to send” row in one local transaction; a separate process publishes the event. No message is lost or sent for a rolled-back change. Data platforms have transactions too: a Delta Lake or Iceberg commit (lesson 9) is atomic, so a failed Spark job leaves the table as it was.
Inside one database
- Autocommit the default in most clients: each statement commits on its own. Open a transaction with BEGIN (or START TRANSACTION) to group statements.
- SAVEPOINT a marker inside a transaction. ROLLBACK TO SAVEPOINT s1 undoes the work after it and keeps the work before.
- Keep them short an open transaction holds locks and old row versions. A transaction left open over lunch blocks writers and bloats the table.
Across systems
- Two-phase commit (2PC) a coordinator asks every participant to prepare, then tells all to commit. Correct, but a coordinator failure can leave participants waiting, so it is rare in cloud systems.
- Sagas a chain of local transactions, each with a compensating action (refund the payment if the seat cannot be booked). The usual choice between microservices.
- Outbox pattern write the business row and an “event to send” row in one local transaction; a separate process publishes the event. No message is lost or sent for a rolled-back change.
Write-ahead log
Write a small log record first; the data can follow later
The write-ahead log is flushed at COMMIT; table pages are written later. After a crash the log is replayed.
How can commit be both fast and permanent? The database first appends a short record of each change to a log, a file written strictly in order, and at commit it forces that log to disk. Only then does commit return. The actual table pages are changed in memory and written out later, in bulk, at a checkpoint. If the server crashes, memory is lost and the table files are behind. On restart the database replays the log from the last checkpoint: committed work is redone, uncommitted work is thrown away.
Lost updates
Two transactions that read, then write, can overwrite each other
The lost update: both sessions read 100, so the second write erases the first. Update in one step, or lock the row.
Now let two transactions overlap. The balance is 100. Session one reads it, to make a withdrawal of 30. Session two reads it too, to make a deposit of 50. Session one writes 70 and commits. Session two writes 100 plus 50, 150, and commits. The withdrawal has vanished; the balance should be 120. The fix is to never do the arithmetic outside the database: let the UPDATE compute balance minus 30 itself, or lock the row when you read it so the other session waits.
Locking patterns · Going deeper
Lock the row, check a version, or skip what is locked
SELECT … FOR UPDATE locks rows until COMMIT; optimistic locking checks a version on write; SKIP LOCKED makes a queue.
Isolation levels
Each stronger level rules out one more anomaly
Read committed stops dirty reads; repeatable read stops changing rows; serializable makes overlap look one-at-a-time.
The SQL standard defines four isolation levels by what they prevent. Read uncommitted prevents nothing; you can see changes that are later rolled back. Read committed hides uncommitted work, and is the default almost everywhere. Repeatable read also guarantees that a row you read twice reads the same. Serializable goes all the way: the outcome must equal running the transactions one at a time. Most databases default to read committed. MySQL defaults to repeatable read, and Delta Lake tables to write-serializable.
Write skew · Going deeper · 1 of 2
Snapshot isolation still lets two transactions break a rule together
Write skew: both read the same rows, each updates a different one. Only SERIALIZABLE stops it, by aborting one with error 40001.
Isolation by engine · Going deeper · 2 of 2
The same isolation level means different things in each engine
Most engines default to READ COMMITTED; MySQL defaults to REPEATABLE READ; Snowflake offers only READ COMMITTED.
MVCC
Readers see a snapshot, so they never wait for writers
MVCC keeps old row versions, so each query reads a consistent snapshot while writers carry on.
How can a long report read a table that is being updated, without blocking anyone? An update does not overwrite the row. It writes a new version and marks the old one as ended, each stamped with the transaction that did it. A report that started before transaction fifty-seven committed keeps reading version one, a hundred, for its entire run. A query that starts later sees version two. Readers and writers never wait for each other. The cost is the old versions, which must be cleaned up once no one needs them.
Deadlocks
Two transactions each wait for the other, so one must die
A deadlock is a cycle of lock waits. The database aborts one transaction; take locks in a fixed order and retry the victim.
Writers lock the rows they change until they commit, and another writer on the same row must wait. Waiting is normal. Transaction one has locked row A. Transaction two has locked row B. Now one asks for B, and two asks for A. Each is waiting for the other, forever. The database detects the cycle, picks a victim and aborts it with a deadlock error. The prevention is discipline: every piece of code takes its locks in the same order, transactions stay short, and the application retries a transaction that was chosen as the victim.
Lesson complete
You finished “Transactions”
Nicely done.