Transactions and Isolation Levels in Application Code

Intermediate · 8 min

Where to actually put BEGIN/COMMIT/ROLLBACK in your code, what isolation levels prevent, and the deadlock-and-retry reality of using them.

Before thisORMs, Query Builders, and Raw SQL

mkdev · 8 min

Transactions and Isolation Levels in Application Code

The player loads from youtube-nocookie.com when you press play. Nothing reaches YouTube before that.

Taught by mkdevReport a problem with this video
Not started
Set this lesson's status

What you'll learn

  • A transaction boundary should wrap exactly one logical unit of work — everything that must succeed or fail together — and nothing more, which usually means it's drawn around a single request's database calls, not the whole request.
  • Each isolation level is defined by which specific anomaly it prevents — dirty reads, non-repeatable reads, or phantom reads — not by a vague notion of 'more safety,' and the right level depends on which of those anomalies your code can actually tolerate.
  • Stronger isolation doesn't just cost performance in the abstract — it increases the odds of two transactions deadlocking or one getting rolled back by the database, so production code using serializable transactions has to be written to retry.
  • The theory behind these same four words — ACID, isolation — gets a distributed-systems treatment elsewhere; this lesson is about the call you make in your own application code, one request at a time.

Why a single SQL statement isn’t enough

Most individual SQL statements are already atomic — one UPDATE either happens or it doesn’t. The problem transactions solve shows up the moment an operation needs more than one statement to be correct. Transferring money between two accounts means debiting one row and crediting another; if your process crashes between the two statements, you’ve either destroyed money or created it. Placing an order means inserting the order row, decrementing inventory, and recording the payment — if any one of those three fails, you don’t want the other two to have happened.

A transaction (BEGIN … COMMIT or ROLLBACK) groups a set of statements so the database guarantees all of them happen or none of them do. That’s the “atomicity” piece. This lesson is about the practical side of using that mechanism in your own application code: where you actually draw the boundary, and what the isolation level you pick actually changes about how concurrent transactions see each other’s work.

Where to draw the boundary

The rule of thumb is: a transaction should wrap exactly the set of operations that need to succeed or fail together as a unit — no more, no less. In a typical web backend, that’s usually one request, or one well-defined operation within a request, not the entire request-handling function and definitely not a whole background job with several unrelated stages bundled in.

Two mistakes show up constantly. The first is a transaction that’s too narrow — wrapping only the final write and leaving an earlier read unprotected, so a race condition sneaks in between the check and the write (a classic case: checking inventory is available, then updating it, as two separate transactions — another request can slip in between and both think they reserved the last item). The second is a transaction that’s too wide — wrapping an entire request handler, including a slow external API call or an email send, inside the database transaction. That holds database locks and keeps rows uncommitted for however long that external call takes, which is exactly the kind of thing that causes contention and timeouts under real load. The fix in both directions is the same: identify the actual unit of work that needs all-or-nothing behavior, and draw the transaction boundary around precisely that, with any slow, non-database work happening before the transaction starts or after it commits.

What isolation levels actually prevent

Transactions running concurrently can interfere with each other in specific, well-named ways, and each standard isolation level is defined by which of these it rules out:

  • Dirty read — transaction A reads a row that transaction B has changed but not yet committed. If B then rolls back, A acted on data that never really existed.
  • Non-repeatable read — transaction A reads a row, transaction B commits a change to that same row, and A reads it again within the same transaction and gets a different value.
  • Phantom read — transaction A runs a query with a WHERE condition, transaction B inserts or deletes a row matching that condition and commits, and A re-runs the same query and gets a different set of rows than before.

The four SQL-standard isolation levels map directly onto which of these they block: read uncommitted blocks none of them (and most production databases don’t even really implement it as written); read committed blocks dirty reads, which is why it’s the default in Postgres and several other databases; repeatable read additionally blocks non-repeatable reads; and serializable blocks all three, behaving as if every transaction ran one after another with no overlap at all. The video embedded above demonstrates several of these directly against Postgres, which is one of the more instructive ways to actually see the difference rather than just read about it.

Picking a level is about matching it to what your code can tolerate being wrong. A page that just displays a dashboard number can usually live with read committed. A process that reads a balance, computes a new value, and writes it back — where seeing stale data between the read and the write would produce a wrong result — needs at least repeatable read, or needs to be restructured to use an atomic UPDATE ... SET balance = balance - x instead of a read-then-write round trip.

The deadlock and retry reality

Stronger isolation isn’t free, and the cost isn’t only “it’s slower.” It’s that the database has to do more work to detect when two transactions’ views of the world have genuinely conflicted, and its only options when that happens are to make one of them wait or make one of them fail. A deadlock — two transactions each holding a lock the other one needs — ends with the database picking one as the “victim” and aborting it. Serializable isolation specifically can abort a transaction purely because it detected a conflict with another concurrent transaction, even if neither one did anything individually wrong.

This means any application code using repeatable read or serializable isolation under real concurrency has to be written expecting that some transactions will fail with a retryable error, and retrying them — usually with a short backoff — is normal, correct behavior, not a bug to chase down. Code that doesn’t retry, and instead surfaces that failure straight to the user as a 500 error, is usually the actual bug.

What this lesson doesn’t cover

This is the application-code version of transactions: where you call db.transaction(), which isolation level you set, and what happens when two requests collide inside your own service. The theoretical and distributed-scale version of the same ground — what ACID actually guarantees, and how (or whether) it holds once a transaction has to span multiple machines — belongs to system design, and it’s covered in ACID transactions and the CAP theorem. That lesson is the “why” behind these words; this one is the “where do I actually put this in my code.”

Check yourself

  1. 01Why should a transaction boundary typically wrap one request's worth of database work rather than, say, an entire background job with several unrelated steps?

    A transaction holds locks and keeps work uncommitted until it ends, so wrapping unrelated steps in one transaction keeps resources locked far longer than necessary and means an unrelated failure later in the job rolls back work that had nothing to do with it. The boundary should match the actual unit of work that needs all-or-nothing behavior, not just everything that happens to run together.

  2. 02What specific problem does a dirty read describe, and which isolation level is the first one that prevents it?

    A dirty read is seeing a row's uncommitted change from another transaction that might still be rolled back — so you've acted on data that technically never existed. Read committed is the first standard level that prevents this: it only ever shows you data from transactions that have already committed.

  3. 03What's the difference between a non-repeatable read and a phantom read?

    A non-repeatable read is re-reading the same row within one transaction and getting a different value because another transaction updated it in between; a phantom read is re-running the same query and getting a different set of rows because another transaction inserted or deleted rows that match your query's condition — one is about a row changing under you, the other is about the result set's membership changing.

  4. 04Why does using serializable isolation mean your application code needs a retry loop around its transactions?

    Serializable isolation is enforced by the database detecting conflicts between concurrent transactions and aborting one of them rather than letting them interleave unsafely, so any code running at that level has to expect transactions to fail with a serialization error under contention and retry them, not treat that failure as an unexpected bug.

  5. 05How does this lesson's scope differ from the CAP-theorem treatment of ACID transactions in the system-design roadmap?

    This lesson is about where you actually call db.transaction() in your own request-handling code and which isolation level you pick for it; the system-design lesson is about how those same guarantees hold up — or don't — once a single logical transaction spans multiple machines or a distributed data store, which is a different problem at a different scale.

Go deeper

Notes

Private to you

Esc
↑↓ navigate↵ open? shortcuts

Keyboard shortcuts

Press Esc to close

Anywhere

⌘K
Search and commands
/
Search
?
This sheet
Esc
Close

Reading a lesson

J
Next lesson
K
Previous lesson
B
Bookmark