Transactions, Isolation Levels & Concurrency Control

-- A transaction groups several statements into ONE all-or-nothing unit
BEGIN;                                   -- open the transaction

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- take £100 from Alice
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- give £100 to Bob

COMMIT;                                  -- make BOTH changes permanent, together

-- If anything failed before COMMIT you would run ROLLBACK instead,
-- and NEITHER update would happen. The account 
...

-- READ UNCOMMITTED — the weakest level. Allows a DIRTY READ:
-- you can see another transaction's changes BEFORE it commits.

-- Session B:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- sees 0 ...
-- ... but Session A (which set it to 0) might ROLLBACK!
-- Then the 0 you read never truly existed → a "dirty read".
COMMIT;

-- Almost never the right choice. PostgreSQL doesn't even
-- implement it — asking for it silently gives READ COMMIT
...

-- READ COMMITTED — the PostgreSQL default. Prevents dirty reads,
-- but each statement sees a FRESH snapshot, so re-reading can change.

BEGIN;  -- defaults to READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;   -- sees 1000

-- Meanwhile another transaction COMMITs: balance = 500

SELECT balance FROM accounts WHERE id = 1;   -- now sees 500!
-- Same query, same transaction, different answer:
-- this is a "non-repeatable read".
COMMIT;

-- REPEATABLE READ — the MySQL/InnoDB default.
-- Your snapshot is FROZEN at the first read, so rows you already
-- read keep their values for the whole transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- sees 1000

-- Another transaction COMMITs balance = 500

SELECT balance FROM accounts WHERE id = 1;   -- STILL sees 1000
-- Non-repeatable reads are gone. (In PostgreSQL this level also
-- blocks phantom rows; the SQL standard on
...

-- SERIALIZABLE — the strongest level. The database guarantees the
-- result is as if every transaction ran one-after-another, alone.
-- It prevents dirty, non-repeatable AND phantom reads.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 10
INSERT INTO orders (status) VALUES ('pending');
COMMIT;
-- If a concurrent transaction would break "as-if-sequential", PostgreSQL
-- aborts one with a serialization_failure (SQLSTATE 40001).
-- Y
...

-- MVCC = Multi-Version Concurrency Control.
-- Instead of locking rows for readers, the engine keeps OLD VERSIONS
-- of each row and shows every transaction a consistent snapshot.

-- A row carries hidden columns: xmin (txn that created it),
--                               xmax (txn that deleted/replaced it).

-- Txn 100 inserts:   (data,     xmin=100, xmax=null)        ← current
-- Txn 200 updates:   (old_data, xmin=100, xmax=200)         ← now "dead"
--                    (new_data, xmin=200
...

-- PESSIMISTIC concurrency: assume a conflict WILL happen, so lock first.
-- SELECT ... FOR UPDATE locks the matched rows until you COMMIT.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- lock row 1
-- Any other transaction that does FOR UPDATE on row 1 now WAITS here.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock released

-- Good when conflicts are frequent (e.g. hot inventory rows):
-- you pay the wait cost up front instead of retrying.

-- OPTIMISTIC concurrency: assume conflicts are RARE, don't lock —
-- detect a clash at write time using a version column.

-- Read the row AND its version:
SELECT balance, version FROM accounts WHERE id = 1;   -- balance 1000, version 7

-- Write only if nobody changed it since (version still 7):
UPDATE accounts
SET    balance = balance - 100,
       version = version + 1
WHERE  id = 1 AND version = 7;

-- If 0 rows were updated, someone else got there first →
-- re-read and try again. Great fo
...

-- SAVEPOINT = a named bookmark INSIDE a transaction.
-- ROLLBACK TO undoes work back to that bookmark WITHOUT ending the txn.

BEGIN;

INSERT INTO orders (id, customer_id, total, status)
VALUES (1001, 42, 300.00, 'processing');         -- step 1: always keep this

SAVEPOINT before_coupon;                          -- bookmark here

UPDATE orders SET total = total * 0.80 WHERE id = 1001;  -- step 2: try a discount
-- Oops — the coupon turned out to be invalid. Undo ONLY step 2:
ROLLBACK TO before
...