Transactions & ACID Properties

Atomicity means that a transaction is treated as a single, indivisible unit of work. Either ALL operations within the transaction complete successfully and their effects are permanently recorded, or NONE of the operations take effect — the database returns to exactly the state it was in before the transaction began. There is no middle ground. Partial completion is not a possible outcome.

The Problem Atomicity Solves

Without atomicity, a multi-step operation can be interrupted at any point — by a system crash, a hardware failure, an application error, or an explicit rollback. Each step that completed before the interruption is permanently in the database while steps after the interruption never happened. The database is left in a state that corresponds to no valid real-world scenario — it is simply wrong.

-- SCENARIO: Transfer ₹500 from Account A (balance: ₹5000) to Account B (balance: ₹2000)
-- WITHOUT atomicity guarantee — three possible failure points:

-- Step 1 executes:
UPDATE accounts SET balance = 4500 WHERE account_id = 'A';
-- ← CRASH HERE: A has ₹4500, B has ₹2000. ₹500 has vanished from the system.
-- ← APP ERROR HERE: same result. ₹500 is gone.

-- Step 2 executes:
UPDATE accounts SET balance = 2500 WHERE account_id = 'B';
-- ← CRASH HERE: A has ₹4500, B has ₹2500. Transfer succeeded — but crash means
--   the commit may not have been written to disk. On restart: depends on log.

-- WITH atomicity (transaction):
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
COMMIT;

-- Failure scenarios WITH atomicity:
-- CRASH after Step 1, before Step 2:
--   On restart: recovery system reads WAL log, sees incomplete transaction → ROLLBACK
--   A = ₹5000, B = ₹2000. Exactly as before. ₹500 never left A.

-- CRASH after Step 2, before COMMIT:
--   On restart: recovery sees transaction not committed → ROLLBACK both steps
--   A = ₹5000, B = ₹2000. Both updates are undone.

-- CRASH after COMMIT is written to log, before data pages updated:
--   On restart: recovery sees committed transaction → REDO both steps
--   A = ₹4500, B = ₹2500. Transfer completes correctly.

-- RESULT: The only possible outcomes are:
-- (A=₹4500, B=₹2500) — transfer completed, OR
-- (A=₹5000, B=₹2000) — transfer never happened
-- NEVER: (A=₹4500, B=₹2000) — money vanished

How Atomicity Is Implemented — Write-Ahead Logging

Atomicity is implemented through the Write-Ahead Log (WAL)— a persistent, sequential log file where every change is recorded before it is applied to the actual data pages. The WAL entry includes the old value (for undo) and the new value (for redo) of every modified row. On recovery after a crash, the DBMS reads the WAL, identifies which transactions were committed and which were not, redoes committed transactions whose data pages may not have been flushed, and rolls back transactions that were not committed. WAL is covered in full depth in Module 16 (Crash Recovery).

Consistency means that a transaction must bring the database from one valid state to another valid state. Every integrity constraint, business rule, and data invariant that was true before the transaction must also be true after the transaction commits. A transaction that would violate any constraint is rejected — it cannot commit.

Consistency is both a DBMS responsibility and an application responsibility. The DBMS enforces the constraints it knows about — primary keys, foreign keys, NOT NULL, CHECK constraints, UNIQUE constraints. The application must ensure that the operations it performs are semantically meaningful — the DBMS cannot know, for example, that an account balance should never go negative unless the developer explicitly creates that constraint.

DBMS-Enforced Consistency — What the Database Checks Automatically

-- DOMAIN CONSTRAINT: value must be within the defined domain
ALTER TABLE accounts ADD CONSTRAINT chk_balance_positive
    CHECK (balance >= 0);
-- Consequence: any transaction that would set balance < 0 is REJECTED
BEGIN;
    UPDATE accounts SET balance = balance - 10000
    WHERE account_id = 'A' AND balance = 500;
    -- This sets balance = -9500 → violates CHECK constraint
COMMIT;
-- ERROR: new row for relation "accounts" violates check constraint "chk_balance_positive"
-- Transaction is automatically aborted. Balance remains ₹500.

-- REFERENTIAL INTEGRITY: foreign key must reference existing row
BEGIN;
    INSERT INTO order_items (order_id, item_id, quantity, unit_price)
    VALUES (9999, 12, 2, 140.00);
    -- order_id = 9999 does not exist in orders table
COMMIT;
-- ERROR: insert or update on table "order_items" violates foreign key constraint
-- Transaction aborted. No row inserted.

-- UNIQUENESS CONSTRAINT: duplicate primary key rejected
BEGIN;
    INSERT INTO customers (customer_id, email)
    VALUES (1, 'new@email.com');
    -- customer_id = 1 already exists
COMMIT;
-- ERROR: duplicate key value violates unique constraint "customers_pkey"
-- Transaction aborted.

-- NOT NULL CONSTRAINT:
BEGIN;
    INSERT INTO orders (customer_id, restaurant_id, total_amount)
    VALUES (NULL, 2, 280.00);
    -- customer_id cannot be NULL
COMMIT;
-- ERROR: null value in column "customer_id" violates not-null constraint

Application-Enforced Consistency — What the Developer Must Ensure

Some consistency rules are too complex or context-dependent for the database to enforce automatically. The application must validate these before committing.

-- BUSINESS RULE: A customer cannot place an order if they have unpaid invoices
BEGIN;
    -- Application checks business rule BEFORE inserting the order:
    SELECT COUNT(*) FROM invoices
    WHERE customer_id = :customer_id AND status = 'overdue';
    -- If count > 0: ROLLBACK and return error to user
    -- If count = 0: proceed with order creation

    INSERT INTO orders (customer_id, restaurant_id, total_amount)
    VALUES (:customer_id, :restaurant_id, :total_amount);
COMMIT;

-- BUSINESS RULE: Total amount must equal sum of order items
-- (The DB could enforce this with a trigger, but often done in application)
BEGIN;
    INSERT INTO orders (order_id, customer_id, total_amount) VALUES (5001, 1, 420.00);
    INSERT INTO order_items VALUES (5001, 12, 2, 140.00);  -- ₹280
    INSERT INTO order_items VALUES (5001, 8,  1, 140.00);  -- ₹140
    -- Sum = ₹420 = total_amount → consistent ✓
COMMIT;

-- BUSINESS RULE: Stock cannot go negative (even without DB constraint)
BEGIN;
    SELECT stock FROM inventory WHERE product_id = :pid FOR UPDATE;
    -- FOR UPDATE: lock this row so no other transaction can modify it simultaneously
    -- If stock >= requested_quantity: proceed
    -- If stock < requested_quantity: ROLLBACK and return "out of stock" error
    UPDATE inventory SET stock = stock - :qty WHERE product_id = :pid;
COMMIT;

Isolation means that when multiple transactions execute concurrently, the intermediate state of one transaction is not visible to other transactions. Each transaction appears to execute in isolation — as if it were the only transaction running in the system. The final result of concurrent execution must be equivalent to some serial execution (running the transactions one after another).

Isolation is the most complex ACID property because it involves a fundamental trade-off: stronger isolation = stronger correctness guarantees but lower concurrency and throughput. Weaker isolation allows more concurrent execution but introduces specific classes of anomalies. SQL defines four isolation levels — each preventing a different set of anomalies — allowing applications to choose their operating point on this trade-off.

Why Isolation Is Necessary — The Seat Booking Problem

Consider an airline seat booking system. Seat 14A on flight AI-101 has one remaining seat. Two passengers attempt to book it simultaneously.

-- TRANSACTION T1 (Passenger Rahul — booking agent 1):
-- T2 starts at almost exactly the same time

-- T1: Check if seat 14A is available
SELECT count FROM seats WHERE flight = 'AI-101' AND seat = '14A';
-- Result: count = 1 (available)

-- T2: Check if seat 14A is available (executing at same moment)
SELECT count FROM seats WHERE flight = 'AI-101' AND seat = '14A';
-- Result: count = 1 (available) ← T1's reservation hasn't committed yet!

-- T1: Reserve the seat (decrement count)
UPDATE seats SET count = 0, passenger = 'Rahul' WHERE flight = 'AI-101' AND seat = '14A';
COMMIT;  -- Rahul has the seat. count = 0.

-- T2: Reserve the seat (also decrements — READING STALE DATA)
UPDATE seats SET count = 0, passenger = 'Priya' WHERE flight = 'AI-101' AND seat = '14A';
COMMIT;  -- Priya also "has" the seat. count = 0. Passenger = 'Priya' (overwrites Rahul).

-- RESULT: Both passengers have confirmation emails. Same physical seat. Conflict at boarding.
-- The airline is legally liable.

-- WITH PROPER ISOLATION (SELECT FOR UPDATE — serialises the reservation):
BEGIN;
SELECT count FROM seats WHERE flight = 'AI-101' AND seat = '14A' FOR UPDATE;
-- FOR UPDATE: acquires an exclusive lock on this row.
-- T2's SELECT FOR UPDATE on the same row now BLOCKS until T1 commits or rolls back.
UPDATE seats SET count = 0, passenger = 'Rahul' WHERE ...;
COMMIT;
-- T1 commits. Lock released. T2's SELECT FOR UPDATE now proceeds.
-- T2 reads: count = 0 (seat is gone).
-- T2 application sees count = 0, returns "seat not available" to Priya.
-- Only Rahul gets the seat. Correct.

The Four Concurrency Problems Isolation Prevents

Different isolation levels prevent different subsets of these four problems. Understanding exactly what each problem is — and what isolation level prevents it — is essential for both system design and interviews. These problems are covered in complete depth in Module 10 (Concurrency Control). Here we introduce them in the context of isolation.

The Four SQL Isolation Levels

SQL standard defines four isolation levels — from weakest to strongest. Each level is defined by which of the above anomalies it prevents. Higher isolation = stronger correctness guarantees = lower concurrency (more locking and blocking). The choice of isolation level is a deliberate engineering trade-off.

-- Set isolation level for the current transaction:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- All reads in this transaction see only committed data.

-- Set isolation level for all future transactions in the session:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- PostgreSQL: check current isolation level
SHOW transaction_isolation;

-- Common production choices:
-- Web application (read-heavy, tolerates eventual consistency):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- PostgreSQL default

-- Financial system (balance calculations, reporting):
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Prevents reading changed values mid-transaction

-- Bank account updates (must prevent lost updates and phantom reads):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Maximum correctness, minimum throughput

-- OR: use SELECT FOR UPDATE to serialise specific row access:
BEGIN;
SELECT balance FROM accounts WHERE account_id = 'A' FOR UPDATE;
-- Acquires exclusive lock on this row
-- No other transaction can modify this row until T1 commits or rolls back
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
COMMIT;

Durability means that once a transaction has been committed, its effects are permanent — they survive any subsequent failure. Power cuts, OS crashes, hardware failures, process kills — none of these can cause a committed transaction to be lost. If the database told you "COMMIT successful," your data is safe. Forever. No exceptions.

This sounds obvious, but implementing it correctly is one of the most technically challenging aspects of database engineering. The problem is the gap between RAM and disk. When your application commits a transaction, the data may still be in the database's buffer pool in RAM — not yet written to the physical disk. If the server loses power at this moment, the RAM contents are gone and the committed data is lost. Durability is the guarantee that this cannot happen.

How Durability Is Implemented — Write-Ahead Logging (WAL)

Durability is implemented through the Write-Ahead Log (WAL). The core rule of WAL is: before any change is written to the actual data pages, a record of that change must be written to the WAL and flushed to durable storage (physically written to disk, not just buffered). A COMMIT is only acknowledged to the application after the commit record has been written and flushed to the WAL.

This means: even if the server crashes immediately after sending "COMMIT" to the application, the WAL on disk contains a complete record of every change in that transaction. On restart, the recovery system reads the WAL, identifies the committed transaction, and re-applies its changes to the data pages. The committed data is recovered completely. This is covered in complete depth in Module 16.

-- WHAT HAPPENS WHEN YOU COMMIT:

-- Step 1: Application executes operations
BEGIN;
UPDATE accounts SET balance = 4500 WHERE account_id = 'A';  -- change in buffer pool
UPDATE accounts SET balance = 2500 WHERE account_id = 'B';  -- change in buffer pool

-- Step 2: Application issues COMMIT
COMMIT;

-- Step 3: Database writes commit record to WAL
-- WAL entry: TXN_ID=8821 COMMITTED
-- WAL entry: TXN_ID=8821 BEFORE(A.balance=5000) AFTER(A.balance=4500)
-- WAL entry: TXN_ID=8821 BEFORE(B.balance=2000) AFTER(B.balance=2500)
-- WAL is flushed to disk (fsync) — this is the performance-critical step

-- Step 4: "COMMIT" is returned to the application
-- The data pages (A.balance, B.balance) may still be in buffer pool, NOT on disk
-- But durability is guaranteed because WAL is on disk

-- If server crashes NOW:
-- RAM is gone: buffer pool data (updated A and B values) is lost
-- Disk has: WAL with committed transaction record
-- On restart: recovery reads WAL, sees committed TXN 8821, REDOES both updates
-- A.balance = 4500, B.balance = 2500 — correctly recovered

-- DURABILITY PERFORMANCE TRADE-OFF:
-- fsync (WAL flush) is expensive — it waits for physical disk write confirmation
-- PostgreSQL configuration:
-- synchronous_commit = on  (default): wait for WAL fsync before returning COMMIT
--   → guaranteed durability, ~1-10ms added to each transaction
-- synchronous_commit = off: return COMMIT without waiting for WAL fsync
--   → slightly faster, but up to synchronous_commit_delay (200ms) of committed
--     transactions may be lost on crash
--   → acceptable for low-stakes operations (analytics, logging)
--   → NEVER use for financial transactions

-- CHECK current setting:
SHOW synchronous_commit;

Durability vs Availability — They Are Not the Same

Durability guarantees that committed data survives failures on the local server. It does NOT guarantee that the database is always available. If the disk fails completely, the WAL is gone too and data recovery depends on backups. High availability (always reachable) and durability (no data loss on commit) are related but separate concerns. High availability requires replication — keeping copies of data on multiple servers so that if one fails, another takes over. Replication is covered in Module 17 (Distributed Databases).

Every payment involves debiting the customer's account, crediting the merchant, creating a transaction record, and updating the payment status. All four steps must be atomic — a partial payment that debits without crediting is a legal and financial disaster. Isolation at SERIALIZABLE level prevents two concurrent requests from processing the same payment twice. Durability ensures that a payment confirmed to the customer is permanently recorded even if the server crashes 10 milliseconds after the confirmation is sent.

When a customer places an order, Swiggy must atomically: (1) create the order record, (2) deduct from inventory, (3) charge the payment, (4) notify the restaurant. These are wrapped in a transaction — if payment fails, the order and inventory changes are rolled back. They use READ COMMITTED isolation for the order listing page — it is acceptable for a customer to see an order that was created 50ms ago, and the lower locking overhead supports higher throughput.

During Big Billion Days sales with thousands of concurrent purchases of limited items, lost updates are the primary threat. Without proper isolation, two customers can simultaneously read stock=1, both think they can purchase, both write stock=0 — and both receive confirmation for an item that only has one unit. Flipkart uses SELECT FOR UPDATE on inventory rows during checkout, serialising access to each product's stock count. This causes some requests to queue, but prevents overselling.

Credit score calculations involve reading multiple tables (payment history, credit utilisation, account age) and computing a score. If a user's payment is processed between two reads in the same calculation, the score could be inconsistently computed. CRED uses REPEATABLE READ isolation for credit score transactions — all reads within the transaction see a consistent snapshot of the database as of when the transaction started, regardless of concurrent updates.

Used by: PostgreSQL, MySQL, Oracle, SQL Server

Best for: financial systems, inventory, anything where correctness is mandatory

Used by: Cassandra, DynamoDB, CouchDB, many NoSQL systems

Best for: social feeds, product catalogs, user preferences — data where slight staleness is acceptable

# Python Flask backend — checkout endpoint (buggy version)
@app.route('/checkout', methods=['POST'])
def checkout():
    data = request.json

    # Step 1: Create order record
    order_id = db.execute("""
        INSERT INTO orders (customer_id, restaurant_id, total_amount, status)
        VALUES (%s, %s, %s, 'payment_pending')
        RETURNING order_id
    """, (data['customer_id'], data['restaurant_id'], data['total_amount']))
    # ← AUTO-COMMITTED immediately (no BEGIN)! Order exists in DB.

    # Step 2: Insert order items
    for item in data['items']:
        db.execute("""
            INSERT INTO order_items (order_id, item_id, quantity, unit_price)
            VALUES (%s, %s, %s, %s)
        """, (order_id, item['id'], item['qty'], item['price']))
    # ← Each INSERT AUTO-COMMITTED immediately!

    # Step 3: Process payment via external API
    try:
        payment_result = payment_gateway.charge(
            amount=data['total_amount'],
            card_token=data['card_token']
        )
    except PaymentFailedException as e:
        # Payment failed — update order status
        db.execute("""
            UPDATE orders SET status = 'payment_failed', total_amount = 0
            WHERE order_id = %s
        """, (order_id,))
        # ← Updates order to failed state
        # BUT: order_items from Step 2 were already committed and remain in DB
        # total_amount set to 0 but items still exist
        return {'error': 'Payment failed'}, 400

    return {'order_id': order_id}, 200
# BUG: The order and order_items are committed even when payment fails.
# total_amount is reset to 0 but line items remain — inconsistent state.

# Python Flask backend — checkout endpoint (FIXED with transaction)
@app.route('/checkout', methods=['POST'])
def checkout():
    data = request.json

    try:
        # START TRANSACTION — all operations are now part of one atomic unit
        db.execute("BEGIN")

        # Step 1: Create order record
        order_id = db.execute("""
            INSERT INTO orders (customer_id, restaurant_id, total_amount, status)
            VALUES (%s, %s, %s, 'payment_pending')
            RETURNING order_id
        """, (data['customer_id'], data['restaurant_id'], data['total_amount']))
        # NOT committed — held in transaction buffer

        # Step 2: Insert order items
        for item in data['items']:
            db.execute("""
                INSERT INTO order_items (order_id, item_id, quantity, unit_price)
                VALUES (%s, %s, %s, %s)
            """, (order_id, item['id'], item['qty'], item['price']))
        # NOT committed — held in transaction buffer

        # Step 3: Process payment
        payment_result = payment_gateway.charge(
            amount=data['total_amount'],
            card_token=data['card_token']
        )
        # Payment succeeded — now commit everything together

        db.execute("COMMIT")
        # COMMIT: order + order_items become permanent simultaneously
        return {'order_id': order_id}, 200

    except PaymentFailedException as e:
        db.execute("ROLLBACK")
        # ROLLBACK: order + order_items are UNDONE — as if they never existed
        # No orphaned records, no inconsistent state
        return {'error': 'Payment failed, no charges made'}, 400

    except Exception as e:
        db.execute("ROLLBACK")
        # Any unexpected error: clean rollback
        return {'error': 'Checkout failed'}, 500

# WITH TRANSACTION:
# - Payment fails → ROLLBACK → zero records in DB → customer can retry cleanly
# - Payment succeeds → COMMIT → order + items both permanently exist
# - Server crash mid-operation → auto-ROLLBACK on restart → clean state

Use the bank transfer: Atomicity — both the debit and credit happen together or neither happens; the money cannot vanish mid-transfer. Consistency — both accounts must satisfy their constraints before and after (balance cannot go negative if a CHECK constraint exists; the total money in the system is conserved). Isolation — if two transfers happen simultaneously, each transfer sees a consistent state; the intermediate state of one is not visible to the other. Durability — once the transfer is confirmed, it survives any subsequent failure; the committed changes are written to durable storage and recovered after a crash. These four properties together make the bank transfer reliable even in a system with failures and concurrent users.

Atomicity deals with the outcome of a transaction: either all operations complete or none do. It is about preventing partial completion. It applies during the transaction's execution and determines what happens on failure before commit. Durability deals with what happens after a successful commit: the committed changes persist through any subsequent failure. It is about ensuring committed data is not lost. A transaction can be atomic (never partially committed) without being durable (committed data can still be lost if it was in RAM and not flushed to disk when the power failed). Durability specifically addresses the gap between acknowledging a commit and physically writing it to non-volatile storage.

Consistency is about data validity — ensuring that integrity constraints, business rules, and data invariants are satisfied before and after every transaction. It is a property of the database state at transaction boundaries. A consistent transaction moves the database from one valid state to another. Isolation is about transaction visibility — ensuring that concurrent transactions do not see each other's intermediate (incomplete) states. It is a property of how multiple transactions interact during their concurrent execution. A database can be consistent (all constraints satisfied) while not being isolated (transactions seeing each other's uncommitted changes). They address completely orthogonal concerns.

Nothing bad — this is exactly what Durability guarantees. When COMMIT returns to the application, the database has already written the commit record to the Write-Ahead Log and flushed it to disk (with synchronous_commit = on). The actual data pages may still be in the buffer pool in RAM, but the WAL on disk contains a complete record of all changes. On restart, the recovery system reads the WAL, identifies the committed transaction (by finding its commit record), and re-applies (redoes) all its changes to the data pages. The committed data is fully recovered. The application can be confident that a committed transaction is permanent.

READ COMMITTED: a transaction only sees data that has been committed before each individual statement executes. If another transaction commits between two statements in the same transaction, the second statement sees the new data. This allows non-repeatable reads — reading the same row twice in one transaction can return different values. REPEATABLE READ: a transaction sees a consistent snapshot of the database as of the moment the transaction began. Even if other transactions commit changes to rows being read, the repeatable read transaction always sees the original values it first read. Non-repeatable reads are impossible. However, phantom reads (new rows appearing that match a previous query) are still possible in some implementations (though PostgreSQL's REPEATABLE READ actually prevents phantoms too using snapshot isolation). PostgreSQL default is READ COMMITTED. MySQL default is REPEATABLE READ.

Yes. A COMMIT can fail in several scenarios. Disk failure during WAL write: the commit record cannot be written to durable storage. The DBMS cannot guarantee durability, so the commit fails and the transaction is rolled back. Constraint violation detected at commit time: some constraints (deferred constraints in PostgreSQL) are checked at commit rather than immediately. If a violation is detected at commit time, the commit fails and the transaction is aborted. Out of disk space: if the WAL or data files cannot be written because disk is full, the commit fails. In all cases, a failed commit results in the transaction being aborted and all its changes being rolled back. The application receives an error rather than a success confirmation. No partial commits are possible.

ROLLBACK (without SAVEPOINT) aborts the entire transaction and undoes ALL changes since BEGIN. The transaction terminates and cannot continue. A new transaction must be started if needed. ROLLBACK TO SAVEPOINT undoes only the changes made after the specified SAVEPOINT was created. The transaction continues — it does NOT terminate. Changes made before the SAVEPOINT are preserved and can still be committed. The savepoint itself is preserved (you can roll back to it again). RELEASE SAVEPOINT removes a savepoint but the transaction continues. Savepoints are useful for partial error recovery within a long transaction: try a risky operation, and if it fails, rollback to the savepoint and try an alternative approach without losing the work done before the savepoint.