Transactions and concurrency

Transactions group multiple operations into an all-or-nothing unit. Either everything succeeds (COMMIT) or everything is undone (ROLLBACK). This is how databases keep data consistent even when things go wrong.

ACID properties

Yes! PostgreSQL wraps every standalone statement in an implicit transaction (autocommit mode). When you write BEGIN explicitly, you are grouping multiple statements into one transaction so they either all succeed or all fail together.

transaction_setup.sql
sql
-- The Olist dataset has no bank accounts, so create two small
-- practice tables for the transaction demos in this lesson
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  balance NUMERIC NOT NULL
);

INSERT INTO accounts (id, balance) VALUES (1, 500), (2, 500);

CREATE TABLE audit_log (
  action TEXT
);

Run this once before the examples below. These throwaway tables give you rows that are safe to update, unlike the analytics tables you have been querying.

transactions.sql
sql
-- Basic transaction: transfer money between accounts
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- If anything fails, ROLLBACK undoes everything
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- Oops, something went wrong!
ROLLBACK;
-- Account 1 balance is unchanged

BEGIN starts a transaction. COMMIT saves all changes. ROLLBACK undoes all changes since BEGIN. No partial updates.

Sometimes you want to undo part of a transaction without aborting the whole thing. SAVEPOINTs let you create checkpoints within a transaction.

savepoints.sql
sql
-- SAVEPOINT: partial rollback within a transaction
BEGIN;
  INSERT INTO audit_log VALUES ('action 1');
  SAVEPOINT sp1;
  INSERT INTO audit_log VALUES ('action 2');
  -- Undo only action 2
  ROLLBACK TO sp1;
  INSERT INTO audit_log VALUES ('action 2 retry');
COMMIT;
-- Result: action 1 + action 2 retry are saved

SAVEPOINT creates a checkpoint within a transaction. ROLLBACK TO undoes back to that checkpoint without aborting the entire transaction.

PostgreSQL uses row-level locking. The first transaction locks the row. The second transaction waits until the first commits or rolls back. If two transactions lock rows in opposite order, you get a deadlock. PostgreSQL detects this and automatically aborts one transaction.

Matching exercise: ACID properties

Loading practice…

Fill in the blanks: Complete the transaction

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Safe account transfer

Loading practice…