Transactions and ACID

• A transaction = a group of statements run as one all-or-nothing unit of work
• Money transfer = debit + credit, together or not at all
• Failure midway undoes everything already done

• Transfer: both updates apply together, or neither

BEGIN;
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
UPDATE accounts SET bal = bal + 100 WHERE id = 2;
COMMIT;

debit + credit as one unit

• Autocommit: each statement is its own transaction
• BEGIN (START TRANSACTION) turns it off until COMMIT/ROLLBACK
• ROLLBACK TO sp1 undoes only work after the savepoint; transaction stays open

SAVEPOINT sp1;
-- more statements
ROLLBACK TO sp1;
COMMIT;

work before sp1 is still committed

• Both transactions read the same value, then both write
• One overwrite is silently lost
• Fix: lock the row with SELECT ... FOR UPDATE, or use one atomic UPDATE

SELECT bal FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET bal = bal + 1 WHERE id = 1;

either form works; the atomic UPDATE needs no prior read

• Two transactions read overlapping data, update different rows
• Together they break an invariant: two doctors both go off-call
• Needs SERIALIZABLE or explicit locking

• READ COMMITTED: each statement sees data committed before it started
• REPEATABLE READ: one snapshot from the first read
• InnoDB adds gap locks, blocking most phantoms
• SERIALIZABLE: outcome equals some serial order, paid in locks or aborts
• The app must retry serialization failures

• Set it per transaction; the order differs by database, and MySQL gives ERROR 1568 if it is wrong

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

PostgreSQL: after BEGIN, before the first query

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

MySQL: before START TRANSACTION, applies to the next one

• WAL rule: the log record is flushed before the data page it describes
• COMMIT needs only the log flush, a sequential write
• Data pages are written lazily later, so durability is cheap

• Checkpoint: flush dirty pages and record a log position
• Recovery replays only the log after it, so replay is bounded

• Atomicity: all or nothing
• Consistency: valid state to valid state
• Isolation: no interference
• Durability: survives a crash

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SAVEPOINT x;
ROLLBACK TO x;
COMMIT;  -- or ROLLBACK;

PostgreSQL order: SET TRANSACTION right after BEGIN

• MySQL: SET TRANSACTION goes before START TRANSACTION; after it, ERROR 1568

• SELECT ... FOR UPDATE locks selected rows: stops lost updates
• Keep transactions short: fewer lock waits and deadlocks