Beyond CRUD: Building Reliable Software Systems · Part 5
New to this series? Start with Part 1
Database Isolation Levels Explained: Choosing the Right Consistency Guarantees
Understanding the Four SQL Isolation Levels
Now that we’ve seen the kinds of problems concurrent transactions can create, the next question is obvious:
How does a database prevent them?
The answer lies in isolation levels.
Rather than enforcing a single set of rules for every application, relational databases allow developers to choose how isolated transactions should be from one another.
This flexibility exists because different applications have different priorities.
A banking application transferring millions of shillings every day values consistency above almost everything else.
A reporting dashboard displaying website traffic may prefer speed over perfect accuracy.
Isolation levels allow the database to balance these competing requirements.
As isolation becomes stronger, transactions observe more consistent data, but the database also has to coordinate more aggressively, often reducing concurrency.
As isolation becomes weaker, transactions execute more freely, improving performance but increasing the likelihood of observing changing data.
The SQL standard defines four isolation levels.
Each one builds upon the guarantees of the previous level.
Read Uncommitted
Read Uncommitted is the weakest isolation level defined by the SQL standard.
At this level, transactions are allowed to read changes made by other transactions even if those changes haven’t yet been committed.
Returning to our banking example, imagine Alice begins transferring KES 20,000 to another account.
The database deducts the money from her balance but hasn’t yet committed the transaction.
Another transaction immediately reads Alice’s account.
Instead of seeing KES 50,000, it now sees KES 30,000, even though the transfer could still fail and be rolled back.
That second transaction has just performed a dirty read.
The advantage of Read Uncommitted is that transactions almost never wait for one another.
Because the database performs very little coordination, throughput can be extremely high.
The downside is that applications may make decisions using data that never officially existed.
For most business applications, this is unacceptable.
Imagine calculating payroll using salaries that are eventually rolled back or approving a loan based on a balance that disappears moments later.
Fortunately, very few modern relational databases actually encourage Read Uncommitted.
Many databases either discourage it entirely or internally behave more conservatively even when it’s requested.
In practice, you’ll rarely choose this isolation level for production systems.
Read Committed
Read Committed is the default isolation level in databases such as PostgreSQL, Oracle, and SQL Server.
Instead of allowing transactions to read uncommitted changes, the database only exposes data that has already been committed.
This immediately eliminates dirty reads.
Returning to Alice’s transfer, suppose another transaction checks her balance while the transfer is still running.
Instead of seeing the temporary balance of KES 30,000, it continues seeing the previously committed balance of KES 50,000 until the transfer completes.
Only after the transaction commits does the new balance become visible.
This makes Read Committed an excellent general-purpose isolation level.
Applications never observe incomplete work, while the database still allows a high degree of concurrency.
However, Read Committed doesn’t solve every problem.
Suppose your transaction reads Alice’s balance at the beginning of a report.
A few seconds later, another transaction deposits KES 100,000 into the account and commits.
If your report queries the balance again before finishing, you’ll now see a different value.
The same row has changed during your transaction.
Read Committed prevents dirty reads, but it still allows non-repeatable reads and phantom reads.
For many applications, that’s a perfectly acceptable trade-off.
Read Committed Timeline
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Transaction A Transaction B
BEGIN
Read Balance = 50,000
BEGIN
Deposit 100,000
COMMIT
Read Balance = 150,000
COMMIT
Both values are valid.
The important difference is that Transaction A never sees incomplete or rolled-back data.
It only observes committed changes.
Repeatable Read
Suppose you’re generating an end-of-day financial report.
Your transaction calculates the total account balance across thousands of customers.
Halfway through generating the report, another transaction updates several account balances.
If your report re-reads those accounts later, the totals may no longer match the values used earlier in the report.
This is exactly the situation Repeatable Read was designed to solve.
At this isolation level, once a transaction reads a row, subsequent reads of that same row always return the same version for the lifetime of the transaction.
Even if another transaction updates the row and commits, your transaction continues working with the original version.
It’s as though your transaction receives its own private snapshot of the database.
This provides a much more consistent view of the data, making it particularly useful for reporting systems and financial calculations.
However, Repeatable Read doesn’t necessarily prevent new rows from appearing that satisfy your query conditions.
Depending on the database implementation, phantom reads may still occur, although databases like PostgreSQL use Multi-Version Concurrency Control (MVCC) to eliminate many of these anomalies without locking every row.
This is one of the reasons database behavior differs slightly across vendors.
We’ll return to that shortly.
Repeatable Read Timeline
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Transaction A Transaction B
BEGIN
Read Balance = 50,000
BEGIN
Deposit 100,000
COMMIT
Read Balance = 50,000 ✅
COMMIT
Although the database now contains KES 150,000, Transaction A continues seeing KES 50,000 because it is working from a consistent snapshot.
Serializable
Serializable is the strongest isolation level defined by the SQL standard.
The easiest way to understand it is to imagine that every transaction runs one after another instead of simultaneously.
Internally, the database may still execute many transactions concurrently, but it guarantees that the final result is identical to some serial execution order.
Returning to our concert ticket example, suppose only one seat remains.
Customer A begins purchasing the ticket.
Customer B attempts to purchase the same seat at exactly the same time.
Under Serializable isolation, the database ensures that only one transaction succeeds.
The other transaction must either wait, retry, or fail.
The database refuses to produce a result that couldn’t happen if the transactions had executed one after another.
This provides the strongest possible consistency guarantees.
It also comes at the highest performance cost.
Serializable transactions often require additional locking, conflict detection, or transaction retries.
For systems processing large volumes of concurrent requests, this can reduce throughput significantly.
Because of this, Serializable is typically reserved for situations where correctness is absolutely critical.
Financial ledgers, securities trading systems, and certain accounting operations are common examples.
Isolation Levels at a Glance
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads | Performance |
|---|---|---|---|---|
| Read Uncommitted | ❌ Possible | ❌ Possible | ❌ Possible | ⭐⭐⭐⭐⭐ |
| Read Committed | ✅ Prevented | ❌ Possible | ❌ Possible | ⭐⭐⭐⭐ |
| Repeatable Read | ✅ Prevented | ✅ Prevented | ⚠ Depends on database | ⭐⭐⭐ |
| Serializable | ✅ Prevented | ✅ Prevented | ✅ Prevented | ⭐⭐ |
The table makes an important pattern clear.
As isolation increases, the number of concurrency anomalies decreases.
At the same time, the amount of coordination required by the database increases.
This is why there is no universally “best” isolation level.
The appropriate choice always depends on the requirements of your application.
Database Isolation Levels in Popular Databases
One detail that often surprises developers is that not every relational database implements isolation levels in exactly the same way.
The SQL standard defines the four isolation levels, but database vendors have some flexibility in how they achieve those guarantees.
For example, PostgreSQL relies heavily on Multi-Version Concurrency Control (MVCC). Instead of locking rows aggressively, PostgreSQL keeps multiple versions of a row and allows transactions to read a consistent snapshot of the data. This approach provides excellent concurrency while maintaining strong consistency.
MySQL’s InnoDB storage engine also supports MVCC but implements certain isolation behaviors differently. In particular, its default Repeatable Read isolation level prevents many phantom reads by using a combination of snapshot reads and gap locks.
SQL Server, on the other hand, traditionally relies more heavily on locking, although it also offers snapshot-based isolation levels that can be enabled when appropriate.
As a developer, you don’t need to memorize every implementation detail.
The important lesson is this:
Always understand how your specific database implements isolation before assuming its behavior.
The SQL standard provides the vocabulary, but your database documentation explains the exact behavior.
Choosing the Right Isolation Level
After learning about all four isolation levels, it’s natural to ask:
“Which one should I actually use?”
The honest answer is:
It depends on what you’re building.
Suppose you’re developing a dashboard that displays the number of users currently online.
If the number changes while someone refreshes the page, that’s perfectly acceptable.
There’s little value in sacrificing performance just to ensure every count remains identical throughout a transaction.
Read Committed is usually more than sufficient.
Now consider a payroll system.
Calculating employee salaries requires reading thousands of records while ensuring the figures don’t change halfway through the calculation.
If one employee’s salary is updated while payroll is being processed, the final report could contain inconsistent totals.
Repeatable Read becomes a much better fit because it provides a stable snapshot throughout the transaction.
Finally, imagine a securities trading platform or a banking ledger where even a single inconsistency could have significant financial consequences.
Here, correctness is more important than throughput.
Serializable isolation is often the safest choice, even if it means transactions occasionally wait or retry.
The goal isn’t to choose the strongest isolation level.
The goal is to choose the weakest isolation level that still guarantees the correctness your application requires.
Doing so allows the database to maximize concurrency without sacrificing business integrity.
Isolation Levels and Performance
One mistake developers sometimes make is assuming higher isolation is always better.
In reality, every additional guarantee comes at a cost.
Higher isolation levels typically require the database to:
- Coordinate more transactions.
- Acquire additional locks or maintain more snapshots.
- Detect conflicts more aggressively.
- Delay or retry conflicting transactions.
As concurrency increases, these costs become more noticeable.
A high-traffic e-commerce platform processing thousands of orders every minute cannot afford unnecessary waiting if a lower isolation level already satisfies its business rules.
Likewise, a financial institution cannot sacrifice correctness simply to process a few extra transactions per second.
Finding the right balance is part of designing reliable software.
Isolation Levels vs Transactions vs Race Conditions
At this point in the series, we’ve covered three concepts that are closely related but often confused.
Let’s put them side by side.
Transactions
Transactions answer the question:
What happens if my operation fails halfway through?
They ensure a group of related database operations either all succeed together or all fail together.
Without transactions, partial updates can leave your data inconsistent.
Race Conditions
Race conditions answer a different question:
What happens if two requests modify the same data at the same time?
These problems arise because multiple users or systems interact with shared data concurrently.
The outcome often depends entirely on timing.
Transactions alone don’t eliminate race conditions.
Additional mechanisms such as locking, optimistic concurrency, or stronger isolation levels are often required.
Isolation Levels
Isolation levels answer yet another question:
While another transaction is running, what am I allowed to see?
Should your transaction observe unfinished work?
Should it continue seeing the same data even after another transaction commits?
Should it behave as though it’s the only transaction running?
Isolation levels define these rules.
Together, these three concepts form the foundation of reliable database applications.
They complement one another rather than compete.
A payment system, for example, might use:
- Idempotency to prevent duplicate payment requests.
- Transactions to ensure payment records and account balances remain synchronized.
- Read Committed or Serializable isolation to guarantee consistent reads.
- Locking to prevent concurrent modifications of the same account.
No single technique solves every reliability problem.
Reliable systems combine several techniques, each addressing a different class of failure.
Practical Advice for Backend Developers
If you’re just beginning your backend engineering journey, don’t feel pressured to master every isolation level immediately.
Instead, focus on developing the habit of asking the right questions whenever you design a feature.
For example:
- Could another user modify this data while I’m reading it?
- If the same query runs twice, should it return the same result?
- What happens if another transaction inserts new rows before mine finishes?
- Is perfect consistency necessary, or is slightly stale data acceptable?
- Would optimistic concurrency or explicit locking be a better solution?
Thinking through these questions early in the design process often prevents bugs that are incredibly difficult to diagnose later in production.
Many concurrency issues aren’t caused by writing incorrect code.
They’re caused by making incorrect assumptions about how multiple users interact with the same data simultaneously.
Final Thoughts
Database isolation levels are often presented as a collection of definitions that developers are expected to memorize.
In reality, they’re much simpler than they first appear.
They’re simply different answers to one question:
How much should one transaction be allowed to observe while another transaction is still working?
Lower isolation levels prioritize concurrency, allowing more users to interact with the database simultaneously.
Higher isolation levels prioritize consistency, ensuring every transaction sees a predictable view of the data.
Neither approach is universally correct.
The right choice depends entirely on the problem you’re solving.
As your applications grow, understanding isolation levels becomes increasingly important because concurrency is no longer the exception—it’s the norm.
Every online store, banking application, inventory system, booking platform, and loan management system eventually reaches a point where multiple transactions compete for the same data.
The developers who understand isolation levels don’t simply build applications that work.
They build applications that continue working correctly under real-world load.
What’s Next?
In this series we’ve explored:
- Idempotency
- Race Conditions
- Database Transactions
- Database Isolation Levels
We’ve learned how to protect our systems from duplicate requests, concurrent updates, partial failures, and inconsistent reads.
But one challenge still remains.
Everything we’ve discussed assumes our application is running on a single database.
What happens when your application is running on ten servers, each processing requests simultaneously?
A normal database lock isn’t always enough.
In the next article, we’ll explore Distributed Locks Explained: Coordinating Work Across Multiple Servers, where we’ll see how systems like Redis, ZooKeeper, and etcd help ensure that only one application instance performs a critical operation at a time.