ACID & the Transaction

Advanced · 22 min read · ▶ live playground · ✦ checkpoint

ACID is the contract that lets several SQL statements behave like one honest change. A transaction is that boundary: COMMIT keeps the whole unit of work, and ROLLBACK throws the whole unit away.

That matters most when money, stock, or account balances move. The database is not protecting one statement at a time anymore. It is protecting the story those statements tell together.

Autocommit: the transaction you did not notice

PostgreSQL normally runs in autocommit mode: every standalone statement is wrapped in its own tiny transaction. If it succeeds, PostgreSQL commits it immediately. If it fails, that statement rolls back.

Start with three cafe cash accounts and a small ledger table:

CREATE TABLE cafe_accounts (
  account_id integer PRIMARY KEY,
  account_name text NOT NULL UNIQUE,
  balance numeric(8,2) NOT NULL CHECK (balance >= 0)
);
 
CREATE TABLE account_entries (
  entry_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id integer NOT NULL REFERENCES cafe_accounts(account_id),
  entry_note text NOT NULL,
  amount numeric(8,2) NOT NULL
);
 
INSERT INTO cafe_accounts (account_id, account_name, balance)
VALUES
  (1, 'Ada register', 100.00),
  (2, 'Ken register', 40.00),
  (3, 'Mina register', 15.00);
 
SELECT account_id, account_name, balance
FROM cafe_accounts
ORDER BY account_id;
CREATE TABLE
CREATE TABLE
INSERT 0 3
 account_id | account_name  | balance
------------+---------------+---------
          1 | Ada register  |  100.00
          2 | Ken register  |   40.00
          3 | Mina register |   15.00
(3 rows)

Outside BEGIN, a write commits as soon as it succeeds. The Section 8 ritual still comes first: rehearse the row, write it, verify it.

SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id = 1;
 
UPDATE cafe_accounts
SET balance = balance - 5.00
WHERE account_id = 1;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id = 1;
 account_id | account_name | balance
------------+--------------+---------
          1 | Ada register |  100.00
(1 row)
UPDATE 1
 account_id | account_name | balance
------------+--------------+---------
          1 | Ada register |   95.00
(1 row)

There was no visible COMMIT, but the update is committed. In a normal database session, ROLLBACK cannot undo a statement that already committed. In this course runtime, the Run button rebuilds the whole lesson session from scratch, so it is a teaching reset, not how production undo works.

BEGIN and COMMIT: one unit of work

A transfer is the canonical transaction because half a transfer is a lie. If Ada sends Ken 25.00, the debit, the credit, and the ledger rows belong to one unit of work.

BEGIN opens the transaction. COMMIT closes it and keeps every statement inside.

BEGIN;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (1, 2)
ORDER BY account_id;
 
UPDATE cafe_accounts
SET balance = balance - 25.00
WHERE account_id = 1;
 
UPDATE cafe_accounts
SET balance = balance + 25.00
WHERE account_id = 2;
 
INSERT INTO account_entries (account_id, entry_note, amount)
VALUES
  (1, 'transfer to Ken register', -25.00),
  (2, 'transfer from Ada register', 25.00);
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (1, 2)
ORDER BY account_id;
 
COMMIT;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
ORDER BY account_id;
BEGIN
 account_id | account_name | balance
------------+--------------+---------
          1 | Ada register |   95.00
          2 | Ken register |   40.00
(2 rows)
UPDATE 1
UPDATE 1
INSERT 0 2
 account_id | account_name | balance
------------+--------------+---------
          1 | Ada register |   70.00
          2 | Ken register |   65.00
(2 rows)
COMMIT
 account_id | account_name  | balance
------------+---------------+---------
          1 | Ada register  |   70.00
          2 | Ken register  |   65.00
          3 | Mina register |   15.00
(3 rows)

The transaction made four writes behave like one change. That is the shape to remember: rehearse the target rows, BEGIN, do the related work, verify the new state, then COMMIT.

ROLLBACK: throw away the whole unit

ROLLBACK ends the transaction too, but it keeps none of the transaction's changes. Use it when the verification grid is wrong, the script touched more rows than expected, or you are intentionally testing a risky change.

BEGIN;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (1, 3)
ORDER BY account_id;
 
UPDATE cafe_accounts
SET balance = balance - 50.00
WHERE account_id = 1;
 
UPDATE cafe_accounts
SET balance = balance + 50.00
WHERE account_id = 3;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (1, 3)
ORDER BY account_id;
 
ROLLBACK;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
ORDER BY account_id;
BEGIN
 account_id | account_name  | balance
------------+---------------+---------
          1 | Ada register  |   70.00
          3 | Mina register |   15.00
(2 rows)
UPDATE 1
UPDATE 1
 account_id | account_name  | balance
------------+---------------+---------
          1 | Ada register  |   20.00
          3 | Mina register |   65.00
(2 rows)
ROLLBACK
 account_id | account_name  | balance
------------+---------------+---------
          1 | Ada register  |   70.00
          2 | Ken register  |   65.00
          3 | Mina register |   15.00
(3 rows)

Notice the final grid: the earlier committed transfer is still there, but this trial transfer vanished. ROLLBACK travels only to the start of the current transaction.

SAVEPOINT: partial rollback

Sometimes the first half of a transaction is good and the later experiment is not. A savepoint is a named checkpoint inside the open transaction. ROLLBACK TO SAVEPOINT name undoes work after that checkpoint while keeping the transaction open.

BEGIN;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (2, 3)
ORDER BY account_id;
 
UPDATE cafe_accounts
SET balance = balance + 10.00
WHERE account_id = 2;
 
SAVEPOINT before_optional_refund;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id = 3;
 
UPDATE cafe_accounts
SET balance = balance - 8.00
WHERE account_id = 3;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (2, 3)
ORDER BY account_id;
 
ROLLBACK TO SAVEPOINT before_optional_refund;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (2, 3)
ORDER BY account_id;
 
COMMIT;
 
SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id IN (2, 3)
ORDER BY account_id;
BEGIN
 account_id | account_name  | balance
------------+---------------+---------
          2 | Ken register  |   65.00
          3 | Mina register |   15.00
(2 rows)
UPDATE 1
SAVEPOINT
 account_id | account_name  | balance
------------+---------------+---------
          3 | Mina register |   15.00
(1 row)
UPDATE 1
 account_id | account_name  | balance
------------+---------------+---------
          2 | Ken register  |   75.00
          3 | Mina register |    7.00
(2 rows)
ROLLBACK
 account_id | account_name  | balance
------------+---------------+---------
          2 | Ken register  |   75.00
          3 | Mina register |   15.00
(2 rows)
COMMIT
 account_id | account_name  | balance
------------+---------------+---------
          2 | Ken register  |   75.00
          3 | Mina register |   15.00
(2 rows)

Ken's adjustment survived because it happened before the savepoint. Mina's optional refund vanished because it happened after the savepoint and before ROLLBACK TO SAVEPOINT.

The 16.3 safety wrap

Later, EXPLAIN ANALYZE will become your performance truth tool. The dangerous part is that EXPLAIN ANALYZE really executes the statement. If the statement mutates rows, those rows really change.

The reusable trick is to rehearse the target, wrap the diagnostic run in BEGIN, and end with ROLLBACK:

SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id = 2;
 
BEGIN;
 
EXPLAIN ANALYZE
UPDATE cafe_accounts
SET balance = balance + 1.00
WHERE account_id = 2;
 
ROLLBACK;

Do not read the plan yet. Section 16 owns that. For now, the point is narrower: mutating diagnostics still mutate, so use a transaction wrapper when the diagnostic work must leave no trace.

SELECT account_id, account_name, balance
FROM cafe_accounts
WHERE account_id = 2;
 account_id | account_name | balance
------------+--------------+---------
          2 | Ken register |   75.00
(1 row)

Try the choreography at the prompt

Use this playground differently from most lessons. Click Run once to create the tables, then type transaction commands into sql> one line at a time. The prompt keeps the same session open, including an open transaction, until you COMMIT, ROLLBACK, or click Run again. Run resets the whole lesson session.

sql — playgroundlive
⌘/Ctrl + Enter to run

Transactions are the single-session mechanics. Next, 12.2 adds the hard part: what happens when two sessions read and write the same rows at the same time.

Checkpoint

Answer all three to mark this lesson complete

+50 XP on completion