# Transactions & ACID, Explained > What a database transaction actually is, the four ACID guarantees in plain language, and what really goes wrong when transactions overlap - so you can make multiple changes all-or-nothing without losing sleep. --- # Transactions & ACID, Explained You're moving money from one account to another. You subtract $100 from Alice, and then - right there, between two statements - the process crashes, the connection drops, the server reboots. Bob never got his $100. It vanished. Somewhere a customer is furious and you're staring at two rows that don't add up. This is the problem transactions exist to solve: making a *group* of changes happen completely or not at all, with nothing torn in half. This guide gives you the mental model first (a transaction is an all-or-nothing bundle), then the four guarantees behind ACID in plain words, and finally the messy reality of what happens when many transactions run at once - dirty reads, deadlocks, and the dial you turn to trade safety for speed. ## How to read this - **Need the gist fast?** Phase 1 alone gives you the working mental model and the three commands (`BEGIN`, `COMMIT`, `ROLLBACK`) you'll use every day. - **Want it to finally make sense?** Read in order. Each phase builds on the last: the bundle (Phase 1), the four promises about that bundle (Phase 2), and what breaks when bundles overlap (Phase 3). ## The phases 1. **[What a Transaction Is](01-what-a-transaction-is.md)** - the money-transfer story, `BEGIN` / `COMMIT` / `ROLLBACK`, and the mental model of a transaction as one all-or-nothing bundle. 2. **[ACID, Explained](02-acid-explained.md)** - Atomicity, Consistency, Isolation, and Durability, each in one plain sentence with a concrete example. 3. **[Isolation & Concurrency in Real Life](03-isolation-and-concurrency.md)** - dirty reads, non-repeatable reads, and phantoms; isolation levels as a safety-vs-speed dial; and โš ๏ธ deadlocks - what they are and how real apps handle them. > This guide covers single-database transactions. Distributed transactions across multiple databases (two-phase commit, sagas) are a much harder problem with their own trade-offs - that's a follow-up guide, and you'll find a thread to it from [Scaling a Database](/guides/scaling-a-database). --- # What a Transaction Is Let's go back to that money transfer, because it's the cleanest way to feel why transactions exist. Moving $100 from Alice to Bob isn't one change - it's two: take $100 off Alice's balance, add $100 to Bob's. Both are simple `UPDATE` statements. The danger lives in the gap *between* them. If anything interrupts you after the first statement and before the second - a crash, a network drop, a thrown exception in your application code - you've created money out of thin air or destroyed it. The database doesn't know these two updates belong together. Unless you tell it. ## The mental model: a bundle that's all-or-nothing A transaction is a way of saying to the database: *"Treat these statements as one indivisible unit. Apply all of them, or - if anything goes wrong - apply none of them. Never leave me halfway."* Picture it as wrapping several changes in a single sealed envelope. While the envelope is open you can keep adding changes to it. The moment you seal it (`COMMIT`), everything inside becomes permanent together. If you tear it up instead (`ROLLBACK`), everything inside disappears together, as if you never started. ```mermaid flowchart LR B["BEGIN
(open the bundle)"] --> U["UPDATE alice -$100
UPDATE bob +$100"] U --> C["COMMIT
all changes become permanent at the same moment"] U --> R["ROLLBACK
the WHOLE bundle is thrown away,
as if nothing ever happened"] ``` That's the entire idea. Everything else in this guide is detail hanging off this one picture: changes go in a bundle, and the bundle commits or rolls back *as a whole*. ## The three commands you'll actually use **`BEGIN`** opens the envelope. From this point, your changes are provisional - visible to you, but not yet permanent and (usually) not yet visible to anyone else. **`COMMIT`** seals it. Every change since `BEGIN` becomes permanent, all at once. **`ROLLBACK`** tears it up. Every change since `BEGIN` is undone, all at once. ๐Ÿ“ **Terminology.** Different databases spell the opener slightly differently: PostgreSQL and MySQL accept `BEGIN`; the SQL standard keyword is `START TRANSACTION` (both work in MySQL and Postgres). They mean the same thing - open the bundle. We'll use `BEGIN`. ## A real example: the safe money transfer Here's the transfer done right. Watch the edges. ```sql BEGIN; UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'; COMMIT; ``` ```text name | balance name | balance name | balance -------+-------- -------+-------- -------+-------- Alice | 500 โ”€โ”€โ”€โ–บ Alice | 400 โ”€โ”€โ”€โ–บ Alice | 400 Bob | 200 Bob | 200 Bob | 300 (before) (mid-bundle, (after COMMIT - seen only by you) everyone sees this) ``` *What just happened:* Between `BEGIN` and `COMMIT`, both updates ran inside the bundle. Critically, no other connection saw Alice down $100 while Bob was still waiting - to the rest of the world, the two balances changed in the same instant, at `COMMIT`. The money was never missing and never doubled. If the server had crashed after the first `UPDATE` but before `COMMIT`, the unsealed bundle would be discarded on restart, and Alice would still have her $500. ## When something goes wrong: ROLLBACK Now suppose you check Alice's balance mid-transfer and discover she only has $50. You don't want a half-finished transfer sitting there. You throw the whole thing away: ```sql BEGIN; UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'; -- your application checks the balance, sees it went negative, and decides to abort ROLLBACK; ``` ```text name | balance name | balance name | balance -------+-------- -------+-------- -------+-------- Alice | 50 โ”€โ”€โ”€โ–บ Alice | -50 โ”€โ”€โ”€โ–บ Alice | 50 (before) (mid-bundle, (after ROLLBACK - seen only by you) back to exactly before) ``` *What just happened:* The `UPDATE` had taken effect *inside* the bundle, so your own connection saw Alice at -$50. But `ROLLBACK` discarded the entire bundle, snapping Alice's balance back to exactly where it was before `BEGIN`. No trace of the attempt remains. This is the superpower: you can make changes, look at the result, and still change your mind cleanly. โš ๏ธ **Gotcha: an open transaction holds on until you close it.** A transaction isn't free while it's open - it can hold locks and keep older row versions around so it has a consistent view. If your application opens a transaction and then wanders off (waits on a slow API call, hits an unhandled exception, or the developer forgets to commit), that bundle stays open. Other transactions can pile up behind its locks, and your database's cleanup can't reclaim space. The rule: **open a transaction as late as you can, and close it - commit or rollback - as soon as you can.** Don't do slow, unrelated work in the middle of one. ## Why this saves you later Once you see every group of related writes as a bundle, a whole category of 2am bugs stops being mysterious. "Why is this order marked paid but has no line items?" "Why does this user have a profile row but no account row?" Almost always: two writes that should have been one transaction were left as two separate statements, and something died in the gap. The fix is the same shape every time - wrap the related writes in `BEGIN ... COMMIT` so they live or die together. ## Recap 1. A **transaction** is a bundle of changes that either all happen or none do - never halfway. 2. **`BEGIN`** opens the bundle; **`COMMIT`** makes everything in it permanent at once; **`ROLLBACK`** discards everything in it at once. 3. Inside an open bundle you see your own provisional changes; the outside world sees nothing until `COMMIT`. 4. The money transfer is the canonical case: two updates that must succeed or fail *together*. 5. Keep transactions short - an open one holds locks and resources until you close it. --- # ACID, Explained "ACID-compliant" gets thrown around like a marketing checkbox, and most people can recite that it stands for Atomicity, Consistency, Isolation, and Durability without being able to say what any of them actually buy you. That's a shame - each one is a specific promise that defuses a specific way your data could get wrecked. You already met the most important one in Phase 1 without naming it. Here's the whole set, one plain sentence and one concrete example each. Keep the bundle from Phase 1 in mind throughout: ACID is really four guarantees *about that bundle*. ## A - Atomicity: all of it, or none of it **In one sentence:** every change in a transaction happens, or none of them do - there is no halfway. This is the all-or-nothing promise you already felt in the money transfer. "Atomic" means *indivisible* - you can't cut the bundle in half. **Concrete example.** You debit Alice and credit Bob inside one transaction. The server loses power right after the debit. When it comes back up, atomicity guarantees the database will *not* have applied just the debit. The unsealed bundle is discarded; Alice keeps her money. The only two legal outcomes are "both updates" or "neither update" - never "just one." ๐Ÿ’ก **Key point.** Atomicity is what makes `ROLLBACK` trustworthy. Because the bundle is indivisible, undoing it can't leave debris behind. ## C - Consistency: the rules always hold **In one sentence:** a transaction can only move the database from one valid state to another - it can never commit a state that breaks your defined rules. The "rules" here are the constraints *you* declared: a balance can't be negative, an email must be unique, every order line must point at a real order. Consistency means the database refuses to commit a transaction that would violate them. **Concrete example.** Suppose you declared a rule that account balances can't go below zero (`CHECK (balance >= 0)`). You try to overdraw Alice inside a transaction: ```sql BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice'; -- Alice only has $400 ``` ```text ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check" DETAIL: Failing row contains (Alice, -600). ``` *What just happened:* The database checked your rule, saw that the update would leave Alice at -$600, and refused - the statement errored instead of being applied. The transaction can't commit a balance that breaks the constraint. To finish at all, you'd have to issue a `ROLLBACK` (or fix the statement). The invalid state never becomes real. ๐Ÿ“ **Terminology.** "Consistency" here means *your* declared rules stay true. It is a different thing from the "C" people argue about in distributed systems (the CAP theorem), which is about whether all servers agree on the latest value. Same word, different conversation - don't let anyone conflate them. ## I - Isolation: concurrent transactions don't trip over each other **In one sentence:** when many transactions run at the same time, each one behaves as if it had the database to itself - their intermediate, uncommitted changes stay hidden from each other. This is the promise that lets a database serve thousands of users at once without their half-finished work bleeding together. **Concrete example.** Alice's transfer is mid-flight: she's been debited but the bundle isn't committed yet. At that exact moment, a reporting job runs `SELECT SUM(balance) FROM accounts`. Isolation guarantees the report does *not* see Alice's temporary, uncommitted -$100 dip. It sees a consistent picture - either the whole transfer or none of it - never the torn-in-half middle. โš ๏ธ **Gotcha.** Isolation is the one ACID property that comes with a dial, not a fixed setting. "As if it had the database to itself" is the *strongest* guarantee, and it's expensive. Most databases default to a weaker, faster level, which lets certain anomalies sneak through. Phase 3 is entirely about that dial and what slips past at each setting - it's where the real-world trouble lives. ## D - Durability: committed means committed **In one sentence:** once a transaction has committed, its changes survive anything - crash, power loss, reboot - they will be there when the database comes back. The promise is about that one instant: the moment `COMMIT` returns success. Before it returns, all bets are off (atomicity handles that). After it returns, the data is safe. **Concrete example.** Your transfer commits, the database returns `COMMIT` successfully, and you tell the user "Done." One second later the data center loses power. When the server reboots, Bob's $100 is still there. Durability is why you can trust a success message: the database wrote the committed change somewhere permanent (typically a write-ahead log on disk) *before* telling you it succeeded. ๐Ÿ’ก **Key point.** Durability is a promise about *committed* transactions only. If `COMMIT` never returned - the connection dropped while you were waiting - you don't actually know whether it landed. That uncertain case is its own headache; the safe move is to check the data rather than assume. ## How the four fit together It's tempting to memorize ACID as four trivia points. Don't. See them as four ways the database has your back across one transaction's life: ```mermaid flowchart LR B["BEGIN

Atomicity:
the bundle is
indivisible (all or none)"] --> W["work

Isolation:
others can't see
your half-finished work"] W --> C["COMMIT

Consistency:
the commit is refused
if it breaks your rules"] C --> F["forever

Durability:
once committed,
it survives any crash"] ``` Atomicity and Consistency are about *one* transaction being whole and legal. Isolation is about *many* transactions coexisting. Durability is about surviving *time and failure* after the fact. ## Recap 1. **Atomicity** - all changes in a transaction happen or none do; what makes `ROLLBACK` trustworthy. 2. **Consistency** - a transaction can't commit a state that breaks your declared rules (constraints). 3. **Isolation** - concurrent transactions don't see each other's uncommitted, in-progress changes. 4. **Durability** - once `COMMIT` succeeds, the change survives crashes and reboots. 5. Isolation is the one with a tunable dial - and the source of the real-world surprises in Phase 3. Watch it animated: [ACID transactions](/explainers/ACIDTransactions.dc.html) --- # Isolation & Concurrency in Real Life Phase 2 said isolation makes each transaction "behave as if it had the database to itself." That's the dream. The reality is that giving every transaction a truly private universe is slow, so databases offer it as a *dial* - and most ship with the dial turned down for speed. This phase is about what slips through when it's turned down, and the one concurrency hazard that can bite at *any* setting: the deadlock. This is the part of the topic people get burned by in production, so we'll go gently and concretely. ## The concurrency cheat-card > **Seeing weird behavior under load? Find the symptom, then read the section.** | Symptom | What it is | Where | |---|---|---| | Read a value that later vanished (the other side rolled back) | **Dirty read** (ยง1) | below | | Read a row twice in one transaction, got two different values | **Non-repeatable read** (ยง1) | below | | Ran the same `WHERE` twice, the second time new rows appeared | **Phantom read** (ยง1) | below | | "Too much locking / too slow" vs "occasional weird read" | You're choosing an **isolation level** (ยง2) | below | | Transaction aborted with "deadlock detected" | Two transactions waited on each other (ยง3) | below | --- ## 1. What goes wrong when transactions overlap When two transactions touch the same data at the same time, three classic anomalies can appear, in increasing order of subtlety. You don't need to memorize the names, but you do need to recognize the *shapes* - they explain bugs that look like the database "lying" to you. **Dirty read.** Your transaction reads a change another transaction made but *hasn't committed yet* - and then that other transaction rolls back. You acted on data that never really existed. ```mermaid sequenceDiagram participant A as Transaction A participant B as Transaction B A->>A: BEGIN, UPDATE alice balance = 0 B->>B: BEGIN B->>A: SELECT alice balance โ†’ reads 0 (DIRTY: not committed!) B->>B: decides Alice is broke A->>A: ROLLBACK (balance was never really 0) ``` *What just happened:* B read a value A was still working on, then A changed its mind. B made a decision on a number the database, moments later, pretended never happened. This is the worst anomaly, and most databases forbid it by default. **Non-repeatable read.** You read the same row twice in one transaction and get two different answers, because someone else committed a change in between. ```mermaid sequenceDiagram participant A as Transaction A participant B as Transaction B A->>A: BEGIN A->>A: SELECT alice balance โ†’ 400 B->>B: UPDATE alice balance = 300, COMMIT A->>A: SELECT alice balance โ†’ 300 (same query, different answer!) ``` *What just happened:* Within a single transaction, a value you already read shifted under your feet. If your logic assumed the first read was still true (say, you checked the balance, then deducted from it), you've got a bug. **Phantom read.** You run a query with a `WHERE` filter, then run it again, and *new rows* that match have appeared (or matching rows have vanished) because another transaction committed an insert or delete. ```mermaid sequenceDiagram participant A as Transaction A participant B as Transaction B A->>A: BEGIN A->>A: SELECT count(*) WHERE balance > 1000 โ†’ 3 B->>B: INSERT a $5000 account, COMMIT A->>A: SELECT count(*) WHERE balance > 1000 โ†’ 4 (a phantom row appeared) ``` *What just happened:* It's like a non-repeatable read, but for *which rows match* rather than the value in one known row. The set you're reasoning about grew or shrank mid-transaction. ๐Ÿ’ก **Key point.** Notice the progression: dirty read = reading uncommitted garbage; non-repeatable read = a row you read *changed*; phantom = the *set* of matching rows changed. Each is subtler and more expensive to prevent than the last. ## 2. Isolation levels: the safety-vs-speed dial Here's the design decision databases made. Preventing every anomaly means heavy locking and bookkeeping, which slows everything down and makes transactions wait on each other; preventing *none* is fast but lets garbage through. So the SQL standard defines four **isolation levels** - settings on the dial - each promising to block more anomalies than the last, at more cost. | Isolation level | Dirty read | Non-repeatable read | Phantom | Feel | |---|---|---|---|---| | Read Uncommitted | possible | possible | possible | fastest, least safe | | Read Committed | prevented | possible | possible | the common default | | Repeatable Read | prevented | prevented | possible* | stricter | | Serializable | prevented | prevented | prevented | safest, slowest | (Source: the SQL standard's isolation levels, as summarized in the [PostgreSQL docs on transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html).) โš ๏ธ **Gotcha: "default" and the fine print vary by database.** The standard says what each level must *at least* prevent, but vendors differ in defaults and in how strict they actually are. PostgreSQL and Oracle default to Read Committed; MySQL's InnoDB defaults to Repeatable Read. And the asterisk above is real: PostgreSQL's Repeatable Read actually blocks phantoms too (implemented more strictly than the standard requires). The lesson isn't to memorize a grid - it's to **look up your specific database's default and behavior** before you rely on a guarantee. Assuming Serializable when you're running Read Committed is how subtle money bugs are born. You set the level per transaction when you need something stronger than the default: ```sql BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- ...your reads and writes are now protected from all three anomalies... COMMIT; ``` *What just happened:* You turned the dial up to maximum for this one transaction. The database will now ensure the result is as if your transaction had run completely alone - at the cost of more contention, and (as we'll see) a higher chance it gets aborted and asks you to retry. ๐Ÿ“ **Terminology.** "Serializable" means the outcome is equivalent to running the overlapping transactions *one after another in some order* (in series), with no interleaving visible. It's the formal name for the "as if it had the database to itself" promise from Phase 2. ## 3. โš ๏ธ Deadlocks: two transactions waiting on each other forever There's one concurrency hazard that isn't about *reading* the wrong thing - it's about getting *stuck*. It can happen at any isolation level, and it surprises people because nothing they wrote looks wrong. To change a row, a transaction takes a **lock** on it so no one else can change it at the same time. A deadlock happens when two transactions each hold a lock the other one is waiting for - a perfect standoff. Neither can move, because each is waiting for the other to let go first. The classic recipe: two transfers grab the same two rows in *opposite order*. ```mermaid sequenceDiagram participant A as Transaction A (Alice โ†’ Bob) participant B as Transaction B (Bob โ†’ Alice) A->>A: BEGIN, UPDATE alice (locks Alice's row) B->>B: BEGIN, UPDATE bob (locks Bob's row) A-->>B: UPDATE bob โ†’ wants Bob's lock, which B holds. WAIT B-->>A: UPDATE alice โ†’ wants Alice's lock, which A holds. WAIT Note over A,B: each waits for the lock the other holds โ†’ DEADLOCK ``` *What just happened:* A locked Alice and then asked for Bob; B locked Bob and then asked for Alice. Now A is waiting on B and B is waiting on A. Left alone they'd wait forever - so the database steps in. Databases detect deadlocks automatically and break the tie by **killing one of the transactions** (the "victim") and rolling it back, so the other can proceed. The victim's connection gets an error: ```sql -- The losing transaction sees something like: ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321. HINT: See server log for more details. ``` *What just happened:* The database noticed the standoff, picked your transaction as the victim, and rolled it back entirely (atomicity again - nothing partial survives). The *other* transaction completed normally. Your code now has an error to deal with, and nothing it did committed. A deadlock victim error isn't a logic bug - it's the database doing exactly its job, and the fix is almost always to **catch the error and try the transaction again.** The retry usually succeeds, because the conflicting transaction has by now finished and released its locks. ```text try: run the whole transaction (BEGIN โ€ฆ COMMIT) on "deadlock detected": wait a tiny, slightly random moment try the whole transaction again (give up after a few attempts) ``` Two practical notes that prevent most deadlocks in the first place: - **Acquire rows in a consistent order.** The deadlock above happened only because A and B grabbed Alice and Bob in opposite orders. If every transfer always touches the lower account id first, the standoff can't form. - **Keep transactions short** (the Phase 1 rule again). The less time you hold locks, the less window there is to collide. ๐Ÿช– **War story.** A team I know had a nightly batch job that deadlocked against live user traffic a few times a week. The "bug" was that the batch processed rows in insertion order while the API touched them in id order. Adding a single `ORDER BY id` to the batch - so both sides locked rows in the same order - made the deadlocks disappear. No retry logic, no isolation change; just agreeing on an order. ## Tying it back to scaling Everything here is the cost of letting transactions overlap on *one* database. When that database can't keep up and you split data across several machines, these problems get harder, not easier: a transaction needing rows on two different servers can't be protected by a single database's locks and isolation. That's the wall where single-node ACID meets distributed systems - and where the trade-offs in [Scaling a Database](/guides/scaling-a-database) take over. ## Recap 1. Overlapping transactions can cause **dirty reads** (reading uncommitted data), **non-repeatable reads** (a row's value changes mid-transaction), and **phantom reads** (the set of matching rows changes). 2. **Isolation levels** are a dial from Read Uncommitted (fast, unsafe) to Serializable (safe, slow); each blocks more anomalies at more cost. 3. **Defaults and exact behavior differ by database** - look up yours; don't assume. 4. A **deadlock** is two transactions each waiting on a lock the other holds; the database kills one as a victim and rolls it back. 5. Handle deadlocks by **retrying** the transaction; prevent most of them by **locking rows in a consistent order** and keeping transactions short. **Related:** [Querying Basics - SELECT & WHERE](/guides/querying-basics-select-where) ยท [Scaling a Database](/guides/scaling-a-database)