Database Transactions Explained: ACID and Isolation Levels

Why a bank transfer needs more than two UPDATE statements

Move $50 from one account to another and the naive version is two statements: subtract from account A, add to account B. Run them separately, and if the process crashes or the connection drops between the two, the money is gone from A and never arrives in B. The database did exactly what it was told, twice. The business still lost $50.

A transaction wraps both statements so the database treats them as one unit: either both happen or neither does. That guarantee, plus three others, is what “ACID” names. It’s the reason you can trust a database with money or stock levels, where “half-done” is worse than “not done.”

What the four ACID properties guarantee

• Atomicity. All the statements in a transaction commit together, or none of them do. If the process crashes after the debit but before the credit, the database rolls the debit back too. There is no partial state to clean up by hand.
• Consistency. A transaction can only move the database from one valid state to another. If a CHECK constraint says a balance can’t go negative, no transaction is allowed to commit a row that violates it, no matter what the application code tried to do.
• Isolation. Transactions running at the same time don’t see each other’s half-finished work. How strictly that is enforced is a dial, not a fixed rule: that dial is the isolation level, covered below.
• Durability. Once the database says a transaction committed, that data survives a crash one second later. This is what the write-ahead log is for: the change is on disk (or replicated) before the commit returns.

Atomicity and durability are the database’s job, and mainstream engines do them correctly. Isolation is the one you make decisions about. Stricter isolation costs concurrency, and the cost shows up in production latency graphs.

How to run a transaction with BEGIN, COMMIT and ROLLBACK

In PostgreSQL (and most SQL databases, with minor syntax differences), a transaction is the statements between BEGIN and COMMIT:

1
2
3
4
5
6

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

If something goes wrong before COMMIT, say a constraint violation or a business check in your application code, ROLLBACK undoes everything since BEGIN:

1
2
3
4
5

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- business rule check in application code finds balance is now negative
ROLLBACK;

After the rollback, account 1’s balance is exactly what it was before BEGIN ran. For a long transaction where you only want to undo part of the work, a savepoint gives you a partial rollback:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

ROLLBACK TO before_credit;  -- undoes only the credit
UPDATE accounts SET balance = balance + 50 WHERE id = 3; -- credit a different account instead

COMMIT;

Outside an explicit BEGIN, most drivers run every statement in its own auto-committed transaction. Each statement is atomic on its own, but nothing ties two statements together. Skip BEGIN in the bank transfer and you are back to the lost $50.

What transaction isolation levels prevent

Two concurrent transactions can interfere with each other in three specific ways, and isolation levels are defined by which of these they allow:

The SQL standard defines four levels around these three anomalies:

You will rarely want READ UNCOMMITTED. PostgreSQL doesn’t even implement it and quietly runs Read Committed instead; MySQL does honor it and will show you dirty reads. In practice the two levels that matter are Read Committed and Repeatable Read, and the two major open-source databases ship different defaults:

• PostgreSQL defaults to Read Committed. Each statement inside a transaction sees a fresh snapshot taken when that statement starts, so two SELECTs in the same transaction can return different data if another transaction committed in between.
• MySQL (InnoDB) defaults to Repeatable Read. The snapshot is taken once, at the first read in the transaction, and every later plain SELECT in that transaction sees that same snapshot, even if another transaction has committed an update since.

In PostgreSQL, set the level right after BEGIN, before any query runs:

1
2
3
4
5
6
7

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- ... later in the same transaction ...
SELECT balance FROM accounts WHERE id = 1; -- guaranteed same value as above
COMMIT;

In MySQL, the SESSION form changes the level for every following transaction on that connection:

1
2

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

How Repeatable Read blocks a phantom read in PostgreSQL

Open two psql sessions against the same table and run this. Seeing it once sticks better than memorizing the table.

Session A:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Session B, while A’s transaction is still open:

1
2
3

BEGIN;
INSERT INTO accounts (id, balance) VALUES (9, 2000);
COMMIT;

Session A, same open transaction, re-running the identical query:

1
2
3
4

SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

The row B inserted and committed doesn’t appear. PostgreSQL’s Repeatable Read takes its snapshot at the first query of A’s transaction and keeps it until COMMIT, so new rows from other transactions never show up. That is stricter than the standard asks for: the table above allows phantoms at this level, but PostgreSQL’s implementation doesn’t produce them. Run the same sequence with A on Read Committed and the second SELECT returns the new row, because Read Committed takes a fresh snapshot per statement.

Serializable goes further. It detects when two concurrent transactions produced a result that couldn’t have happened in any one-at-a-time order, and aborts one of them with a serialization failure (PostgreSQL returns SQLSTATE 40001) instead of letting the anomaly commit. This is the level that closes the classic race condition where two transactions each read a value, decide based on it, and both write. Code using SERIALIZABLE has to be ready to retry:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;
-- on error 40001 ("could not serialize access due to read/write dependencies"):
-- roll back and retry the whole transaction

What stricter isolation levels cost

The price of stricter isolation is concurrency:

• Writes stay locked until commit, at every level. An UPDATE takes a row lock the moment it runs and holds it until COMMIT or ROLLBACK, regardless of isolation level. What Repeatable Read and Serializable add on top is holding the same snapshot (or, for Serializable, predicate locks on the rows your queries examined) for the whole transaction. Make a slow external API call between BEGIN and COMMIT and every row you’ve written stays locked, and every row you’ve read under Serializable stays tracked, for as long as that call takes.
• Retries aren’t optional under Serializable. It will reject valid-looking transactions under concurrent load by design. If your application doesn’t catch serialization failures and retry, those transactions fail intermittently, under load, which is the worst time to find out. Repeatable Read can raise the same 40001 error on a plain concurrent update, so the retry code isn’t only for Serializable — any transaction above Read Committed needs it.
• Long transactions block schema changes and bloat storage. PostgreSQL’s MVCC keeps old row versions around as long as any open transaction might still need to see them. A transaction left open for hours (a common bug: opening one in a web request and never committing or rolling back) prevents VACUUM from reclaiming that space, and an ALTER TABLE on a table it touched waits for it to finish.
• Not every write needs an explicit transaction. A single UPDATE statement is already atomic. Wrapping one statement in BEGIN/COMMIT adds a round trip for no extra guarantee. Reach for an explicit transaction when correctness depends on two or more statements succeeding or failing together: the bank transfer, a signup that writes both a users row and an accounts row, an order that decrements inventory and inserts the order record.

ORMs (Entity Framework, SQLAlchemy, ActiveRecord) usually open a transaction per unit of work and commit it for you. Don’t fight that. The bug to watch for is the opposite: code that calls .save() or .commit() separately for two related writes, throwing away the atomicity the ORM would have given you.

Which isolation level to use

Start at Read Committed and move up only for the specific transactions where you can name the race you’re closing. It’s the same discipline as adding an index only after you’ve found the slow query. If you can’t write that race down in one sentence, you don’t need a stricter level yet.

Related articles

• How Database Indexes Work and When to Add One
• SQL JOIN Types Explained With Examples
• What Is a Race Condition and How to Fix One