SQL Transactions: ACID, Isolation Levels, and COMMIT/ROLLBACK

ACID properties

ACID is the set of properties that guarantee transactions are processed reliably.

-- Atomicity: all or nothing
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- both updates succeed together, or neither does

-- If an error occurs:
ROLLBACK;  -- undoes all changes since BEGIN

BEGIN, COMMIT, ROLLBACK, SAVEPOINT

These are the control statements for managing transactions.

BEGIN;  -- start a transaction

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
INSERT INTO orders (product_id, quantity) VALUES (42, 1);

SAVEPOINT before_payment;  -- partial save point

INSERT INTO payments (order_id, amount) VALUES (LAST_INSERT_ID(), 99.99);

-- If payment fails:
ROLLBACK TO SAVEPOINT before_payment;  -- undo only the payment insert

-- If all succeeds:
COMMIT;

Isolation levels

Isolation levels control how concurrent transactions see each other's changes.

-- Set isolation level (PostgreSQL syntax)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- The four standard levels:
-- READ UNCOMMITTED: can see uncommitted changes (dirty reads)
-- READ COMMITTED:   only sees committed data (default in most DBs)
-- REPEATABLE READ:  same rows return same data within a transaction
-- SERIALIZABLE:     full isolation, as if transactions ran serially

Concurrency problems

Know these terms — they come up in every transactions interview.

-- DIRTY READ: reading uncommitted data from another transaction
-- T1 writes X=99 (not committed). T2 reads X=99. T1 rolls back.
-- T2 saw data that never existed.

-- NON-REPEATABLE READ: reading same row twice gets different values
-- T1 reads salary=80000. T2 commits salary=90000.
-- T1 reads salary again: 90000. Different result in same transaction.

-- PHANTOM READ: re-running a query returns different rows
-- T1: SELECT COUNT(*) gets 100. T2 inserts new row and commits.
-- T1: SELECT COUNT(*) gets 101. A "phantom" row appeared.

-- Which levels prevent which:
-- READ UNCOMMITTED: prevents nothing
-- READ COMMITTED:   prevents dirty reads
-- REPEATABLE READ:  prevents dirty + non-repeatable reads
-- SERIALIZABLE:     prevents all three

Exam tip

ACID is always asked in SQL interviews — be able to define each property with an example. The most common follow-up: "What isolation level does your database use by default?" (READ COMMITTED for PostgreSQL and SQL Server; REPEATABLE READ for MySQL InnoDB).