Chapter 16: Transactions and ACID

Transactions ensure that groups of SQL statements are executed safely and consistently. They bundle multiple operations into a single logical unit of work that either fully succeeds or has no effect at all. This chapter introduces transaction control commands, the ACID properties that define reliability, and how databases handle isolation, concurrency, and locking.

COMMIT, ROLLBACK, SAVEPOINT

A transaction starts automatically when you execute the first statement after a previous one has committed (or when you issue BEGIN or START TRANSACTION). You can then decide whether to make changes permanent or discard them.

-- Basic transaction pattern
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;   -- make changes permanent
-- or
ROLLBACK; -- undo all changes

COMMIT permanently applies all changes made since the transaction began. ROLLBACK cancels them and restores the database to its prior state.

SAVEPOINT and partial rollbacks

SAVEPOINT creates a named checkpoint inside a transaction. You can roll back to it without discarding the whole transaction.

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE product_id = 101;
SAVEPOINT before_discount;

UPDATE products SET price = price * 0.8 WHERE category = 'Sale';

-- Change of mind: cancel the discount only
ROLLBACK TO SAVEPOINT before_discount;

COMMIT;

After a ROLLBACK TO SAVEPOINT, the savepoint remains defined in most engines, allowing further rollbacks to earlier points if needed.

Autocommit mode

Autocommit executes each statement in its own implicit transaction. This is convenient for read operations but risky for multi-step updates that must stay consistent.

-- Disable autocommit in MySQL
SET autocommit = 0;

-- Explicitly start and end a transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 10;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 20;
COMMIT;

-- Re-enable autocommit
SET autocommit = 1;

Use autocommit for safe single-statement updates and disable it for atomic multi-statement operations.

Isolation levels

Isolation defines how visible uncommitted changes are between concurrent transactions. SQL defines four standard isolation levels. Each prevents certain anomalies but may reduce performance or concurrency.

Setting isolation level

-- Session-level in MySQL and PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Oracle default is READ COMMITTED

Higher isolation reduces concurrency, so choose the weakest level that preserves correctness for your workload.

Common anomalies

• Dirty read: Transaction A reads uncommitted data from B, which B later rolls back.
• Non-repeatable read: Transaction A rereads the same row and sees different data due to B’s commit.
• Phantom read: A range query in A returns new or missing rows after B inserts or deletes matching rows.

Isolation levels balance performance and correctness by deciding which of these anomalies to allow.

Concurrency control

Databases use concurrency control to let many transactions operate safely at once. Two major strategies dominate: pessimistic locking (block conflicting operations) and optimistic control (detect conflicts and retry).

Pessimistic concurrency

Pessimistic systems lock resources as soon as they are read or updated, preventing conflicts but reducing parallelism.

-- Explicit lock in SQL Server
SELECT * FROM accounts WITH (UPDLOCK)
WHERE account_id = 100;

MySQL’s InnoDB and PostgreSQL automatically lock rows written inside a transaction. You can request explicit locks using SELECT ... FOR UPDATE.

-- Reserve a row for editing
SELECT * FROM accounts
WHERE account_id = 100
FOR UPDATE;

Optimistic concurrency

Optimistic control allows concurrent transactions without locking, checking later whether data changed. It suits read-heavy workloads.

-- Example using a version column
UPDATE accounts
SET balance = balance + 50, version = version + 1
WHERE account_id = 10 AND version = 5;

If zero rows are affected, another transaction modified the same row, and the update must be retried.

Locking and deadlocks

Locks control concurrent access to data. They can apply to rows, pages, or entire tables depending on the engine and query. Proper lock management prevents inconsistent reads but may introduce deadlocks when transactions wait on each other.

Lock types

Most engines acquire and release locks automatically. Manual control is rarely needed except for specialized concurrency logic.

Deadlocks

A deadlock occurs when two transactions each hold a lock that the other needs. Neither can proceed, so the database aborts one to resolve the conflict.

-- Example scenario
-- Transaction A:
UPDATE accounts SET balance = balance - 10 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10 WHERE account_id = 2;

-- Transaction B (in parallel):
UPDATE accounts SET balance = balance - 10 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 10 WHERE account_id = 1;

Each transaction locks one row, then waits on the other’s lock, creating a circular wait. The engine detects this and rolls back one transaction.

Monitoring locks

Databases provide diagnostic views to inspect locks and waits:

• MySQL: SHOW ENGINE INNODB STATUS;
• PostgreSQL: pg_locks system view
• SQL Server: sys.dm_tran_locks DMV
• Oracle: v$lock and v$session

Regularly reviewing these can reveal long-held locks or frequent deadlocks, which suggest missing indexes or inefficient transaction design.

Transactions, isolation, and concurrency control together form the foundation of reliable SQL systems. Mastering them lets you balance correctness with performance and design applications that remain consistent even under heavy load.