Transactions and Isolation Levels
ACID, what each isolation level still lets through, and the three distributed options: two-phase commit, sagas and the outbox.
BEGIN [TRANSACTION] | START TRANSACTION; -- same statement, two spellings ... COMMIT | ROLLBACK;A transaction is a group of reads and writes the database treats as one unit: Atomic (all or nothing), Consistent (constraints hold at the boundaries), Isolated (concurrent transactions do not see each other's half-done work), Durable (a commit survives a crash). Isolation is the part sold by the level, and every level below serializable permits something.
| Level | Still permits |
|---|---|
| Read uncommitted | Dirty reads: you see a write that later rolls back |
| Read committed | Non-repeatable reads, phantoms, lost updates |
| Repeatable read | Phantoms (in the standard), write skew |
| Serializable | Nothing — paid for in aborts or in blocking |
What the engines actually do
Postgres's REPEATABLE READ is snapshot isolation: no phantoms, but write skew is possible. Its SERIALIZABLE has used serializable snapshot isolation since 9.1, which does not block — it aborts the loser with SQLSTATE 40001, so every serializable transaction needs a retry loop. InnoDB defaults to REPEATABLE READ and uses next-key locks (an index record lock plus a gap lock on the gap before it) on locking reads, which is how it keeps phantoms out.
Across services
Two-phase commit (XA) gives atomicity across resource managers: prepare, then commit. If the coordinator dies in between, participants sit holding locks until it comes back. Sagas (Garcia-Molina and Salem, 1987) replace that with a chain of local transactions, each with a compensating action; there is no isolation, so partial states are visible to users. The transactional outbox writes the row and the event in one local transaction and lets a relay publish afterwards — at-least-once, which is why the consumer must be idempotent.
Example
// Transactional outbox with node-postgres. The row and the event it implies
// are written in ONE local transaction, so they cannot disagree. No 2PC and
// no publishing from inside a transaction.
//
// CREATE TABLE outbox (
// id bigserial PRIMARY KEY,
// topic text NOT NULL,
// payload jsonb NOT NULL,
// created_at timestamptz NOT NULL DEFAULT now(),
// published_at timestamptz
// );
export async function placeOrder(pool, order) {
const client = await pool.connect();
try {
// BEGIN and START TRANSACTION are the same statement; Postgres and MySQL
// accept both, and BEGIN is what you will see in most code.
await client.query("BEGIN");
const { rows } = await client.query(
`INSERT INTO orders (customer_id, total_cents, status)
VALUES ($1, $2, 'placed') RETURNING id, created_at`,
[order.customerId, order.totalCents],
);
await client.query(
"INSERT INTO outbox (topic, payload) VALUES ($1, $2::jsonb)",
["order.placed", JSON.stringify({ orderId: rows[0].id, total: order.totalCents })],
);
await client.query("COMMIT");
return rows[0];
} catch (err) {
await client.query("ROLLBACK");
throw err;
} finally {
client.release();
}
}
// A separate relay polls (or reads the WAL via change data capture) and
// publishes unpublished rows. It may publish the same row twice after a
// crash between publish and UPDATE, so consumers must be idempotent.When to use it
- Deciding whether a booking flow needs SERIALIZABLE or a uniqueness constraint
- Replacing a cross-service two-phase commit with a saga after the coordinator became a single point of failure
- Publishing an event only if the database write committed, without dual writes
- Explaining why a read-modify-write loop lost updates under READ COMMITTED
More examples
Write skew, which snapshot isolation does not stop
Write skew is the anomaly interviewers probe because it survives the isolation level most teams assume is enough. Recognise the shape - read a set, decide, write somewhere else in that set - in on-call rotas, overbooking, inventory holds and budget checks.
// Two doctors both on call. Each transaction checks "is anyone else on call?",
// sees the other still there, and takes itself off. Under Postgres REPEATABLE
// READ (snapshot isolation) both commit and the shift ends up uncovered:
// nothing they wrote overlaps, so there is no write conflict to detect.
async function goOffCall(client, doctorId, shiftId) {
await client.query("BEGIN ISOLATION LEVEL REPEATABLE READ");
const { rows } = await client.query(
"SELECT count(*)::int AS on_call FROM shifts WHERE shift_id = $1 AND on_call",
[shiftId],
);
if (rows[0].on_call < 2) { // reads its own snapshot, not reality
await client.query("ROLLBACK");
throw new Error("you are the last doctor on call");
}
await client.query("UPDATE shifts SET on_call = false WHERE shift_id = $1 AND doctor_id = $2",
[shiftId, doctorId]);
await client.query("COMMIT");
}
// Three fixes, cheapest first:
// 1. Materialise the thing you are asserting on and lock it:
// SELECT ... FROM shifts WHERE shift_id = $1 FOR UPDATE
// 2. Express it as a constraint the database enforces.
// 3. BEGIN ISOLATION LEVEL SERIALIZABLE and retry on SQLSTATE 40001.A saga, with the compensations spelled out
The answer to "how do you do a transaction across three services" that does not involve XA. Reach for a saga when each step owns its own database and a compensating action genuinely exists - and say out loud which intermediate states users can observe.
// No distributed transaction: three local transactions plus the undo for
// each. Compensations run in reverse order and must themselves be idempotent,
// because the orchestrator can crash midway and replay.
const steps = [
{ name: "charge", run: (ctx) => payments.charge(ctx.orderId, ctx.totalCents),
undo: (ctx) => payments.refund(ctx.orderId) },
{ name: "reserve", run: (ctx) => inventory.reserve(ctx.orderId, ctx.items),
undo: (ctx) => inventory.release(ctx.orderId) },
{ name: "ship", run: (ctx) => shipping.createLabel(ctx.orderId, ctx.address),
undo: (ctx) => shipping.voidLabel(ctx.orderId) },
];
export async function runSaga(ctx) {
const done = [];
try {
for (const step of steps) {
await step.run(ctx);
done.push(step);
await recordSagaProgress(ctx.orderId, step.name); // survives a restart
}
} catch (err) {
for (const step of done.reverse()) {
// Compensation failures are an operator problem, not a caller problem:
// log loudly and keep unwinding the rest.
await step.undo(ctx).catch((e) => logger.error({ step: step.name, e }, "compensation failed"));
}
throw err;
}
}
// What you gave up: isolation. Between 'charge' and 'ship' a customer can see
// a paid order with no shipment. The UI has to have a name for that state.The retry loop SERIALIZABLE requires
Every serializable workload needs exactly this wrapper, and the bug it prevents only appears under concurrency, which is to say in production. Note the constraint it imposes: the transaction body cannot send an email or call an API, because it may run more than once.
// Postgres aborts the losing transaction instead of blocking it, so the retry
// loop is part of the feature, not an optional extra.
// 40001 = serialization_failure, 40P01 = deadlock_detected
const RETRYABLE = new Set(["40001", "40P01"]);
export async function inSerializableTransaction(pool, fn, { attempts = 5 } = {}) {
for (let attempt = 1; ; attempt++) {
const client = await pool.connect();
try {
await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
const result = await fn(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK").catch(() => {});
if (!RETRYABLE.has(err.code) || attempt >= attempts) throw err;
// Full jitter: retrying in lockstep reproduces the conflict exactly.
await new Promise((r) => setTimeout(r, Math.random() * 50 * 2 ** attempt));
} finally {
client.release();
}
}
}
// The whole transaction is retried, which is why fn must be a pure function of
// its inputs - no side effects outside the client it is handed.
Discussion