Transactions (ACID)

Work in Safe Batches

A transaction bundles several statements into one all-or-nothing unit. If anything fails, the whole batch rolls back.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- everything saved together

-- If an error happened, instead:
ROLLBACK; -- nothing is saved

This is why transferring money between accounts never loses the 100 in the middle.

The Four ACID Properties

• Atomicity: all or nothing.
• Consistency: data stays valid from constraint to constraint.
• Isolation: concurrent transactions do not see each other mid-flight.
• Durability: committed data survives crashes.

Isolation Levels

• READ UNCOMMITTED: can see uncommitted changes; risky.
• READ COMMITTED: only committed data (PostgreSQL, SQL Server default).
• REPEATABLE READ: stable reads within the transaction (MySQL default).
• SERIALIZABLE: strongest isolation, lowest concurrency.

Savepoints

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 5 WHERE id = 1;
ROLLBACK TO SAVEPOINT before_fee;  -- undo only the fee
COMMIT;

Key Points

• Transactions are all-or-nothing groups of statements.
• ACID means atomic, consistent, isolated, durable.
• Isolation levels trade strictness for concurrency.
• SAVEPOINT lets you roll back part of a transaction.