Transactions and ACID
A transaction groups several SQL statements into one unit of work that the database runs all or nothing. Follow a bank transfer of 100 from Ann to Ben inside PostgreSQL: row versions and locks while it runs, a reader that never sees the half-done transfer, the write-ahead log flushed to disk before COMMIT returns, a crash that is repaired by replaying that log, and a second transfer that breaks a CHECK rule and is rolled back. Together they are the four ACID promises.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01Inside an open transaction, Ben has been credited 100 but Ann has not been debited yet. In PostgreSQL, what does another session's SELECT see?
PostgreSQL never shows a transaction's uncommitted changes to other sessions (no dirty reads at any isolation level). Readers use the older row version and do not wait for the writer's row lock.
02What does COMMIT wait for before PostgreSQL (with the default synchronous_commit = on) answers COMMIT?
Write-ahead logging: the log is flushed first, and the data pages can be written later. After a crash the log is replayed, so a committed transaction is never lost. That is Durability.
03The second transfer adds 500 to Ben, then tries to take 500 from Ann, who has 400. The table has CHECK (bal >= 0). What is in the table after ROLLBACK?
The CHECK rule refuses Ann's debit, PostgreSQL aborts the transaction, and ROLLBACK undoes Ben's credit too. All or nothing: the balances stay 400 and 400.
04After a statement fails inside a PostgreSQL transaction, the program sends another UPDATE. What happens?
PostgreSQL answers 'current transaction is aborted, commands ignored until end of transaction block'. SQLite behaves differently: it undoes only the failed statement and keeps the transaction open.
05Which ACID property is the DBMS NOT able to guarantee on its own?
The DBMS enforces the rules it knows (types, keys, CHECK, foreign keys). A rule only your program knows, such as writing both halves of a transfer, is the program's job; the transaction makes sure the halves land together.
Glossary
- Transaction
- A group of SQL statements that the database treats as one unit of work, from BEGIN to COMMIT or ROLLBACK: all of it takes effect, or none of it.
- COMMIT
- Ends a transaction and makes all its changes permanent and visible to other sessions at once.
- ROLLBACK
- Ends a transaction and undoes every change it made.
- ACID
- Atomicity, Consistency, Isolation, Durability: the four promises of a transaction (Haerder and Reuter, 1983).
- Row version (MVCC)
- PostgreSQL's multiversion concurrency control: an UPDATE writes a new version of the row, so readers can keep using the old, committed one.
- Row lock
- A lock an UPDATE takes on the rows it changes, so that other writers of those rows wait until the transaction ends. Readers are not blocked.
- Write-ahead log (WAL)
- A log on disk where every change is recorded before the data files are changed; COMMIT waits for it to be flushed, and crash recovery replays it.
- CHECK constraint
- A rule on a column or table, such as CHECK (bal >= 0), that every row must satisfy; a statement that would break it fails.
Notes and sources
- Real WAL records describe changes to 8 kB table and index pages, not "ben 300→400"; the figure writes them as rows so you can read them. The card shows only T1’s records: the failed transfer also writes WAL (its change to Ben and an abort record), left out to keep the card short.
- COMMIT waits for the WAL flush because synchronous_commit is on by default. Turning it off makes commits faster but can lose the last few committed transactions in a crash (never corrupting the data).
- PostgreSQL’s default level, Read Committed, gives each statement a fresh snapshot, so two reads in one transaction can see different committed data. Repeatable Read and Serializable give stronger guarantees.
- The DBMS checks the rules you declare (types, keys, CHECK, foreign keys). Rules it does not know about, such as "a transfer must not change the total", stay the program’s job: Consistency is shared.
- SQLite differs after an error: a failed statement is undone, but the transaction stays open and earlier changes remain until the program runs ROLLBACK. Python’s with db: block does that for you.
- In PostgreSQL, sending COMMIT to an aborted transaction also rolls it back, and the server answers ROLLBACK.
- The Python example uses the sqlite3 module that ships with Python. The first transfer commits, the second rolls back, and it prints the CHECK error and [('ann', 400), ('ben', 400)].
- PostgreSQL documentation: Tutorial, Transactions
- PostgreSQL documentation: Transaction Isolation
- PostgreSQL documentation: Concurrency Control, Introduction (MVCC)
- PostgreSQL documentation: Explicit Locking
- PostgreSQL documentation: Constraints
- PostgreSQL documentation: Write-Ahead Logging (WAL)
- PostgreSQL documentation: WAL Configuration
- PostgreSQL documentation: Asynchronous Commit
- PostgreSQL documentation: BEGIN
- PostgreSQL documentation: COMMIT
- PostgreSQL documentation: ROLLBACK
- SQLite: ON CONFLICT clause
- SQLite: Transaction
- Python Library Reference: sqlite3
- Haerder and Reuter, Principles of Transaction-Oriented Database Recovery, ACM Computing Surveys 15(4), 1983
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapters 17 and 19
Quiz Unlocks after the last step.