Lesson 22 / 35
Transactions and ACID
Explain atomicity, consistency, isolation and durability, and see a failed multi-step write roll back completely.
By Rahul Agarwal · 15 years in web development
All or nothing
A transaction groups several statements so the database treats them as one unit. The guarantees are summarised as ACID. Atomicity: either every statement takes effect or none does; if anything fails, the database rolls back to the state before the transaction began. Consistency: constraints such as foreign keys and CHECK rules hold before and after. Isolation: concurrent transactions do not see each other's half-finished work, to a degree set by the isolation level (PostgreSQL defaults to Read Committed). Durability: once committed, the change survives a crash. The classic example is a wallet transfer: debit one account and credit another. Without a transaction, a crash or error between the two updates leaves money created or destroyed. Note that an ORM's single create or update already runs as one atomic statement; you need an explicit transaction when several statements must succeed or fail together.
The life of a transaction
BEGIN opens the unit of work, the statements run, and then it ends with COMMIT if everything succeeded or ROLLBACK if anything failed.
Watching a rollback happen
Runs with Python's built-in SQLite. The second transfer credits Ravi first, then the debit violates the CHECK, so the credit is undone too.
import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE wallets (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
db.executemany("INSERT INTO wallets VALUES (?, ?)", [('asha', 500), ('ravi', 100)])
db.commit()
def transfer(src, dst, amount):
try:
with db: # BEGIN ... COMMIT, or ROLLBACK on exception
db.execute("UPDATE wallets SET balance = balance + ? WHERE name = ?", (amount, dst))
db.execute("UPDATE wallets SET balance = balance - ? WHERE name = ?", (amount, src))
print('ok:', amount)
except sqlite3.IntegrityError as e:
print('rolled back:', e)
transfer('asha', 'ravi', 200)
transfer('asha', 'ravi', 900)
print(db.execute("SELECT * FROM wallets ORDER BY name").fetchall())
Output:
ok: 200
rolled back: CHECK constraint failed: balance >= 0
[('asha', 300), ('ravi', 300)]A UPI transfer
When you pay a friend over UPI, the money must leave your account and arrive in theirs as one event. If the second half fails, you expect the first half to be reversed, not your balance to drop with nothing received. That expectation is atomicity.
Quick check: Which ACID property guarantees that a half-finished transfer is undone when the second update fails?
- Durability
- Isolation
- Consistency
- Atomicity
Answer
Atomicity — Atomicity is the all-or-nothing property. Consistency is about constraints holding, isolation about concurrent visibility, durability about surviving crashes after commit.