Transactions & ACID

What is a transaction?

A transaction is a group of database operations that must behave as one unit. The classic example is a bank transfer:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

If the server crashes between the two updates, ₹100 disappears — unless the database guarantees the ACID properties.

ACID

Property	Meaning	How databases achieve it
Atomicity	All or nothing	Undo log / rollback
Consistency	Rules (constraints, totals) hold before and after	Constraints + the other three properties
Isolation	Concurrent transactions don’t interfere	Locks, MVCC, isolation levels
Durability	Committed changes survive crashes	Write-ahead log flushed to disk at commit

Atomicity & durability: the log

Before changing any data, the database appends a record to a write-ahead log (WAL): “T1 changed A from 500 to 400”. At COMMIT, a commit record is forced to disk.

After a crash, the recovery manager reads the log:

• transactions with a COMMIT record → redo their changes if needed (durability),
• transactions without one → undo their changes using the old values (atomicity).

Run the crash scenario in the model to watch A being restored from the log.

Isolation: concurrent transactions

Databases run many transactions at once. Without control, their steps interleave and cause anomalies:

Problem	What happens
Lost update	Two transactions overwrite each other’s write
Dirty read	Reading data written by a transaction that later rolls back
Non-repeatable read	Reading the same row twice gives different values
Phantom read	Re-running a query returns new rows inserted by someone else

Locking

With two-phase locking (2PL), a transaction must take a shared lock to read and an exclusive lock to write, and it can’t take new locks after releasing one. In the with locks scenario, T2 waits until T1 commits, so the result equals running them one after the other — a serializable schedule. (Locks can cause deadlocks, which the database detects and resolves by aborting one transaction.)

Many databases (PostgreSQL, Oracle, MySQL InnoDB) also use MVCC — readers see a consistent snapshot without blocking writers.

Isolation levels (SQL standard)

Level	Dirty read	Non-repeatable read	Phantom
Read uncommitted	possible	possible	possible
Read committed	✗	possible	possible
Repeatable read	✗	✗	possible
Serializable	✗	✗	✗

Higher isolation = fewer anomalies but less concurrency.

Code (Python + SQLite)

import sqlite3

db = sqlite3.connect("bank.db")
db.execute("CREATE TABLE IF NOT EXISTS accounts(id TEXT PRIMARY KEY, balance INT CHECK(balance >= 0))")
db.execute("INSERT OR REPLACE INTO accounts VALUES ('A', 500), ('B', 300)")
db.commit()

try:
    with db:                                    # BEGIN ... COMMIT, or ROLLBACK on error
        db.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 'A'")
        db.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 'B'")
except sqlite3.Error as e:
    print("rolled back:", e)

print(db.execute("SELECT * FROM accounts").fetchall())   # [('A', 400), ('B', 400)]

The with db: block commits if everything succeeds and rolls back automatically if any statement fails — atomicity in one line.

Common mistakes

• Running related updates without a transaction (auto-commit after each statement).
• Keeping transactions open for a long time — they hold locks and block others.
• Assuming the default isolation level prevents every anomaly (it usually doesn’t; check your database’s default).