Transactions, ACID & Concurrency

The bank transfer that stopped halfway 🏦

Amal sends 30 to Bilal. The bank's program runs two statements: take 30 from Amal, then add 30 to Bilal. The server loses power right between them. Amal is 30 poorer, Bilal is not richer, and the money has simply vanished.

The cure is a transaction: a group of statements the database treats as one unit. Either every statement takes effect, or none of them does. There is no "halfway".

What you'll learn

• BEGIN, COMMIT and ROLLBACK, and what autocommit does when you write none of them
• The four ACID guarantees: atomicity, consistency, isolation, durability
• Isolation levels and the anomalies they prevent: dirty reads, non-repeatable reads, phantoms and lost updates
• Locking, deadlocks, and optimistic concurrency control with a version column

In the labs you control real SQLite transactions from Python, prevent a lost update and detect a deadlock.

One unit of work

BEGIN;                                                         -- start the unit of work
UPDATE accounts SET balance = balance - 30 WHERE id = 'amal';
UPDATE accounts SET balance = balance + 30 WHERE id = 'bilal';
COMMIT;                                                        -- make both changes permanent

-- if anything went wrong before COMMIT:
ROLLBACK;                                                      -- undo everything since BEGIN

BEGIN TRANSACTION and START TRANSACTION are common spellings of the same idea. Until COMMIT, other connections do not see the changes (except at the READ UNCOMMITTED level described below), and ROLLBACK erases them as if they never happened.

Autocommit: when you do not write BEGIN, PostgreSQL, MySQL and SQLite run each statement as its own tiny transaction and commit it immediately. That is fine for one statement, and exactly the bug in the story for two. One trap in Python: by default the sqlite3 module opens a transaction for you before INSERT, UPDATE and DELETE, and waits for commit(). The labs pass isolation_level=None so that nothing happens implicitly and you write BEGIN yourself.

Inside a transaction, a savepoint lets you undo only part of the work: SAVEPOINT item; ... ROLLBACK TO item; keeps everything before the savepoint and still lets you COMMIT the rest.

ACID: four promises

Atomicity: all or nothing. If a transaction fails or the server crashes before COMMIT, none of its changes remain. Databases make this possible by writing changes to a log first: SQLite uses a rollback journal or a write-ahead log (WAL), PostgreSQL uses its WAL. After a crash, recovery reads the log and discards whatever was not committed.

Consistency: from one valid state to another. Every constraint you declared (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) holds after the transaction, otherwise the offending statement fails and you roll back. Note the limit: the database only enforces the rules it knows. "The total amount of money never changes" is a rule your code keeps by putting both updates in one transaction.

Isolation: concurrent transactions do not trip over each other. Ideally the result is the same as if the transactions had run one after another (serializable). Because that can cost speed, databases offer weaker isolation levels, covered next.

Durability: committed means saved. Once COMMIT returns, the change survives a crash or power cut, because the log was flushed to disk before the database reported success. Some settings trade this safety for speed (PostgreSQL's synchronous_commit = off, SQLite's PRAGMA synchronous), and no setting protects you from losing the disk itself: that is the job of backups and replicas.

BEGIN ──▶ change 1 ──▶ change 2 ──▶ COMMIT ──▶ durable, visible to everyone
              │             │
              └─ error or crash before COMMIT ──▶ ROLLBACK: as if nothing happened

Isolation levels and the anomalies they prevent

When transactions run at the same time, these are the classic things that can go wrong:

• Dirty read: you read a change another transaction has not committed yet. If it rolls back, you acted on data that never existed.
• Non-repeatable read: you read the same row twice in one transaction and get different values, because another transaction committed a change in between.
• Phantom read: you run the same WHERE query twice and get a different set of rows, because another transaction inserted or deleted matching rows.
• Lost update: two transactions read a value, both compute a new one from it, and the second write silently overwrites the first.

time  T1 (Amal's phone)               T2 (Bilal's laptop)
 1    SELECT stock -> 10
 2                                    SELECT stock -> 10
 3    UPDATE stock = 10 - 3  (7)
 4    COMMIT
 5                                    UPDATE stock = 10 - 2  (8)
 6                                    COMMIT        -- Amal's sale is lost: it should be 5

The SQL standard (ISO/IEC 9075) defines four isolation levels by the anomalies they still allow:

• READ UNCOMMITTED: dirty reads are possible.
• READ COMMITTED: no dirty reads; non-repeatable reads and phantoms are possible.
• REPEATABLE READ: no dirty or non-repeatable reads; phantoms are possible.
• SERIALIZABLE: the result equals running the transactions one at a time, in some order.

Real databases differ in the details, so read your database's manual. PostgreSQL defaults to READ COMMITTED, treats READ UNCOMMITTED as READ COMMITTED, and its REPEATABLE READ (a snapshot of the data) also prevents phantoms. Its SERIALIZABLE level may abort a transaction with a serialization failure (SQLSTATE 40001), and your code must then retry the whole transaction. MySQL's InnoDB defaults to REPEATABLE READ. SQLite transactions are serializable, because it allows only one writer at a time.

BEGIN ISOLATION LEVEL SERIALIZABLE;   -- PostgreSQL; MySQL uses SET TRANSACTION ISOLATION LEVEL ...

Four ways to stop a lost update

1. Let the database do the arithmetic: UPDATE products SET stock = stock - 2 WHERE id = 1. The read and the write happen inside one statement, so no other write can slip in between.
2. Pessimistic locking: SELECT stock FROM products WHERE id = 1 FOR UPDATE (PostgreSQL, MySQL) locks the row until your transaction ends, so the second client waits.
3. Optimistic concurrency control: do not lock, but detect the conflict when saving, with a version column (the next lab).
4. A stricter isolation level: in PostgreSQL's REPEATABLE READ or SERIALIZABLE, the second writer gets an error instead of overwriting.

Locks and deadlocks

To keep transactions apart, databases use locks. A shared lock lets many readers in; an exclusive lock gives one writer sole access. PostgreSQL and MySQL's InnoDB lock individual rows, and thanks to multiversion concurrency control (MVCC) a plain SELECT at the default isolation level neither blocks writers nor waits for them. Two transactions that write the same row still have to take turns: the second waits until the first commits or rolls back. SQLite is coarser: one writer for the whole database at a time (BEGIN IMMEDIATE takes the write lock at the start instead of at the first write).

A deadlock happens when transactions wait for each other in a circle:

T1: UPDATE accounts ... WHERE id = 'amal'    -- T1 locks amal
T2: UPDATE accounts ... WHERE id = 'bilal'   -- T2 locks bilal
T1: UPDATE accounts ... WHERE id = 'bilal'   -- T1 waits for T2
T2: UPDATE accounts ... WHERE id = 'amal'    -- T2 waits for T1: nobody can ever move

wait-for graph:    T1 ──waits for──▶ T2
                   ▲                  │
                   └────waits for─────┘

Waiting longer cannot fix this, so the database steps in. It keeps a wait-for graph (an arrow from each waiting transaction to the one holding its lock), and a cycle in that graph is a deadlock. It then aborts one transaction of the cycle, the victim, which releases its locks so the others continue. PostgreSQL checks after a transaction has waited deadlock_timeout (1 second by default) and reports deadlock detected (SQLSTATE 40P01); InnoDB detects deadlocks automatically and rolls back the transaction it judges smallest. The victim's application gets an error and should retry the whole transaction.

Better still, make deadlocks rare:

• Lock in a consistent order. If every transfer updates the account with the smaller id first, the circle above cannot form.
• Keep transactions short. Never wait for user input or a slow network call while holding locks.
• Retry on deadlock and serialization errors (40P01, 40001), with a small limit on attempts.
• Optionally set a lock timeout (PostgreSQL's lock_timeout) so a stuck request fails fast instead of hanging.

Optimistic or pessimistic? Optimistic control (the version column) holds no locks, so it suits conflicts that are rare, such as two people editing the same profile. Pessimistic locking (FOR UPDATE) suits hot rows that conflict often, where retrying again and again would waste more work than waiting.

Recap and what to practice next

• A transaction (BEGIN ... COMMIT) is all or nothing; ROLLBACK undoes it, and savepoints undo part of it. Without BEGIN, autocommit makes every statement its own transaction.
• ACID: atomicity (all or nothing), consistency (declared constraints always hold), isolation (concurrent transactions do not interfere), durability (committed data survives a crash).
• The anomalies are dirty reads, non-repeatable reads, phantoms and lost updates. Stronger isolation levels prevent more of them; check what your database actually does, and retry on serialization failures.
• Stop lost updates with an atomic UPDATE, SELECT ... FOR UPDATE, or optimistic concurrency control with a version column and a rowcount check.
• A deadlock is a cycle in the wait-for graph. The database aborts a victim; you prevent most of them by locking in a consistent order and keeping transactions short.

Practice next: extend the first lab with a SAVEPOINT so a batch of transfers keeps the good ones and skips the bad ones in a single transaction. In the second lab, add an automatic retry loop with at most three attempts. Then move on to how data is modelled outside the relational world: NoSQL databases make different promises about transactions, and knowing ACID tells you exactly what to ask.