Transactions and Isolation Levels
You are writing the code that moves money between two accounts. It is two statements. Take 100 from Alice, give 100 to Bob.
await db.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [alice]);
await db.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', [bob]);It passes every test. You ship it. Then one night, between those two statements, something goes wrong. The second UPDATE trips a constraint, or the database connection drops, or the process gets OOM-killed halfway through a deploy. The first write already landed. The second never runs.
Alice is out 100 dollars and Bob never saw it. You didn’t lose money to fraud or a rounding bug. You deleted it, cleanly, with correct-looking code.
The fix is to tell the database that these two writes are one indivisible unit: either both land or neither does. That unit is a transaction, and it is the single most important tool you have for keeping data honest. This article is about what a transaction actually guarantees, where those guarantees quietly stop, and what to do about the gap.
A transaction is a fence around your writes
You wrap the two statements in BEGIN and COMMIT. If anything between them fails, you ROLLBACK instead, and the database throws away every change as if you never started.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
COMMIT;Now the crash scenario is safe. If the process dies after the debit but before COMMIT, the database never made those changes durable. On the next connection they are simply gone. No half-transfer. No money in limbo.
In Node, you don’t send raw BEGIN/COMMIT strings by hand. You grab a single connection from your pool and run the whole transaction on it. This part matters: a transaction lives on one connection, so you cannot spread it across pool.query() calls that each pick a random connection.
async function transfer(pool, fromId, toId, cents) {
const client = await pool.connect(); // one dedicated connection
try {
await client.query('BEGIN');
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[cents, fromId]
);
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[cents, toId]
);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK'); // any failure: undo everything
throw err;
} finally {
client.release(); // always hand the connection back
}
}ACID, in words you’ll actually use
People recite ACID like a spell. It is four promises, and only one of them is genuinely hard.
- Atomicity. All the writes in a transaction happen together or not at all. This is the money-transfer guarantee above. A
ROLLBACK, a crash, or a lost connection leaves the database exactly as it was beforeBEGIN. - Consistency. The transaction moves the database from one valid state to another, respecting your constraints (foreign keys,
CHECK,NOT NULL, unique indexes). If a write would break a rule, the whole transaction aborts. This one is mostly you and your schema doing the work, not the transaction machinery. - Isolation. Concurrent transactions don’t see each other’s half-done work. How much they don’t see is the isolation level, and that is the dial this whole article turns on.
- Durability. Once
COMMITreturns, the data survives a power cut. Postgres writes changes to a write-ahead log and flushes it before telling you the commit succeeded.
Atomicity and durability are the ones people mean when they say “just use a transaction,” and Postgres hands them to you for free. Consistency is your schema’s job. Isolation is where the real subtlety lives, because the moment two transactions run at the same time, “don’t see each other’s half-done work” turns out to have many possible meanings, each with a different cost.
The trouble starts when two run at once
A single transaction, alone, is easy. Real servers run hundreds at a time, all poking the same rows. That overlap creates a family of bugs called concurrency anomalies. They don’t show up on your laptop with one user. They show up in production, at load, and they are miserable to reproduce.
Each anomaly below is a specific bad interleaving of two transactions. Learn to see them as two timelines running down the page.
Dirty read
Transaction A changes a row but hasn’t committed. Transaction B reads that uncommitted value. Then A rolls back. B acted on a number that never really existed.
Here’s the good news, and it’s specific to Postgres: you cannot get a dirty read. Even if you explicitly ask for the weakest level, READ UNCOMMITTED, Postgres quietly treats it as READ COMMITTED. Its concurrency model has no way to expose uncommitted rows to another transaction. So of the five anomalies here, this is the one you can cross off the list on Postgres and never think about again.
Non-repeatable read
You read the same row twice in one transaction and get two different values, because someone else committed a change in between.
T1: SELECT price FROM items WHERE id = 7; -- 100
T2: UPDATE items SET price = 120 WHERE id = 7; COMMIT;
T1: SELECT price FROM items WHERE id = 7; -- 120 (same query, different answer)At READ COMMITTED, this is allowed and normal. Each statement sees the latest committed data as of the moment it runs. Usually fine. Occasionally a disaster, if you read a value, make a decision, and re-read expecting it to be stable.
Phantom read
Same idea, but for a set of rows instead of one. You run a query, someone inserts a new row that matches your WHERE, and when you run the query again you get an extra row that wasn’t there before.
T1: SELECT count(*) FROM orders WHERE user_id = 5; -- 3
T2: INSERT INTO orders (user_id) VALUES (5); COMMIT;
T1: SELECT count(*) FROM orders WHERE user_id = 5; -- 4 (a phantom appeared)Lost update
This is the one that will actually cost you, and it hides inside the most natural-looking code. You read a value into your program, change it in JavaScript, and write it back. Two requests do it at the same time.
// The trap: read-modify-write across two round trips
const { rows } = await client.query('SELECT balance FROM accounts WHERE id = $1', [id]);
const next = rows[0].balance + amount; // the math happens in Node
await client.query('UPDATE accounts SET balance = $1 WHERE id = $2', [next, id]);Two deposits of 50 land together. Both read a balance of 100. Both compute 150. Both write 150. The account should hold 200. It holds 150. One deposit vanished, and nothing errored.
Hold onto this one. We’ll fix it three different ways once the isolation levels are on the table.
Write skew: the anomaly nobody teaches
The other four anomalies are about reading. Write skew is different, and it is the one that will bankrupt you, because it survives fixes that catch everything else.
Picture a bank with an overdraft rule: your checking plus savings must never drop below zero. Right now both hold 100, so the combined balance is 200. Two withdrawals of 200 arrive at the same moment. One pulls from checking, the other from savings.
Each transaction reads the combined balance to check the rule. Each sees 200. Each concludes “200 is enough, allow it.” Then each writes to a different account.
The result: checking is minus 100, savings is minus 100, the bank is out 200 dollars, and the overdraft rule that both transactions dutifully checked is now violated. This is write skew. Two transactions each read some shared state, each make a decision that is correct in isolation, and each write to a different place, so there is no overlapping write for a lock to catch. The invariant lived across rows, and no single-row protection defends it.
This is why write skew matters so much: the usual lost-update fixes (lock the row you’re changing, guard it with a version) do nothing here, because the two transactions never touch the same row. Only true serializability catches it. Keep this example in your pocket for the isolation section.
Isolation levels: a dial from fast to correct
An isolation level is a promise about which of those anomalies the database will prevent. Stronger levels prevent more and cost you concurrency (more waiting, more conflicts, sometimes forced retries). Weaker levels let more through but let more transactions run at once. It is a genuine tradeoff, not a “just crank it to max” situation.
The SQL standard names four levels. Postgres implements three distinct behaviors (it folds READ UNCOMMITTED into READ COMMITTED) and is stricter than the standard requires at two of them.
That asterisk on Phantom matters. The SQL standard allows phantom reads at Repeatable Read. Postgres refuses them anyway, because its Repeatable Read is built on a whole-transaction snapshot. So on Postgres you get more than the standard promises. Do not carry this assumption to MySQL or SQL Server; their Repeatable Read behaves differently.
Read Committed: the default, and how it really works
Every statement at READ COMMITTED sees a fresh snapshot of everything committed up to the moment that statement began. Within one transaction, two identical SELECTs can return different rows, because each takes its own snapshot. No dirty reads, ever. Non-repeatable and phantom reads are both possible.
The crucial thing to understand is how Postgres does this: MVCC, multi-version concurrency control. Instead of readers taking locks, every row keeps multiple versions, and each statement reads the version that was current when it started. The payoff is a rule worth tattooing on your arm:
For the overwhelming majority of endpoints, Read Committed is correct and you should leave it alone. The trouble is only the two patterns above: read-modify-write (lost update) and check-an-invariant-then-write (write skew). Those you handle deliberately.
Repeatable Read and Serializable: when you need them
Both of these give your whole transaction a single snapshot, taken at its first real statement and frozen until it ends. Every SELECT inside sees the same consistent world. That kills non-repeatable reads and (on Postgres) phantoms in one move.
The difference between them is write skew. Repeatable Read still allows it. Snapshots make your reads stable, but two transactions on two separate snapshots can still each write a different row and break a cross-row invariant. Serializable closes that last gap. It uses a technique called Serializable Snapshot Isolation (SSI) that watches the read/write dependencies between live transactions and, if it spots a pattern that could not have happened in any one-at-a-time ordering, it aborts one of them.
You opt in per transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... your reads and writes ...
COMMIT;Serializable is the closest thing to “just make it correct” that exists. The catch is in that word aborts, and it changes how you have to write the calling code.
Serializable can fail your transaction, so retry it
When Postgres detects a serialization problem at Repeatable Read or Serializable, it does not silently do the wrong thing (that’s the whole point) and it does not block forever. It aborts the transaction with an error and expects you to run the whole thing again. The two errors you’ll see are:
40001(serialization_failure) at Repeatable Read, the message reads could not serialize access due to concurrent update. At Serializable you may instead get could not serialize access due to read/write dependencies among transactions, which is write skew being caught.40P01(deadlock_detected) when two transactions wait on each other’s locks (next section).
Both are transient. The correct response to either is not to log an error and give up. It is to roll back and try the transaction again from the top, on the assumption that the conflicting transaction has now finished and you’ll succeed this time.
async function runSerializable(pool, work, maxAttempts = 5) {
for (let attempt = 1; attempt <= maxAttempts; attempt++) {
const client = await pool.connect();
try {
await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
const result = await work(client); // your reads + writes
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK');
// 40001 = serialization failure, 40P01 = deadlock: both are retryable
if ((err.code === '40001' || err.code === '40P01') && attempt < maxAttempts) {
continue;
}
throw err;
} finally {
client.release();
}
}
throw new Error('transaction failed after ' + maxAttempts + ' attempts');
}Retry immediately in a tight loop and a genuinely contended row will just make all your retries collide again. Space them out with exponential backoff and a little randomness so the competing transactions don’t keep landing on the same instant. That pattern (and why the jitter is not optional) is its own topic: see retries and backoff.
Fixing lost update, three ways
Back to the deposit that vanished. You have three tools, and the right one depends on where the “modify” step happens.
1. Do the arithmetic in SQL. The simplest fix, and the one people skip right past. If the new value is a function of the old value, let the database compute it:
UPDATE accounts SET balance = balance + $1 WHERE id = $2;This is safe even at Read Committed, with no explicit locking from you. When two of these hit the same row, Postgres takes a write lock for the UPDATE, makes the second one wait for the first to commit, then re-reads the fresh committed balance and applies + $1 on top of it. No lost update. If you can express your change as “new = f(old)” in one statement, this is almost always the answer.
2. Pessimistic lock: SELECT ... FOR UPDATE. When the modification is too complex for one statement, or spans several rows and reads, lock the rows you’re about to change as you read them. FOR UPDATE takes a write lock on each returned row; any other transaction that tries to SELECT ... FOR UPDATE, UPDATE, or DELETE those rows waits until you commit.
await client.query('BEGIN');
const { rows } = await client.query(
'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE', // locked now
[id]
);
const next = computeSomethingComplex(rows[0].balance);
await client.query('UPDATE accounts SET balance = $1 WHERE id = $2', [next, id]);
await client.query('COMMIT'); // lock releasedA second transfer on the same account blocks at its own FOR UPDATE until this one commits, then reads the already-updated balance. “Pessimistic” because you assume a conflict and take the lock up front. It’s simple and correct; the cost is that contended rows serialize, and a lock held too long stalls everyone behind it.
3. Optimistic lock: a version column. When the “think” step is long or happens far from the database (a user editing a form for two minutes, say), you don’t want to hold a lock the whole time. Instead, read a version alongside the data, and make your write conditional on the version not having moved.
const { rows } = await client.query(
'SELECT balance, version FROM accounts WHERE id = $1',
[id]
);
const next = rows[0].balance + amount;
const res = await client.query(
`UPDATE accounts SET balance = $1, version = version + 1
WHERE id = $2 AND version = $3`,
[next, id, rows[0].version]
);
if (res.rowCount === 0) {
// Someone changed the row between our read and write.
// Our write matched zero rows. Re-read and try again.
}If nobody touched the row, version still matches, the update lands, and version ticks up. If someone slipped in first, version has moved, your WHERE matches nothing, rowCount is 0, and you know to retry on fresh data. “Optimistic” because you assume no conflict and only pay when one actually happens. No lock is held across the think time.
Try the race yourself. Both transactions read a balance of 100 and each deposit 50, so the correct final balance is 200. Flip between the naive read-modify-write and the version-guarded write and run the exact same interleaving:
Deadlocks: two transactions waiting on each other
Locks solve lost updates, but they introduce a new failure mode. A deadlock is when transaction A holds a lock B wants, and B holds a lock A wants. Neither can move. Both wait forever.
The classic way to build one: two transactions lock the same two rows in opposite order.
T1: UPDATE accounts ... WHERE id = 'alice'; -- locks alice
T2: UPDATE accounts ... WHERE id = 'bob'; -- locks bob
T1: UPDATE accounts ... WHERE id = 'bob'; -- waits for T2 to release bob
T2: UPDATE accounts ... WHERE id = 'alice'; -- waits for T1 to release aliceNow they’re stuck in a cycle. Postgres runs a deadlock detector, notices the loop, and breaks it by killing one transaction with 40P01. That victim’s work rolls back; the survivor proceeds. So a deadlock is not a hang (Postgres won’t let it be), but it is an error one of your transactions eats, and under load it becomes noise.
The fix is boring and total: always acquire locks in a consistent order. If every transaction locks rows sorted by id (or any fixed rule), a cycle is impossible, because you can never have one transaction holding a higher id and waiting on a lower one while another does the reverse. For the transfer, sort the two account ids before you lock them:
// lock the two accounts in a fixed order so two transfers can never deadlock
const [firstId, secondId] = [fromId, toId].sort();
await client.query('SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE', [firstId]);
await client.query('SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE', [secondId]);
// ... now do the debit and credit ...Two transfers between Alice and Bob, in either direction, both lock Alice first. One wins the lock, the other waits, and nobody deadlocks. Even so, keep the retry loop from earlier: 40P01 can still surface from locks you didn’t anticipate (like index or foreign-key locks), and retrying is the honest catch-all.
The rules that keep you out of trouble
Most transaction pain traces back to one root cause: a transaction held open too long. A long transaction holds its locks the entire time, keeps one connection out of the pool, and makes MVCC keep old row versions around so it can still see its snapshot. Everything about the database gets worse the longer a transaction lives. So:
- Keep transactions short. Open late, commit early. Do all the reading, thinking, and validating you can before
BEGIN, then make the transaction just the tight cluster of writes that must be atomic. - Never do network I/O inside a transaction. No HTTP calls, no calling a payment provider, no publishing to a queue between
BEGINandCOMMIT. If youawait fetch(...)mid-transaction, you’re holding a database connection and its locks hostage for the entire round trip to some other server, which might be slow, or timing out, or down. A 200ms API call becomes 200ms of locks other requests are blocked behind. Gather external data first, or fire the side effect after you commit. - Don’t hold a transaction open across an
awaiton something slow. This is the same rule stated for the async reality of Node: the connection is genuinely idle-but-locked while yourawaitwaits, and the pool has one fewer connection for everyone else. Under load, a handful of these will exhaust the pool and take the whole service down. - Push work into the database when you can. A single
UPDATE ... SET x = x + 1needs no explicit transaction, no lock management from you, and no round trips. The less logic that has to leave the database and come back, the fewer windows exist for an anomaly to sneak in. - Choose the isolation level per transaction, not globally. Leave the default at Read Committed. Bump the specific transaction that has a read-then-write invariant up to Serializable, wrap it in a retry loop, and leave everything else fast. Don’t set the whole database to Serializable and pay the abort tax on every trivial write.
None of this is exotic. Money-moving endpoints lean on exactly these ideas, and it’s worth seeing them combined with request-level safety in idempotency keys, where a retried request and a retried transaction meet.
Summary
- A transaction makes a group of writes atomic:
BEGIN, your statements, thenCOMMIT, orROLLBACKto undo everything. Run it on one pooled connection, never throughpool.query(). - ACID is four promises. Atomicity and durability come free; consistency is your schema’s job; isolation is the dial with real tradeoffs.
- Concurrency creates anomalies: dirty read (impossible on Postgres), non-repeatable read, phantom read, lost update (read-modify-write races), and write skew (two transactions checking a shared invariant and writing different rows).
- Postgres uses MVCC: each statement reads a snapshot, so readers don’t block writers and writers don’t block readers. The default level is Read Committed, which prevents dirty reads and nothing else.
- Repeatable Read gives your whole transaction one snapshot (no non-repeatable or phantom reads, stronger than the SQL standard), but still allows write skew. Serializable (SSI) closes that gap.
- Repeatable Read and Serializable can abort with
40001, and locks can abort with40P01. Both are transient: roll back and retry the whole transaction with backoff. - Fix lost update three ways: do the math in one
UPDATE, take a pessimisticSELECT ... FOR UPDATElock, or use an optimistic version column and retry onrowCount === 0. Fix write skew with Serializable. - Avoid deadlocks by acquiring locks in a consistent order (sort by id). Keep transactions short, and never do network I/O or slow
awaits while one is open.