Transactions and ACID

Transferring 200 from one account to another is two writes: subtract here, add there. If the server dies between them, 200 has left one account and arrived nowhere.

You cannot prevent crashes. You can make the database treat the two writes as one indivisible step. That is a transaction.

The basic commands#

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    owner      TEXT NOT NULL,
    balance    NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 500.00), (2, 'Ben', 100.00);

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

BEGIN opens the transaction. Nothing you do inside it is visible to other sessions yet. COMMIT makes every change visible at once and permanent. ROLLBACK throws all of them away as if they never happened.

 BEGIN --> UPDATE Asha (-200) --> UPDATE Ben (+200) --> COMMIT
                                      |
                                crash or error here
                                      |
                                      v
                     both updates undone, balances unchanged

Outside a transaction block, every single statement is its own transaction that commits automatically. That is the default in psql, and it is why a lone UPDATE is atomic by itself.

ACID#

Four guarantees, each with a specific meaning.

Atomicity: all or nothing#

Either every statement in the transaction takes effect, or none do. If a statement fails halfway, the database rolls the whole thing back.

BEGIN;
UPDATE accounts SET balance = balance - 600 WHERE account_id = 1;
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
UPDATE accounts SET balance = balance + 600 WHERE account_id = 2;
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

Notice the second error. In PostgreSQL, once any statement fails inside a transaction, the whole transaction is marked aborted and rejects everything until you ROLLBACK. The unlucky part: your code cannot catch the error and carry on with the rest, unless it uses a savepoint.

Consistency: constraints hold#

A transaction takes the database from one valid state to another. The CHECK (balance >= 0) above is what makes the overdraft impossible. Consistency is mostly the constraints you declared, enforced for you.

It does not mean your business rules are right. If your transfer code forgets the second UPDATE, the database will happily commit a transfer that loses money. The database guarantees the rules it knows about.

Isolation: concurrent transactions do not trip over each other#

Each transaction behaves as if it ran alone, to a degree you control with an isolation level. This is a large topic and the next lesson covers it.

Durability: committed means permanent#

Once COMMIT returns, the change survives a crash or power loss. PostgreSQL does this with the write-ahead log. Before reporting success, it writes the log record for your changes and flushes it to disk (fsync). The data pages can be written lazily, and after a crash the server replays the log to rebuild them.

  COMMIT
    |
    +--> append change records to WAL ----> flush to disk ----> "COMMIT" returned
    |
    +--> data pages stay in memory, written to disk later (checkpoint)

  crash --> on restart: replay WAL --> committed changes reappear

The setting synchronous_commit = off skips waiting for the flush. Commits get faster and you may lose the last fraction of a second of commits in a crash. It never corrupts the database, only shortens its memory. Some workloads accept that trade, and money is not one of them.

How atomicity works without copying everything#

PostgreSQL uses multi-version concurrency control (MVCC). An UPDATE does not overwrite a row. It writes a new version of the row and marks the old one as superseded, tagged with transaction IDs.

 before:   [Asha 500]  (visible to everyone)
 UPDATE:   [Asha 500]  (marked: ended by txn 812)
           [Asha 300]  (created by txn 812, invisible until it commits)
 COMMIT:   txn 812 is recorded as committed -> readers now see [Asha 300]
 ROLLBACK: txn 812 is recorded as aborted   -> the new version is ignored

A rollback is nearly free because nothing was overwritten. The aborted version is simply never visible, and a background process called vacuum reclaims it later. This design is also why readers do not block writers.

Savepoints: partial rollback#

A savepoint marks a point inside a transaction you can return to without abandoning everything.

BEGIN;
INSERT INTO accounts VALUES (3, 'Chloe', 50);

SAVEPOINT try_bonus;
UPDATE accounts SET balance = balance + 1000000 WHERE account_id = 3;
-- oops, wrong number
ROLLBACK TO SAVEPOINT try_bonus;     -- undoes only the update, transaction stays usable

UPDATE accounts SET balance = balance + 10 WHERE account_id = 3;
COMMIT;                              -- Chloe exists with balance 60

Savepoints are also how application code recovers from an expected error, such as a duplicate key, without throwing away the rest of the work. Most drivers and ORMs expose this as a "nested transaction".

DDL is transactional in PostgreSQL#

BEGIN;
ALTER TABLE products ADD COLUMN discontinued BOOLEAN NOT NULL DEFAULT FALSE;
UPDATE products SET discontinued = TRUE WHERE stock_qty = 0;
-- check it looks right
ROLLBACK;                              -- the column is gone again

This makes migrations far safer than in databases that commit schema changes immediately (MySQL, Oracle). A migration with five steps either completes all five or none.

Deferred constraints#

Normally a constraint is checked at the end of each statement. A foreign key can be declared DEFERRABLE INITIALLY DEFERRED, which delays the check until COMMIT.

CREATE TABLE staff (
    staff_id   INTEGER PRIMARY KEY,
    manager_id INTEGER REFERENCES staff (staff_id) DEFERRABLE INITIALLY DEFERRED
);

Two rows that reference each other can then be inserted in one transaction, in either order, because nobody checks until the end. Use it sparingly. It hides violations until commit.

Keep transactions short#

A transaction holds its locks until it ends. A long one holds them for a long time.

• Rows you updated stay locked. Others wait.
• Old row versions cannot be vacuumed while an old transaction might still need them, so tables bloat.
• A client that runs BEGIN, does some updates and then waits for a human or a network call is idle in transaction, the most expensive state a connection can be in.

Rules of thumb: do the thinking and the network calls before BEGIN, open the transaction as late as possible, commit as early as possible. Set idle_in_transaction_session_timeout on the server as a safety net.

Transactions in application code#

Most drivers open a transaction implicitly on the first statement and keep it open until you commit.

with psycopg.connect(DSN) as conn:          # commit on normal exit, rollback on exception
    conn.execute("UPDATE accounts SET balance = balance - %s WHERE account_id = %s", (200, 1))
    conn.execute("UPDATE accounts SET balance = balance + %s WHERE account_id = %s", (200, 2))

The trap: forget to commit and the data is never saved, while the locks linger until the connection closes. Know your driver's default, which Module 7 covers.

Common mistakes#

Assuming statements in a block are independent. One failure aborts the transaction in PostgreSQL. Use savepoints to recover.

Splitting related writes across transactions. The debit commits, the credit never runs, and money is gone.

Holding a transaction open across a user prompt or an API call. It blocks others and bloats tables.

Believing COMMIT means "ready to read everywhere". Other sessions see it at their next statement or snapshot, depending on isolation level.

Disabling fsync for speed in production. A crash can corrupt the whole database.

Practice#

1. Create accounts, then write the transfer as a transaction. Verify that a transfer of 600 from Asha fails and leaves both balances intact.
2. Open two psql sessions. In session A, BEGIN and update a balance without committing. In session B, select it. What do you see? Commit in A and select again in B.
3. Use a savepoint to attempt an invalid insert and then continue the transaction.
4. Run BEGIN; DROP TABLE accounts; SELECT * FROM accounts; and then ROLLBACK;. Confirm the table is back.
5. Query pg_stat_activity for rows with state = 'idle in transaction' while a session has an open BEGIN. Explain why this is dangerous in production.