Transactions and ACID

Why transactions: the bank transfer

Sanne transfers €200 to Kwame. In the database that is two changes: subtract 200 from Sanne's balance, add 200 to Kwame's. If the server crashes, the network drops or the second statement fails between those two changes, €200 has vanished. The two updates must happen together or not at all.

A database transaction is a sequence of statements that the database treats as one indivisible unit of work: it either completes entirely or leaves no trace, and other users never see it half done. Transactions are what let a database be trusted with money, stock levels and bookings.

BEGIN, COMMIT and ROLLBACK

Three statements control a transaction. BEGIN starts it. The COMMIT statement ends it and makes all its changes permanent and visible to others. ROLLBACK ends it by undoing every change made since BEGIN. Here the transfer succeeds:

BEGIN;
CREATE TABLE accounts (
  account_id  INTEGER PRIMARY KEY,
  owner       TEXT    NOT NULL,
  balance     INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sanne', 500), (2, 'Kwame', 100);
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
COMMIT;

SELECT * FROM accounts ORDER BY account_id;

account_id  owner  balance
----------  -----  -------
1           Sanne  300
2           Kwame  300

The first transaction sets up the table; the second is the transfer. Now the same transfer, but the program decides to cancel it after both updates — for example because a fraud check failed. Inside the transaction the new balances are visible; after ROLLBACK, they are gone:

BEGIN;
CREATE TABLE accounts (
  account_id  INTEGER PRIMARY KEY,
  owner       TEXT    NOT NULL,
  balance     INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sanne', 500), (2, 'Kwame', 100);
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
SELECT * FROM accounts ORDER BY account_id;
ROLLBACK;

SELECT * FROM accounts ORDER BY account_id;

account_id  owner  balance
----------  -----  -------
1           Sanne  300
2           Kwame  300

account_id  owner  balance
----------  -----  -------
1           Sanne  500
2           Kwame  100

The syntax varies slightly: PostgreSQL and MySQL also accept START TRANSACTION, and SQL Server writes BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION.

When a statement fails

What if the database itself refuses one of the statements? Below, Sanne tries to send €700 while she has only €500. The program adds the money to Kwame first; the second UPDATE then breaks the CHECK constraint:

BEGIN;
CREATE TABLE accounts (
  account_id  INTEGER PRIMARY KEY,
  owner       TEXT    NOT NULL,
  balance     INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sanne', 500), (2, 'Kwame', 100);
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance + 700 WHERE account_id = 2;
UPDATE accounts SET balance = balance - 700 WHERE account_id = 1;

Error: CHECK constraint failed: balance >= 0

SAVEPOINT: partial rollback

A savepoint marks a point inside a transaction that you can roll back to without abandoning the whole transaction. Here a transfer of €100 stays, but an optional €5 fee is undone:

BEGIN;
CREATE TABLE accounts (
  account_id  INTEGER PRIMARY KEY,
  owner       TEXT    NOT NULL,
  balance     INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sanne', 500), (2, 'Kwame', 100), (3, 'Mei', 0);
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 5 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 5 WHERE account_id = 3;
ROLLBACK TO before_fee;
COMMIT;

SELECT * FROM accounts ORDER BY account_id;

account_id  owner  balance
----------  -----  -------
1           Sanne  400
2           Kwame  200
3           Mei    0

SAVEPOINT, ROLLBACK TO and RELEASE SAVEPOINT work in SQLite, PostgreSQL and MySQL. SQL Server uses SAVE TRANSACTION before_fee; and ROLLBACK TRANSACTION before_fee;.

Autocommit

If you never type BEGIN, you are still using transactions. In autocommit mode — the default in SQLite, PostgreSQL, MySQL and SQL Server — every statement is its own transaction, committed automatically when it succeeds. That is why a single UPDATE on a thousand rows never stops halfway: it is atomic on its own. Explicit transactions are needed only to group several statements. Not every tool behaves the same: Oracle's tools, and Python's sqlite3 module in its default mode, open a transaction for you and expect an explicit commit (see Working with Databases from Code).

ACID, one property at a time

ACID is the name for the four guarantees a transactional database gives: atomicity, consistency, isolation and durability. The term was coined by Theo Härder and Andreas Reuter in 1983.

Atomicity means a transaction is indivisible: if any part fails or is rolled back, the database returns to the state before it began. The examples above showed exactly this.

Consistency means a transaction can only move the database from one valid state to another, where "valid" is defined by its constraints — primary and foreign keys, CHECK, NOT NULL (see Designing Tables). The CHECK on balance made the overdraft impossible. Business rules that are not expressed as constraints remain the application's responsibility. (This is not the same "consistency" as in the CAP theorem; see NoSQL Databases.)

Isolation means concurrent transactions do not interfere: ideally, the result is as if they ran one after another. Full isolation is expensive, so databases offer weaker levels, covered below.

Durability means that once COMMIT returns, the changes survive crashes and power loss. Databases achieve this by writing every change to a log (the write-ahead log in PostgreSQL, the redo log in MySQL and Oracle, the transaction log in SQL Server; SQLite uses a rollback journal or optional WAL mode) and forcing it to disk before confirming the commit; after a crash, the log is used to recover a consistent state. The same log is the basis of point-in-time recovery and replication (see Backup, Recovery and Replication).

What goes wrong when transactions overlap

A busy database runs many transactions at the same time. Without isolation, several well-known problems can occur. A dirty read is the worst of them: one transaction reads data that another has changed but not yet committed — data that may be rolled back a moment later and so never officially existed.

The lost update is a race condition (see Concurrency Basics). The simplest cure is to let the database do the arithmetic in one statement — UPDATE products SET stock = stock - 1 WHERE product_id = 4 — instead of reading the value into the program and writing it back.

Isolation levels

An isolation level is a setting that decides which of these problems a transaction is protected from. Stronger isolation means more waiting or more retried transactions; weaker isolation means more concurrency. The SQL standard defines four levels:

The standard sets minimums; real systems often protect more. Their defaults differ, which matters when code moves from one database to another:

The level is chosen per transaction or per session. In PostgreSQL, for example (not run here):

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- statements
COMMIT;

At SERIALIZABLE, PostgreSQL may abort a transaction with a serialization failure rather than allow an anomaly; the application is expected to retry it, just like a deadlock victim.

Locking and MVCC

Databases enforce isolation with two main techniques, usually combined.

A database lock is a marker a transaction places on a row, page or table to control what other transactions may do with it until the lock is released, usually at commit or rollback. A shared (read) lock lets others read but not change the data; an exclusive (write) lock keeps others from changing it, and in purely lock-based systems from reading it too. Most systems lock individual rows, so two transactions that update different customers do not wait for each other. SQLite is the exception: it locks the whole database file, so only one transaction can write at a time.

Multiversion concurrency control (MVCC) keeps several versions of a changed row. A reader sees the last committed version as of the moment its statement or transaction started, while a writer works on a new version. The practical result: readers do not block writers, and writers do not block readers. Only two writers on the same row still wait for each other. PostgreSQL, MySQL's InnoDB and Oracle are built on MVCC; SQL Server uses it when READ_COMMITTED_SNAPSHOT or snapshot isolation is enabled (READ_COMMITTED_SNAPSHOT is on by default in Azure SQL Database). The cost is old row versions that must be cleaned up later — the reason PostgreSQL needs VACUUM (see Database Administration in Practice).

When a program needs to read a row and then update it safely, it can lock the row while reading: SELECT … FOR UPDATE in PostgreSQL, MySQL and Oracle (SQL Server uses the UPDLOCK table hint). Other transactions that want to change that row wait until the first one commits — the classic fix for a lost update when the logic cannot be written as a single UPDATE.

Deadlocks

A database deadlock occurs when two (or more) transactions each hold a lock the other needs, so each waits for the other forever. Suppose Sanne pays Kwame while, at the same moment, Kwame pays Sanne, and each transfer updates the payer's account first:

Neither transaction can continue on its own. Databases detect such cycles and break them by choosing a deadlock victim: one transaction is rolled back with an error, which releases its locks so the other can finish. PostgreSQL reports "deadlock detected", MySQL "Deadlock found when trying to get lock; try restarting transaction", and SQL Server error 1205 ("chosen as the deadlock victim"). SQLite, with its single writer, does not deadlock in this way; a second writer gets "database is locked" after its busy timeout instead.

A deadlock is not a bug in the database and not data corruption — the victim's work was rolled back cleanly. The right response is in the application:

• Retry the whole transaction when the database reports a deadlock (or serialization failure), a few times with a short, growing delay.
• Access rows in a fixed order. If every transfer locks the account with the lower id first, the cycle above cannot form — the same rule as lock ordering in Concurrency Basics.
• Keep transactions short, so locks are held briefly and the chance of overlap is small.

Keeping transactions short

Locks and row versions are held until a transaction ends. A transaction that stays open makes others wait, keeps old versions alive (which blocks cleanup and makes tables grow), and raises the chance of conflicts and deadlocks. Practical rules:

• Never wait for a person inside a transaction. Collect the user's input first, then begin, write and commit in one go.
• Do not call slow external services (payment providers, email) while a transaction is open.
• Do the reading and calculating that does not need locks before BEGIN.
• Break huge batch updates into chunks of, say, a few thousand rows, each in its own transaction.
• Always end a transaction: a program that crashes between BEGIN and COMMIT should leave the connection closed (the database then rolls back), not idle forever. Monitoring for long-running and "idle in transaction" sessions is a routine DBA task.