transactions & ACID

A transaction groups several statements into one all-or-nothing unit. Either every step happens, or none of them do. The classic example is moving money, you can't have the debit happen and the credit fail.

the shape

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

If something goes wrong in the middle:

ROLLBACK;

Undoes everything since BEGIN, like it never happened.

Without BEGIN, postgres runs every statement in its own tiny transaction and commits it immediately (autocommit).

an example with our data

Place an order: insert the order, insert its items, all or nothing.

BEGIN;
  INSERT INTO orders VALUES (111, 7, '2024-08-01', 'pending', 1098);
  INSERT INTO order_items VALUES (111, 1, 1), (111, 3, 1);
COMMIT;

If the second insert fails (say a typo in product_id), you'd otherwise have an order with no items. With the transaction, postgres puts it in an aborted state, and you ROLLBACK and the order is gone too.

ACID

What a transaction guarantees. Interview favourite.

• Atomicity, all or nothing. No half-done transactions.
• Consistency, the database goes from one valid state to another. Constraints (foreign keys, checks) still hold at the end.
• Isolation, transactions running at the same time don't step on each other (how much they're isolated is configurable, below).
• Durability, once COMMIT returns, it's saved. Power cut a millisecond later? Still there.

SAVEPOINT (partial undo)

BEGIN;
  INSERT INTO orders VALUES (112, 7, '2024-08-02', 'pending', 120);
  SAVEPOINT before_items;
  INSERT INTO order_items VALUES (112, 999, 1);  -- bad product, fails
  ROLLBACK TO before_items;
  INSERT INTO order_items VALUES (112, 4, 1);    -- try again
COMMIT;

what goes wrong when transactions run together

Two people doing stuff at the same time can cause:

• dirty read, you read data another transaction hasn't committed yet, and then it rolls back. You read something that never existed.
• non-repeatable read, you read a row twice in your transaction and get different values because someone else committed a change in between.
• phantom read, you run the same WHERE twice and new rows appear because someone inserted them.
• lost update, two people read balance 1000, both add 100, both write 1100. Should've been 1200.

isolation levels

From least to most strict:

level	stops	postgres notes
READ UNCOMMITTED	nothing really	postgres treats it as read committed
READ COMMITTED	dirty reads	postgres default
REPEATABLE READ	+ non-repeatable reads, (and phantoms in postgres)
SERIALIZABLE	everything, acts like transactions ran one by one	may throw errors you have to retry

BEGIN ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

Stricter = safer but more conflicts and retries. Most apps live happily on read committed.

avoiding lost updates

Do the math in the database, not the app:

-- bad: app reads 1000, adds 100, writes 1100
UPDATE accounts SET balance = 1100 WHERE account_id = 1;
-- good: atomic
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;

Or lock the row while you work with it:

BEGIN;
  SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;  -- others wait here
  -- do your checks
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
COMMIT;

deadlocks

A locks row 1 and wants row 2. B locks row 2 and wants row 1. Both wait forever. Postgres detects this and kills one of them with an error.

Avoid it by always locking things in the same order (like lower id first), and keeping transactions short.

keep transactions short

A transaction that stays open holds locks and blocks cleanup (vacuum). Never BEGIN, then wait for a user to click something, then COMMIT. Do the work, commit, done.

stuff that trips people up

• forgetting COMMIT in a manual session, your changes are invisible to everyone else and locks stay held.
• read-modify-write in the app causing lost updates.
• long running transactions blocking everything.
• not retrying serialization failures at SERIALIZABLE.

practice

Check queries.sql. Some of these need two separate connections (two query tabs) to see what happens.

-- transactions
-- run sample-data.sql first

-- setup ------------------------------------------------------------

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (account_id INT PRIMARY KEY, owner TEXT, balance NUMERIC(10,2) CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 'Aman', 1000), (2, 'Priya', 500);


-- examples ---------------------------------------------------------

BEGIN;
  UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
  UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;
COMMIT;
SELECT * FROM accounts;  -- 700, 800

-- this fails the CHECK, whole thing is undone
BEGIN;
  UPDATE accounts SET balance = balance + 5000 WHERE account_id = 2;
  UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1;  -- would go negative, error
ROLLBACK;
SELECT * FROM accounts;  -- still 700, 800

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  SAVEPOINT s1;
  UPDATE accounts SET balance = balance - 100000 WHERE account_id = 2;  -- error
  ROLLBACK TO s1;
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
SELECT * FROM accounts;  -- 600, 900


-- practice ---------------------------------------------------------

-- 1. write a transaction that places order 111 for customer 7 with 2 Notebooks.
--    the amount should match the price. then check orders and order_items.
-- 2. now try placing order 112 with product_id 999 (doesn't exist) inside a transaction.
--    what's left in orders after the error + rollback?
-- 3. (two tabs) tab A: BEGIN; UPDATE accounts SET balance = 0 WHERE account_id = 1;
--    tab B: SELECT * FROM accounts;   what does B see? why?
--    tab A: COMMIT;  now what does B see?
-- 4. (two tabs) tab A: BEGIN; SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
--    tab B: UPDATE accounts SET balance = 1 WHERE account_id = 2;   what happens to B?
--    tab A: COMMIT;
-- 5. clean up: delete order 111 and the accounts table


-- answers ----------------------------------------------------------

-- 1
BEGIN;
  INSERT INTO orders VALUES (111, 7, '2024-08-01', 'pending', 240);
  INSERT INTO order_items VALUES (111, 4, 2);
COMMIT;
SELECT * FROM orders WHERE order_id = 111;
SELECT * FROM order_items WHERE order_id = 111;

-- 2
BEGIN;
  INSERT INTO orders VALUES (112, 7, '2024-08-02', 'pending', 100);
  INSERT INTO order_items VALUES (112, 999, 1);  -- foreign key error
ROLLBACK;
SELECT * FROM orders WHERE order_id = 112;  -- nothing, the order got undone too

-- 3
-- before A commits, B still sees the old balance (read committed, no dirty reads).
-- after A commits, B sees 0.

-- 4
-- B just hangs, waiting for A's lock. as soon as A commits, B's update goes through.

-- 5
DELETE FROM order_items WHERE order_id = 111;
DELETE FROM orders WHERE order_id = 111;
DROP TABLE accounts;