Beyond CRUD: Building Reliable Software Systems · Part 4
New to this series? Start with Part 1
Database Isolation Levels Explained: Why Two Transactions Can See Different Data
“Transactions guarantee that your work completes correctly. Isolation levels determine what everyone else is allowed to see while that work is happening.”
In the previous article, we explored database transactions and learned how they ensure multiple database operations either succeed together or fail together. Transactions protect applications from partial execution, preventing situations where money is deducted from one account without being deposited into another or where inventory is reduced without successfully creating an order.
But transactions solve only part of the problem.
Modern applications rarely have just one user interacting with the database at a time. Thousands of customers may be placing orders, updating records, making payments, or querying reports simultaneously. Each of these actions runs inside its own transaction, and more often than not, several transactions are accessing the same data at exactly the same time.
This raises an important question:
What should one transaction be allowed to see while another transaction is still running?
Imagine opening your banking application to check your account balance.
At the exact same moment, your employer’s payroll system is depositing your monthly salary into the same account.
Should your transaction see the new balance immediately?
Should it continue seeing the old balance until the salary transaction finishes?
Should it wait until the payroll transaction completes before showing you anything at all?
Each answer is technically valid depending on how the database is configured.
Now imagine an online store where only one laptop remains in stock.
Customer A begins placing an order.
Before their transaction finishes, Customer B checks the product page.
Should Customer B still see one laptop available?
Should they see zero?
Should they wait until Customer A’s purchase either succeeds or fails?
Again, the answer depends on the database’s isolation level.
Isolation levels define the rules governing how concurrent transactions interact with one another. They determine whether one transaction can observe another transaction’s work before it has been completed, whether repeated reads always return the same result, and whether new rows appearing during a transaction should be visible immediately.
Although isolation levels are often introduced as an advanced database topic, they’re really about one simple idea:
How much of another transaction’s work should your transaction be allowed to see?
The answer has significant consequences for both correctness and performance.
In this article, we’ll explore why isolation levels exist, the concurrency problems they solve, the four SQL standard isolation levels, and how to choose the right one for your application.
Why Isolation Exists
To understand isolation, imagine you’re reading a book in a library.
Halfway through chapter three, someone walks over, quietly replaces several pages with new ones, and walks away.
You continue reading without realizing anything changed.
The beginning of the chapter describes one story.
The ending describes another.
Nothing makes sense.
Databases can experience a remarkably similar problem.
When multiple transactions execute simultaneously, each transaction may be reading data while another transaction is actively changing it.
Without rules governing these interactions, applications could make decisions based on incomplete information, outdated values, or data that is eventually discarded.
Isolation exists to prevent these situations.
Rather than allowing every transaction unrestricted access to every change happening in the database, the database controls what each transaction can observe and when it can observe it.
Think of it as putting walls between transactions.
Some walls are very thin.
Transactions can see almost everything happening around them.
Other walls are much thicker.
Transactions operate almost as though they’re the only users of the database.
The thicker the wall, the more isolated the transaction becomes.
The Trade-Off Between Consistency and Performance
At first glance, it might seem obvious that every database should simply use the highest possible isolation level.
After all, if stronger isolation produces more consistent data, why wouldn’t every system choose it?
The answer lies in performance.
Imagine a supermarket with only one checkout counter.
Every customer waits patiently in line.
Because only one cashier is serving customers, inventory updates happen one at a time.
Mistakes are rare.
Unfortunately, the queue becomes enormous.
Now imagine opening ten checkout counters.
Customers move much faster.
However, all ten cashiers are now updating the same inventory system simultaneously.
Keeping everything synchronized becomes much more difficult.
Databases face exactly the same challenge.
Higher isolation levels provide stronger guarantees about data consistency, but they often require additional locking, coordination, and waiting.
Lower isolation levels allow more transactions to execute concurrently, increasing throughput and reducing latency, but they also increase the likelihood that transactions observe changing data.
Isolation levels are therefore a balancing act between two competing goals:
- Consistency, ensuring every transaction sees predictable and reliable data.
- Concurrency, allowing as many users as possible to interact with the system simultaneously.
Different applications make different choices.
A banking application processing financial transfers typically prioritizes correctness over raw performance.
An analytics dashboard generating sales reports might tolerate slightly older data if it means thousands of users can run reports simultaneously without slowing the system.
Neither approach is universally correct.
The appropriate isolation level depends entirely on your business requirements.
Concurrency Anomalies: The Problems Isolation Levels Exist to Solve
Isolation levels were not invented simply to make databases more complicated.
They exist because concurrent transactions can produce behaviors that most developers would consider surprising—or even dangerous.
These unexpected behaviors are collectively known as concurrency anomalies.
Every isolation level is essentially a trade-off between preventing these anomalies and maintaining good performance.
Before discussing the isolation levels themselves, it’s important to understand the problems they are designed to solve.
The four anomalies you’ll encounter most often are:
- Dirty Reads
- Non-Repeatable Reads
- Phantom Reads
- Lost Updates
Each represents a different way concurrent transactions can interfere with one another.
Let’s begin with the simplest.
Dirty Reads
Imagine Alice has KES 50,000 in her account.
She initiates a transfer of KES 20,000 to another account.
The banking system begins processing the transaction.
The first step deducts the money from Alice’s balance.
Before the transaction finishes, another process—perhaps an ATM balance inquiry or an online banking session—checks Alice’s account.
At that moment, it sees a balance of KES 30,000.
Everything seems perfectly normal.
Then something unexpected happens.
The transfer fails because the destination account no longer exists.
The database rolls back the transaction.
Alice’s balance immediately returns to KES 50,000.
The second transaction has now made a decision based on information that never officially existed.
It observed data that was eventually discarded.
This is known as a Dirty Read.
A dirty read occurs when one transaction reads data written by another transaction before that transaction has been committed.
The easiest way to understand it is to imagine reading someone’s unfinished draft before they’ve decided whether to keep or delete it.
The version you read may never become the final version.
Making business decisions based on that draft could lead to incorrect outcomes.
Fortunately, most modern relational databases prevent dirty reads by default because they are rarely desirable in business applications.
The SQL standard still defines them because they help explain the spectrum of isolation levels.
Timeline of a Dirty Read
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Transaction A Transaction B
BEGIN
Balance = 50,000
↓
Update Balance = 30,000
Read Balance = 30,000 ❌
↓
Transfer Fails
↓
ROLLBACK
Balance returns to 50,000
Transaction B has observed a value that disappeared moments later.
From the perspective of the database, that balance never officially existed.
Yet another transaction already acted as though it did.
This is precisely the type of inconsistency isolation levels are designed to prevent.
Non-Repeatable Reads
Suppose you’re building an online banking application.
A customer opens the app and views their account balance. At that moment, the database reports a balance of KES 50,000.
The customer decides to transfer KES 40,000 to another account, but before confirming the transfer, the application performs one final balance check to ensure sufficient funds are still available.
This seems like a perfectly reasonable workflow.
However, between the first balance check and the second, another transaction deposits KES 100,000 into the same account.
When the application performs the second query, the balance is no longer KES 50,000.
It’s now KES 150,000.
Nothing is technically wrong.
The second transaction committed successfully.
The balance genuinely changed.
The surprising part is that the same transaction read the same row twice and received two different answers.
This phenomenon is known as a Non-Repeatable Read.
Unlike a dirty read, the second transaction isn’t reading uncommitted data. Every value it sees has been permanently committed to the database.
The inconsistency comes from the fact that another transaction modified the row while the first transaction was still running.
Imagine reading yesterday’s newspaper while someone keeps replacing pages with today’s edition.
The information isn’t incorrect.
It’s simply inconsistent because the document changed while you were reading it.
For many applications, this isn’t a problem.
If you’re refreshing a weather dashboard or checking the number of users currently online, it’s perfectly acceptable for values to change between two queries.
However, systems that rely on a stable snapshot of data—such as financial reporting, payroll processing, or end-of-day reconciliation—often require the same query to return the same result throughout the entire transaction.
Timeline of a Non-Repeatable Read
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
Transaction A never modified the balance itself.
It simply asked the same question twice and received two different answers because another committed transaction changed the underlying data in the meantime.
Real-World Example: Updating a Customer Profile
Consider an insurance application where a customer service representative opens a customer’s profile.
The representative spends several minutes reviewing the information before approving a policy update.
Meanwhile, another employee updates the customer’s phone number and address.
When the representative finally clicks Save, the application may now be working with information that is different from what was originally displayed.
Depending on how the application handles these changes, it could accidentally overwrite newer data or make decisions using outdated information.
This isn’t a database bug.
It’s simply the natural consequence of multiple users interacting with the same record at the same time.
Applications that require users to work with a consistent view of the data often use higher isolation levels or optimistic concurrency controls to detect these situations before committing changes.
Phantom Reads
Now let’s consider a different scenario.
Instead of reading a single row twice, imagine you’re querying an entire collection of rows.
Suppose you’re generating a report showing all loan applications submitted today.
Your first query returns:
1
2
3
4
5
6
7
8
9
10
11
Loan Applications Submitted Today
-------------------------------
Loan #101
Loan #102
Loan #103
Total: 3
While your report is still running, another loan application is submitted and committed to the database.
A few moments later, your transaction performs the exact same query again.
This time the results look different.
1
2
3
4
5
6
7
8
9
10
11
12
13
Loan Applications Submitted Today
-------------------------------
Loan #101
Loan #102
Loan #103
Loan #104
Total: 4
Notice what changed.
None of the existing rows were modified.
Instead, an entirely new row appeared.
This is called a Phantom Read.
A phantom read occurs when the same query returns a different set of rows because another transaction inserted, updated, or deleted records that match the query’s search criteria.
Think of it like counting the number of people in a room.
You count 20 people.
While you’re writing the number down, someone walks into the room.
You count again.
Now there are 21 people.
Nothing about the original twenty people changed.
The difference is that a new “phantom” appeared between your two observations.
Timeline of a Phantom Read
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Transaction A Transaction B
BEGIN
SELECT *
WHERE loan_date = TODAY
Returns 3 rows
BEGIN
INSERT Loan #104
COMMIT
SELECT *
WHERE loan_date = TODAY
Returns 4 rows ❌
COMMIT
Unlike a non-repeatable read, where an existing row changes, phantom reads involve the appearance or disappearance of entire rows.
Why Phantom Reads Matter
Imagine you’re calculating today’s total revenue for financial reporting.
Your reporting transaction begins at 5:00 PM and starts aggregating sales.
While it’s still processing, new sales continue being recorded.
Different parts of the report may now be working with different datasets.
The total revenue calculated on page one might not match the detailed transaction list generated on page five because new rows appeared while the report was still executing.
In reporting systems, this can produce confusing and inconsistent results.
Higher isolation levels solve this problem by ensuring the transaction sees a consistent snapshot of the data throughout its lifetime, even if other transactions continue inserting new rows.
Lost Updates
The final concurrency anomaly is perhaps the most dangerous because it silently discards valid work.
Imagine two warehouse employees looking at the same inventory record.
The system currently shows:
1
Laptop Stock = 10
Employee A sells one laptop.
Employee B also sells one laptop at almost exactly the same time.
Both employees read the current stock before making their update.
Each calculates the new quantity as:
1
10 - 1 = 9
Employee A saves.
The inventory becomes:
1
9
A fraction of a second later, Employee B saves.
The inventory is still:
1
9
One of the updates has effectively disappeared.
The correct inventory should now be 8, but because both transactions started from the same original value, one update overwrote the other.
This is known as a Lost Update.
Unlike the previous anomalies, nothing appears obviously wrong.
No errors occur.
No constraints are violated.
The database happily accepts both updates.
The problem is that one user’s work has unintentionally replaced another’s.
Lost updates are one of the primary reasons databases provide row locking, optimistic concurrency control, and stronger isolation levels.
Without these protections, applications that receive many simultaneous updates—such as inventory systems, banking platforms, or booking applications—can slowly drift away from reality without anyone noticing.
Now that we understand the problems, the next question is obvious: How do databases prevent them? That’s exactly what we’ll cover in Part 2.”