Transactions & ACID

BEGIN…COMMIT groups writes into one atomic, ACID unit; ROLLBACK undoes it on failure.

SQL · Intermediate · ~4 min

In plain English

Group several changes so they all happen or none do. If anything fails halfway, the database rewinds as if you never started.

Why it's worth your time

It's what stops money disappearing between two accounts, and ACID is a guaranteed interview question.

If you remember three things

Atomic, Consistent, Isolated, Durable
Isolation level trades correctness against concurrency
Long transactions hold locks and block everyone else

Overview

A transaction wraps several writes in BEGIN … COMMIT so they take effect as one atomic unit: COMMIT makes them all permanent, ROLLBACK undoes them all. Transactions guarantee ACID — Atomicity, Consistency, Isolation, Durability. Isolation levels (Read Committed, Repeatable Read, Serializable) tune how much concurrent transactions see of each other, trading anomalies against throughput.

In an interview

A transaction groups writes so they're all-or-nothing: BEGIN starts it, COMMIT makes every change permanent at once, ROLLBACK reverts them on any failure. It guarantees ACID. The classic example is a money transfer — debit A, credit B — which must never happen halfway. Isolation levels control what concurrent transactions observe.

Production defaults

Keep them short: no network calls, no LLM calls, no user waiting inside a transaction
Isolation: READ COMMITTED is the usual default; raise it only for a specific anomaly you can name
Retry: serialization failures are expected at higher isolation. Retry with backoff
Idempotency: for anything user-triggered, so a retry can't double-charge

What breaks

Deadlocks under load — Two transactions taking locks in different orders. Establish a consistent ordering and retry on failure.
Connection pool exhausted — Transactions held open across slow external calls. Move the call outside the transaction.

Watch it explained

Intro to ACID Database Transactions | Systems Design Interview: 0 to 1 with Google Software Engineer — Jordan has no life, 7:49