Atomicity — The All-or-Nothing Guarantee

basic

Transfer $100 between two bank accounts. What happens when a crash occurs mid-transaction?

-- DANGEROUS: two separate statements (no transaction)

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

-- ⚡ System crash here — money lost forever

UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

-- SAFE: wrapped in a transaction

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

COMMIT;

-- If crash before COMMIT: automatic ROLLBACK on restart ✅

-- If COMMIT reached: both updates permanent ✅

With transaction: both updates commit, or neither does.

Undo log records: "Account A was 500 before this transaction."

On crash before COMMIT: engine replays undo log → A restored to 500.

No manual cleanup needed. Atomicity is automatic.

The BEGIN/COMMIT block makes the two UPDATEs a single atomic unit. The database engine writes the before-image of each row to an undo log. If anything fails before COMMIT, it reads the undo log in reverse and restores every changed row.

All

Consistency — Constraints Are the Guardrails

basic

An account cannot go negative. How does Consistency stop an overdraft even inside a transaction?

CREATE TABLE accounts (

id VARCHAR(10) PRIMARY KEY,

balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)

);

-- Attempt to overdraw $600 from a $500 account:

BEGIN;

UPDATE accounts SET balance = balance - 600 WHERE id = 'A';

-- Engine evaluates CHECK: -100 >= 0 → FALSE

-- Constraint violation raised immediately

COMMIT;

-- ERROR: new row for relation "accounts" violates check constraint

-- Transaction automatically rolled back

-- Account A still has $500

Consistency is enforced by the constraint layer.

The CHECK fires before the row is written.

The transaction is aborted; no partial state exists.

Application receives an error and must handle it (retry, notify user).