Database Transaction Isolation Levels
Let’s talk about something most of us gloss over until it bites hard: transaction isolation levels. You’ve probably seen settings like READ COMMITTED or SERIALIZABLE in your database docs and thought, “Meh, default is fine.” Spoiler: sometimes default is not fine. Mis‐configured isolation can cause ghost data, phantom rows, and 3 a.m. incident calls—all without a single line of code being “wrong.”
Quick-and-Dirty Primer
Isolation levels are basically the database’s promise about how “alone” your transaction really is while it runs. From most relaxed to most paranoid:
| Level | What It Promises | What Can Still Go Wrong |
|---|---|---|
| READ UNCOMMITTED | You can read anything—even uncommitted changes. | Dirty reads, non-repeatable reads, phantom rows, lost updates. |
| READ COMMITTED (most defaults) | You’ll never see uncommitted data. | Non-repeatable reads, phantom rows. |
| REPEATABLE READ | Rows you read stay the same during the transaction. | Phantom rows (new rows matching your query can appear). |
| SERIALIZABLE | Database behaves as if transactions ran one after another. | Slowdowns, lock contention, and sometimes deadlocks. |
Translation: the lower the level, the faster and more concurrent things are; the higher, the safer (and slower) it gets.
The Horror Story (a.k.a. How We Learned the Hard Way)
We had two services:
- BillingService – sums up daily charges and invoices the customer.
- UsageTracker – logs every API call for analytics.
Both hit the same usage table but used the default READ COMMITTED level in Postgres. Life was good—until it wasn’t.
Bug: Some invoices were off by a few cents (sometimes dollars) in high-traffic hours.
Why?
- BillingService started a transaction, queried
SUM(cost)over the day’s usage. - While it was doing that, UsageTracker sneaked in more rows.
- BillingService committed, thinking the sum was final—but new rows slipped into “today,” so the invoice under-charged.
Classic phantom read.
Fix? We wrapped that billing query in a REPEATABLE READ transaction and retried on serialization errors. In short, we paid a small performance cost to never under-charge again (finance loved us).
Dirty Reads, Non-Repeatable Reads, Phantoms— Oh My!
| Phenomenon | Real-World Analogy |
|---|---|
| Dirty read | You peek at your friend’s unfinished essay and quote from it—even if they later delete half of it. |
| Non-repeatable read | You read a book title once; five minutes later the title magically changes. |
| Phantom read | You count all people in the room, turn around, and new folks have appeared—even though the door was “closed.” |
| Lost update | Two people edit the same doc; whoever hits save last nukes the other person’s changes. |
Most teams discover these only when customers do.
Picking the Right Level (Rule of Thumb)
- READ COMMITTED – Fine for CRUD apps where occasional re-reads are harmless (think blog comments, likes).
- REPEATABLE READ – Great when aggregation accuracy matters (billing, stock counts).
- SERIALIZABLE – Use for money transfers or anything where “almost right” = “totally wrong.” Accept retries.
- READ UNCOMMITTED – Basically “YOLO.” Only for pure analytics on replicas or debugging.
Practical Tips We Now Swear By
- Start low, crank up selectively. Most tables live happily on READ COMMITTED. Raise isolation only where mistakes are painful.
- Automate retries: SERIALIZABLE and REPEATABLE READ can throw serialization errors. Wrap critical operations in retry loops.
- Write tests that simulate concurrency: Use
pg_sleep()or multi-threaded scripts to catch lost updates in CI, not prod. - Monitor lock wait times: High isolation can slow things. Watch
pg_stat_activity(Postgres) or your DB’s equivalent. - Document every isolation change: Future devs shouldn’t ask “why is this SERIALIZABLE?”—they should see the ticket linked in the code comment.
Conclusion
Isolation levels boil down to trust: Do you trust that nobody else will change the data under your feet? The database can’t read minds, so you have to tell it how paranoid to be.
Set it too low, and you risk money (or reputation). Set it too high everywhere, and you risk performance (and still your reputation). The art is in knowing where to tighten the screws.
Have a gnarly isolation bug story? I’d love to hear it—misery loves company, and we all learn faster that way.
Until next time,
Keep Learning. Keep Shipping.
Member discussion