Transactions and ACID

Some changes only make sense together. Moving ₹5,000 from Asha to Bilal is two updates: subtract from one balance, add to the other. If the first succeeds and the second doesn’t, money has vanished from the system. Crashes, network failures and bugs make that not just possible but, at scale, certain. Transactions are how databases make multi-step changes safe.

A transaction in SQL

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 'asha';
UPDATE accounts SET balance = balance + 5000 WHERE id = 'bilal';
COMMIT;

Between BEGIN and COMMIT, the changes are provisional. COMMIT makes them permanent and visible to others, all at once. ROLLBACK — or any failure before the commit — throws them all away.

Try it on a real database. This SQLite playground has the two accounts and a rule that no balance may go below zero. Run the transfer twice, break the rule inside a transaction, then do the same without one — and keep checking the total:

Watch a crash

Step through the same transfer twice: first as two independent updates, then as a transaction. Keep an eye on the total amount of money.

The four ACID guarantees

Atomicity

All the changes in a transaction happen, or none do. There is no half-finished transfer.

Consistency

A transaction takes the database from one valid state to another. Constraints — foreign keys, CHECK rules, unique keys — are enforced, and violating ones abort the transaction.

Isolation

Concurrent transactions don’t see each other’s unfinished work, and behave as if they ran one after another — to a degree set by the isolation level.

Durability

Once committed, changes survive crashes and power failures.

How databases keep the promise

The key mechanism is the write-ahead log (WAL). Before changing data pages, the database appends a description of the change to a sequential log on disk. Committing means writing — and flushing to durable storage — a commit record. After a crash, the database replays the log: it redoes committed changes that hadn’t reached the data files, and undoes anything without a commit. Sequential log writes are fast, which is why databases can promise durability without writing every data page immediately.

Isolation is provided by locks and by multi-version concurrency control (MVCC), where readers see a consistent snapshot while writers create new versions of rows. Module 2 covers isolation levels and the anomalies each one allows.

Isolation is not automatic protection from every race, though. The most common concurrency bug in business software is the lost update: two requests read the same value, each computes a new one in application code, and the second write silently erases the first:

Transactions in application code

with connection:                       # BEGIN … COMMIT, or ROLLBACK on error
    with connection.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (5000, "asha"))
        cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (5000, "bilal"))

• Group changes that belong together in one transaction, and let an exception trigger a rollback.
• Keep transactions short. Never hold one open while calling a slow external API or waiting for a user.
• Let the database enforce rules. A CHECK (balance >= 0) constraint protects every program that touches the table, not just the one that remembered to validate.

That completes the first module: the relational model, SELECT, joins, indexes and transactions — the core of working confidently with any SQL database. Module 2 moves on to designing schemas, reading query plans, isolation levels, replication and NoSQL.