SQL Transactions and Isolation Levels Explained

What and Why

A transaction is a unit of work that either fully happens or fully does not. Without transactions, a money transfer could debit one account and crash before crediting the other. With them, the database guarantees a consistent view to readers and an all-or-nothing outcome for writers.

Once you have multiple concurrent transactions, things get subtle. Two clients reading and writing the same rows can see each other’s half-finished work unless the isolation level prevents it. Understanding those levels is the difference between a correct app and one that produces ghost rows under load.

Mental Model

ACID summarizes the guarantees: Atomicity (all or nothing), Consistency (constraints hold across the transaction), Isolation (concurrent transactions appear to run in some order), Durability (committed data survives crashes).

BEGIN
|
v
[write/read]   --(error)--> ROLLBACK --> nothing changed
|
v
COMMIT --> visible to everyone, durable on disk

Isolation is the dimension most people get wrong. The SQL standard defines four levels by which anomalies they forbid:

• READ UNCOMMITTED: anything goes; you can see other transactions’ uncommitted writes (dirty reads).
• READ COMMITTED: you only see committed data, but two reads in the same transaction may differ.
• REPEATABLE READ: rows you read once stay the same if read again, but new rows matching your filter (phantoms) may appear.
• SERIALIZABLE: as if transactions ran one at a time.

Hands-on Example

Set up an accounts table:

CREATE TABLE accounts (
  id      INT PRIMARY KEY,
  balance NUMERIC
);
INSERT INTO accounts VALUES (1, 100), (2, 100);

A correct transfer in Postgres:

BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

If anything fails between the two updates, ROLLBACK restores both to their original values.

Now demonstrate a non-repeatable read. Open two psql sessions.

Session A:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- sees 100

Session B:

BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

Session A again:

SELECT balance FROM accounts WHERE id = 1;   -- sees 200
COMMIT;

The same read inside one transaction returned different values. Switch session A to REPEATABLE READ and the second read would still return 100.

A phantom read: in REPEATABLE READ on the SQL standard, you might see new rows that match a WHERE clause appear between two reads. Postgres’s implementation of REPEATABLE READ (snapshot isolation) prevents this; SQL Server’s traditional REPEATABLE READ does not. Read your engine’s docs.

For full safety:

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- read-modify-write logic
COMMIT;

In Postgres serializable, a conflicting concurrent transaction will fail with could not serialize access and your app must retry. That is the cost of strong guarantees.

Set the isolation per session or per transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN ISOLATION LEVEL SERIALIZABLE;

Default levels vary: Postgres defaults to READ COMMITTED, MySQL InnoDB to REPEATABLE READ, SQL Server to READ COMMITTED (often with snapshot enabled). Always check before assuming.

Common Pitfalls

Long-running transactions. A BEGIN that stays open for minutes holds locks, blocks vacuum, bloats undo logs, and eventually crashes throughput. Keep transactions short and focused.

Reading then writing without the right isolation. A classic bug:

SELECT balance FROM accounts WHERE id = 1;     -- 100
-- app logic
UPDATE accounts SET balance = 100 - 25 WHERE id = 1;

If another transaction debited in between, you have just overwritten its change. Use SELECT ... FOR UPDATE, atomic SQL (SET balance = balance - 25), or serializable isolation.

Forgetting that DDL inside a transaction may behave differently per engine. Postgres is great here; MySQL implicitly commits before most DDL.

Catching exceptions silently in app code and then issuing COMMIT. You may persist partial work. Always rollback explicitly on error.

Assuming SERIALIZABLE means “slow but safe”. In Postgres, serializable snapshot isolation is often nearly as fast as READ COMMITTED for well-indexed workloads. Measure rather than guess.

Practical Tips

Wrap multi-statement operations in transactions; do not rely on autocommit to do the right thing across two statements.

Use SELECT ... FOR UPDATE to lock specific rows when you intend to modify them based on what you read. FOR NO KEY UPDATE and FOR SHARE exist for narrower needs.

Add a retry loop around transactions that use SERIALIZABLE or that may deadlock. A two-or-three-attempt loop with brief backoff handles nearly all real-world contention.

Prefer SQL-side increments (balance = balance - 25) to read-modify-write in app code. They are atomic without extra ceremony.

Monitor long-running transactions: SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start < now() - interval '5 min'. Killing those before they kill you is a sysadmin reflex worth building.

Wrap-up

Transactions give you atomicity and isolation; isolation levels let you trade strictness for throughput. Start with your engine’s default, learn the anomalies each level allows, and step up to serializable for the genuinely tricky cases. Keep transactions short, write idempotent retry logic, and your database will quietly handle the concurrency that would otherwise produce ghost orders and missing money.