Meritshot

Chapter 17 of 18

Transactions & ACID

Ensure data integrity with transactions — BEGIN, COMMIT, ROLLBACK, SAVEPOINT, isolation levels, and the ACID properties.

Meritshot10 min read
SQLTransactionsACIDCOMMITROLLBACKIsolationConcurrency
All SQL Chapters

What Is a Transaction?

A transaction is a sequence of SQL statements treated as a single logical unit of work. Either all statements succeed (and their changes are saved), or none of them do (and everything is rolled back).

The classic example is a bank transfer:

BEGIN;

-- Step 1: Deduct from sender
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;

-- Step 2: Credit the receiver
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;

COMMIT;  -- Both steps saved permanently

If anything goes wrong between Step 1 and Step 2 (power failure, error, application crash), ROLLBACK undoes Step 1 — the money is never lost.

Without transactions, Step 1 might succeed but Step 2 fail — leaving money "in the air."

The ACID Properties

ACID is the standard guarantee that database transactions must provide:

PropertyMeaning
AtomicityAll operations succeed or all are rolled back — no partial updates
ConsistencyTransaction brings database from one valid state to another (constraints hold)
IsolationConcurrent transactions don't interfere with each other
DurabilityCommitted data survives crashes (written to disk)

Atomicity

BEGIN;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
-- If the next statement fails, the above is automatically undone
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;

COMMIT;
-- Only if both succeed do the changes become permanent

Consistency

Constraints enforce consistency. If an UPDATE would violate a CHECK or FOREIGN KEY, the entire transaction fails:

BEGIN;

UPDATE accounts SET balance = -100 WHERE account_id = 101;
-- If balance has CHECK (balance >= 0), this triggers an error
-- The entire transaction is rolled back

ROLLBACK;  -- or the DB rolls back automatically

Durability

Once COMMIT is executed, the data is persisted to disk. Even if the server crashes immediately after, the data survives and appears correctly on restart.

Transaction Syntax

PostgreSQL / SQLite

BEGIN;           -- start transaction
-- ... SQL statements ...
COMMIT;          -- save all changes
-- OR
ROLLBACK;        -- undo all changes

MySQL

START TRANSACTION;
-- ... SQL statements ...
COMMIT;
-- OR
ROLLBACK;

-- Auto-commit can be toggled
SET autocommit = 0;  -- disable for session

SQL Server

BEGIN TRANSACTION;
-- ... SQL statements ...
COMMIT TRANSACTION;
-- OR
ROLLBACK TRANSACTION;

Auto-Commit Mode

Most databases operate in auto-commit mode by default — every statement is automatically wrapped in its own transaction and committed immediately:

-- Without explicit BEGIN, each statement is its own transaction
UPDATE employees SET salary = 80000 WHERE id = 1;  -- immediately committed

To control transactions explicitly, you must either:

  1. Start with BEGIN / START TRANSACTION
  2. Or disable auto-commit: SET autocommit = 0 (MySQL)

ROLLBACK — Undoing Changes

BEGIN;

UPDATE employees SET salary = salary * 2;  -- accidental! doubles everyone's salary
-- Oops — we can undo it:

ROLLBACK;
-- The UPDATE is reversed; salaries are unchanged

Error-Triggered Rollback

In most databases, an error inside a transaction does not automatically roll back — you must handle it:

BEGIN;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;

-- If this next statement raises an error:
INSERT INTO transfers (from_id, to_id, amount) VALUES (101, 999, 5000);
-- Error: FK violation (account 999 doesn't exist)

-- PostgreSQL: the transaction is now in an error state
-- You MUST rollback before doing anything else
ROLLBACK;

In application code, always wrap transactions in error handling:

try:
    cursor.execute("BEGIN")
    cursor.execute("UPDATE accounts SET balance = balance - 5000 WHERE id = 101")
    cursor.execute("UPDATE accounts SET balance = balance + 5000 WHERE id = 202")
    cursor.execute("COMMIT")
except Exception as e:
    cursor.execute("ROLLBACK")
    raise e

SAVEPOINT — Partial Rollback

A SAVEPOINT marks a point within a transaction that you can roll back to without undoing the entire transaction:

BEGIN;

INSERT INTO orders (customer_id, amount) VALUES (1, 15000);

SAVEPOINT after_order;   -- set a savepoint

INSERT INTO order_items (order_id, product_id, qty) VALUES (999, 5, 2);
-- Error: order_id 999 doesn't exist

ROLLBACK TO SAVEPOINT after_order;  -- undo only the last INSERT
-- The first INSERT (orders) is still pending

RELEASE SAVEPOINT after_order;   -- remove the savepoint (optional)

COMMIT;   -- commit the order (without the broken order_item)

Savepoints let you handle partial failures gracefully within a long transaction.

Transaction Isolation Levels

When multiple transactions run concurrently, they can interfere in several ways:

ProblemDescription
Dirty ReadReading uncommitted data from another transaction
Non-repeatable ReadReading the same row twice gives different results (another tx committed between reads)
Phantom ReadA range query returns different rows on repeated execution (another tx inserted rows)

Isolation levels control which of these problems are allowed:

Isolation LevelDirty ReadNon-repeatable ReadPhantom Read
READ UNCOMMITTEDPossiblePossiblePossible
READ COMMITTED (default in PostgreSQL/SQL Server)PreventedPossiblePossible
REPEATABLE READ (default in MySQL)PreventedPreventedPossible
SERIALIZABLEPreventedPreventedPrevented

Higher isolation = fewer anomalies = lower concurrency performance.

Setting Isolation Level

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... queries ...
COMMIT;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Practical Isolation Example

-- Transaction A reads account balance (balance = 10000)
-- Transaction B withdraws 8000 and commits (balance = 2000)
-- Transaction A tries to withdraw 9000:

-- READ COMMITTED: Transaction A re-reads and sees 2000 — withdrawal correctly fails
-- READ UNCOMMITTED: Transaction A might have seen the uncommitted withdrawal mid-operation

Locking

Databases use locks to enforce isolation. Common lock types:

Lock TypeAcquired ByBehaviour
Shared (S)SELECTMultiple readers allowed simultaneously
Exclusive (X)UPDATE, DELETE, INSERTOnly one writer; blocks all other readers and writers
Row-levelPer rowLocks only the affected rows
Table-levelWhole tableLocks all rows (coarser, less concurrent)

Modern databases use MVCC (Multi-Version Concurrency Control) — readers don't block writers and writers don't block readers by keeping old row versions visible to concurrent readers.

FOR UPDATE — Locking Rows for Update

BEGIN;

-- Lock the row so no other transaction can modify it before we do
SELECT balance FROM accounts WHERE account_id = 101 FOR UPDATE;

-- Now safely update (no one else can have changed it since our SELECT)
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;

COMMIT;

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs:

Transaction A: holds lock on row 1, waiting for row 2
Transaction B: holds lock on row 2, waiting for row 1
→ Neither can proceed

Databases detect deadlocks and automatically abort one of the transactions (the "victim"). The aborted transaction's client must retry.

Prevent deadlocks by:

  • Always locking rows/tables in the same order across all transactions
  • Keeping transactions short — acquire and release locks quickly
  • Using SELECT ... FOR UPDATE to lock upfront before starting modifications

Practical Transaction Patterns

Pattern 1: Money Transfer

BEGIN;

-- Check balance first
SELECT balance FROM accounts WHERE account_id = 101 FOR UPDATE;
-- (application verifies balance >= 5000)

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;

INSERT INTO transfers (from_id, to_id, amount, transferred_at)
VALUES (101, 202, 5000, CURRENT_TIMESTAMP);

COMMIT;

Pattern 2: Inventory Deduction

BEGIN;

-- Lock the product row to prevent overselling
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- (application checks stock > 0)

UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO order_items (order_id, product_id, qty) VALUES (101, 42, 1);

COMMIT;

Pattern 3: Batch Processing with Savepoints

BEGIN;

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id FROM employees LOOP
        SAVEPOINT per_emp;
        BEGIN
            -- process each employee
            UPDATE employees SET processed = TRUE WHERE id = r.id;
        EXCEPTION WHEN OTHERS THEN
            ROLLBACK TO SAVEPOINT per_emp;  -- skip failed employee, continue
            RAISE NOTICE 'Skipped employee %: %', r.id, SQLERRM;
        END;
    END LOOP;
END;
$$;

COMMIT;

Common Mistakes

1. Forgetting to COMMIT or ROLLBACK

-- In auto-commit OFF mode, forgetting COMMIT leaves changes pending
-- They will be rolled back when the session ends
BEGIN;
UPDATE employees SET salary = 90000 WHERE id = 1;
-- Session ends without COMMIT → change is lost!

2. Long-running transactions holding locks

-- Bad practice: opening a transaction and then doing slow operations
BEGIN;
UPDATE inventory SET reserved = reserved + 1 WHERE product_id = 5;
-- ... user thinks for 5 minutes ...
-- All this time, the row is locked and other transactions are blocked!
COMMIT;

Keep transactions as short as possible — do expensive computations outside the transaction.

3. Ignoring transaction errors

-- After an error in PostgreSQL, the transaction is aborted
-- Any further statements return: "ERROR: current transaction is aborted"
BEGIN;
SELECT * FROM nonexistent_table;  -- error!
SELECT * FROM employees;           -- Error: current transaction is aborted
-- You must ROLLBACK before doing anything useful
ROLLBACK;

Practice Exercises

  1. Write a transaction that transfers ₹10,000 from account A to account B. What happens if the second UPDATE fails?
  2. Use a SAVEPOINT to insert 3 order items; if the second item fails, rollback only that insert and continue with the others.
  3. What isolation level would you use to prevent phantom reads in a financial report? Why?
  4. Explain what a deadlock is and give a concrete example using two transactions and two rows.
  5. What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT name?

Summary

In this chapter you learned:

  • A transaction groups multiple statements into an all-or-nothing unit
  • BEGIN → statements → COMMIT (save) or ROLLBACK (undo)
  • ACID: Atomicity, Consistency, Isolation, Durability
  • SAVEPOINT name — partial rollback point within a transaction
  • ROLLBACK TO SAVEPOINT name — undo only back to the savepoint
  • Isolation levels: READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE
  • Higher isolation = fewer anomalies = lower throughput
  • SELECT ... FOR UPDATE — acquire exclusive row lock
  • Deadlocks: detected and auto-resolved by aborting one transaction; prevent by consistent lock ordering
  • Keep transactions short to minimise lock contention
  • Always handle errors and explicitly ROLLBACK in application code

Next up: Database Design & Normalization — design schemas that are efficient, consistent, and easy to maintain.

Previous

CTEs & Set Operations

Next

Database Design & Normalization