Transactions

COMMIT is the moment a change becomes real

BEGINUPDATE A −100UPDATE B +100COMMITWhat other sessions see:A 500 · B 200A 500 · B 200A 500 · B 200A 400 · B 300crash hereon restart: rolled backA 500 · B 200crash hereon restart: keptA 400 · B 300COMMIT is the single moment a transaction’s changes become real, for everyone, and for good.Before COMMIT the changes are private and provisional: other sessions still see the old values, and a crash or ROLLBACK erases them.After COMMIT returns, every new reader sees them and no crash can undo them. Atomic, consistent, isolated, durable: ACID describesexactly this. Most tools run each statement as its own transaction (autocommit) unless you open one with BEGIN.

Before COMMIT, changes are private and can be rolled back. After it, everyone sees them and no crash can undo them.

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.

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.

Write-ahead log

Write a small log record first; the data can follow later

TransactionUPDATE A, UPDATE B1 appendWrite-ahead log: sequential, on disk……A −100B +100COMMITflushed (fsync) at COMMITthen COMMIT returnsData pageschanged in memory2 later, in bulkTable fileswritten at a checkpointcrashmemory lost; table files are behind3 recovery: replay the logfrom the last checkpoint: committed workis redone, uncommitted work discardedDurability comes from writing a small, sequential log record before touching the data itself.Appending to one log file and flushing it is far faster than writing scattered table pages, so COMMIT waits only for the log. The pagesfollow later. After a crash the log is replayed. The same log feeds replicas and point-in-time restore, and change data capture readsit to stream every change out of the database (lesson 11). PostgreSQL calls it the WAL, SQL Server the transaction log, MySQL the redo log.

The write-ahead log is flushed at COMMIT; table pages are written later. After a crash the log is replayed.

Lost updates

Two transactions that read, then write, can overwrite each other

Session 1: withdraw 30Session 2: deposit 50balance in the tableread balance → 100100read balance → 100100write 100 − 30 = 70; COMMIT70write 100 + 50 = 150; COMMIT150should be 120its read was already staleFixesUPDATE accounts SET balance = balance − 30read and write in one atomic stepSELECT … FOR UPDATElock the row at read; session 2 waitsTwo transactions that read, then write, can overwrite each other: the withdrawal of 30 is lost.Each session did nothing wrong on its own. The damage comes from the overlap: both read the same old balance. This is the lost update,and it happens at the default isolation level of most databases. Compute the new value in the UPDATE itself, lock the row when readingit, or use a version number and refuse the write if the row changed (optimistic locking).

The lost update: both sessions read 100, so the second write erases the first. Update in one step, or lock the row.

Locking patterns · Going deeper

Lock the row, check a version, or skip what is locked

Pessimistic: lock the row when you read itBEGIN;SELECT balance FROM acct WHERE id = 1 FOR UPDATE;-- any other writer of row 1 now waitsUPDATE acct SET balance = 70 WHERE id = 1;COMMIT;… FOR UPDATE NOWAIT instead of waiting:ERROR: could not obtain lock on row in relation "acct"SET lock_timeout = '200ms' and a blocked statement gives:ERROR: canceling statement due to lock timeoutOptimistic: no lock, check a version when you writeUPDATE acct SET balance = 70, version = version + 1 WHERE id = 1 AND version = 1; -- UPDATE 1-- a second session that also read version 1:UPDATE acct SET balance = 150, … WHERE id = 1 AND version = 1; -- UPDATE 0UPDATE 0 means someone got there first: reload, redo, retry.SELECT id FROM jobs WHERE state = 'queued'ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;worker 1 gets job 1, worker 2 gets job 2, neither waits: a queueLock the row when conflicts are likely, check a version when rare, skip locked rows when any row will do.A pessimistic lock is held until COMMIT, so never keep one open across a web request or while a person thinks. An optimisticcheck holds nothing: HTTP’s ETag with If-Match, DynamoDB’s ConditionExpression, Cosmos DB’s _etag and a Delta Lake commitare all this idea. FOR SHARE lets other readers lock the row too but blocks writers. SKIP LOCKED works in PostgreSQL, MySQL8 and Oracle; SQL Server’s form is the READPAST hint. Output from PostgreSQL 16, two sessions.

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

Isolation levelDirty readNon-repeatable readPhantom readOther anomaliesRead uncommittedRead committedRepeatable readSerializable✗✗✗✗✓✗✗✗✓✓✗ (✓ in PostgreSQL)✗✓✓✓✓✓ prevented ✗ can happenDefaults:read committed: PostgreSQL, SQL Server, Oracle, Snowflake · repeatable read: MySQL InnoDB · Delta Lake: WriteSerializableEach stronger isolation level rules out one more way that concurrent transactions can see each other’s work.Dirty read: seeing another transaction’s uncommitted change. Non-repeatable read: reading a row twice and getting two values. Phantom: runninga query twice and getting new rows. Serializable guarantees the result equals some one-at-a-time order, and may abort a transaction tokeep that promise, so the application must retry. Stronger levels cost more waiting or more retries.

Read committed stops dirty reads; repeatable read stops changing rows; serializable makes overlap look one-at-a-time.

Write skew · Going deeper · 1 of 2

Snapshot isolation still lets two transactions break a rule together

Rule: at least one doctor must stay on call. Asha and Ben are both on call, and both ask to go off at the same moment.Session 1 (Asha)Session 2 (Ben)SELECT count(*) FROM doctors WHERE on_call; → 2SELECT count(*) FROM doctors WHERE on_call; → 2UPDATE doctors SET on_call = falseUPDATE doctors SET on_call = false WHERE name = 'Asha'; → UPDATE 1 WHERE name = 'Ben'; → UPDATE 1COMMIT;COMMIT;REPEATABLE READ (snapshot isolation)both COMMITs succeedAsha false · Ben falsenobody is on call: the rule is brokenSERIALIZABLEsession 2: ERROR: could not serialize access due toread/write dependencies among transactionsAsha false · Ben true: retry, and Ben is refusedRun on PostgreSQL 16 with two connections. The error is SQLSTATE 40001, with HINT: The transaction might succeed if retried.Write skew: both read the same rows, each changes a different one, and together they break the rule.Snapshot isolation catches two writers of the same row (PostgreSQL: “could not serialize access due to concurrent update”),but here the writes never touch the same row, so nothing collides. Fixes: SERIALIZABLE with a retry on 40001; SELECT … FORUPDATE on the rows the decision reads; or a constraint the database checks. Oracle’s SERIALIZABLE and SQL Server’s SNAPSHOTare snapshot isolation, so both allow write skew.

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

EngineDefaultAlso offersWhat to knowPostgreSQLREAD COMMITTEDREPEATABLE READ, SERIALIZABLEREAD UNCOMMITTED is accepted but runs as READ COMMITTEDMySQL InnoDBREPEATABLE READall four standard levelsgap (next-key) locks stop most phantoms at the defaultSQL ServerREAD COMMITTEDall four, plus SNAPSHOTreaders wait for writers unless READ_COMMITTED_SNAPSHOT is onAzure SQL DatabaseREAD COMMITTEDthe same as SQL ServerREAD_COMMITTED_SNAPSHOT is on by default: no waitingOracleREAD COMMITTEDSERIALIZABLEno dirty reads at any level; its SERIALIZABLE is a snapshotSnowflakeREAD COMMITTEDnothing elseeach statement sees what was committed before it beganDelta LakeWriteSerializableSerializableoptimistic: a conflicting commit fails and is retriedDynamoDBper itemTransactWriteItemsserializable, up to 100 actions and 4 MB per transactionBEGIN ISOLATION LEVEL SERIALIZABLE; -- PostgreSQLSET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- SQL ServerThe same level name promises different things in different engines, so check what yours actually prevents.PostgreSQL’s REPEATABLE READ also stops phantoms; MySQL’s is the default, with locks added; SQL Server’s takes locks unlessyou choose SNAPSHOT. Any snapshot or serializable level may abort a transaction rather than let an anomaly through, so theapplication must catch the serialization failure and retry. In SQL Server, WITH (NOLOCK) is READ UNCOMMITTED for one table:it can return rows that are later rolled back.

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

One row, three versionsv1 · balance 100written by tx 41ended by tx 57v2 · balance 70written by tx 57ended by tx 63v3 · balance 120written by tx 63currenttx 41 commitstx 57 commitstx 63 commitsReport starts herereads v1 (100) for its whole runQuery starts herereads v2 (70)Old versions are removedonce no snapshot needs them:VACUUM in PostgreSQL,the version store in SQL ServerWriters make new versions instead of overwriting, so each reader sees a consistent snapshot and never waits for a writer.Multi-version concurrency control (MVCC) is how PostgreSQL, Oracle, MySQL InnoDB and SQL Server’s snapshot modes work. A long report seesthe data exactly as it was when it started, even while thousands of updates land. The cost is old versions to clean up, which is why atransaction left open for hours makes tables grow.

MVCC keeps old row versions, so each query reads a consistent snapshot while writers carry on.

Deadlocks

Two transactions each wait for the other, so one must die

Transaction 1holds A, wants BTransaction 2holds B, wants ARow Alocked by 1Row Blocked by 2dashed: waiting for a lockA cycle: neither can ever finishThe database checks for cycles (PostgreSQL afterdeadlock_timeout, 1 s by default), picks a victimand aborts it with a deadlock error.Prevent ittouch rows in the same order everywhere (A, then B)keep transactions short; retry the victimA deadlock is two transactions each waiting for a lock the other holds; the database breaks it by aborting one.A writer locks each row it changes until it commits, so a second writer on the same row queues behind it. Readers under MVCC take no rowlocks. Waiting is normal; a cycle of waiting is not. Deadlocks are not bugs in the database: the application must catch the error and retry.

A deadlock is a cycle of lock waits. The database aborts one transaction; take locks in a fixed order and retry the victim.

Lesson complete

You finished “Transactions”

Nicely done.

Open in a new tab