SQL Transactions: ACID Properties and Isolation Levels Explained

SQL Transactions and ACID

Transactions ensure data integrity when multiple operations must succeed or fail together. ACID properties are the theoretical guarantee behind reliable database systems — understanding them is required for senior interviews.

-- Basic transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 5000);

COMMIT;   -- all succeed together
-- ROLLBACK; -- all fail together if any error

-- Savepoints — partial rollback
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 999);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);
-- Something went wrong with items but not order:
ROLLBACK TO SAVEPOINT order_created;  -- undo items, keep order
COMMIT;  -- commit just the order

ACID Properties

Isolation Levels

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- READ UNCOMMITTED: can read other transactions' uncommitted data (dirty reads)
-- READ COMMITTED:   reads only committed data (default in PostgreSQL)
-- REPEATABLE READ:  same query returns same result within transaction (default MySQL InnoDB)
-- SERIALIZABLE:     strictest, fully isolated, lowest concurrency

-- Concurrency problems:
-- Dirty Read:       reading uncommitted data from another transaction
-- Non-repeatable:  same row read twice gets different values (committed between)
-- Phantom Read:    same query returns different row count (insert between reads)

-- Check current level
SELECT @@transaction_isolation;