Understanding Transaction Isolation Levels

The Spectrum of Isolation: Consistency vs. Performance

When multiple transactions try to read and write the same data at the same time, the database has to make a choice. It can be very strict, making transactions wait for each other to ensure that no transaction ever sees the partial work of another. This provides perfect consistency but can slow down your application, as concurrency is limited.

Alternatively, it can be more lenient, allowing transactions to run more freely. This improves performance and throughput but opens the door to the concurrency anomalies we discussed previously.

The standard SQL isolation levels are the "dials" that let you control this trade-off. They are, from least to most strict:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

Let's explore each one, looking at the problems they solve and the ones they still permit.

A Refresher on Concurrency Anomalies

Before we dive into the levels, let's quickly review the main types of undesirable behaviors, or anomalies, that can occur. A great resource for this is the article "Exploring Database Isolation Levels".

Now, let's see how each isolation level deals with these issues.

Level 1: Read Uncommitted

This is the most lenient isolation level.

• Guarantee: Almost none. A transaction operating at this level can see changes made by other transactions even before they are committed. This is known as a Dirty Read.
• Anomalies Prevented: Dirty Writes. Most databases prevent this anyway, where one uncommitted transaction overwrites the changes of another uncommitted transaction.
• Anomalies Allowed: Dirty Reads, Non-repeatable Reads, Phantom Reads.
• Trade-off: It offers the highest possible concurrency because reads never have to wait for write locks to be released. However, the data it reads can be rolled back at any moment, making it highly unreliable.
• Use Case: Very rare. It might be acceptable for approximate, non-critical analytics where performance is paramount and slight inaccuracies are tolerable (e.g., counting a large number of rows in real-time).

Because it's so permissive, most databases (including PostgreSQL) don't even implement a true Read Uncommitted level or simply upgrade it to Read Committed.

Level 2: Read Committed

This is a significant step up in safety and is the default isolation level for many popular databases, including PostgreSQL and Oracle.

• Guarantee: A transaction will only ever see data that has been successfully committed. It will never see the intermediate, "dirty" work of an in-flight transaction.
• Anomalies Prevented: Dirty Reads.
• Anomalies Allowed: Non-repeatable Reads and Phantom Reads.
• How it works: Imagine your transaction executes a SELECT query. It will only see data that was committed before the query began. If another transaction commits a change and you run the same query again within your transaction, you will see the new, updated value. This leads to the "non-repeatable read" anomaly.

Let's see this in action. The following video provides a fantastic, practical demonstration using a PostgreSQL terminal.

Why is this a problem? As the video demonstrates, if you first read a set of values and then perform a calculation based on them later in the same transaction, you might get an inconsistent result if another transaction changed those values in between your two operations.

This is the trade-off: Read Committed prevents you from reading garbage data, but it doesn't guarantee that the data will stay the same for the duration of your transaction.

Level 3: Repeatable Read

This level provides a stronger guarantee, as required by databases like MySQL (which uses it as the default).

• Guarantee: If a transaction reads a row, it will see the exact same data for that row if it reads it again later within the same transaction. This is achieved by having the transaction operate on a "snapshot" of the database as it existed when the transaction began.
• Anomalies Prevented: Dirty Reads, Non-repeatable Reads.
• Anomalies Allowed: Phantom Reads.
• How it works: When a Repeatable Read transaction starts, it's as if it takes a picture of the entire database. All of its reads for the rest of its duration will be served from this consistent snapshot. It will not see any changes committed by other transactions after it started. However, if its query uses a WHERE clause (a predicate), another transaction could insert a new row that matches that clause. If our transaction runs its query again, that new "phantom" row will appear.

Let's watch the next part of the video to see a demonstration of this snapshot behavior.

Under the Hood with MVCC

Your goal is to understand the backend at the deepest level. So, how does a database implement this "snapshot" idea without just locking everything? Many modern databases use Multi-Version Concurrency Control (MVCC).

Instead of overwriting data, the database keeps multiple versions of a row. Each version is tagged with the transaction ID that created it. When your Repeatable Read transaction starts, the database notes which other transactions are currently active. Its visibility rule then becomes: "Show me the latest version of a row that was committed before my transaction started, and ignore any versions created by transactions that were active when I started."

The article "Implementing MVCC and major SQL transaction isolation levels" shows this logic in code. While you don't need to know Go, looking at the structure of the isvisible function for RepeatableReadIsolation makes this abstract rule concrete. It checks that the data's transaction ID (txStartId) is not from a concurrent or future transaction.

Level 4: Serializable

This is the strictest isolation level, offering the strongest guarantees.

• Guarantee: The effect of running transactions concurrently is identical to running them one after another (in some serial order). The database ensures that no concurrency anomalies of any kind can occur.
• Anomalies Prevented: Dirty Reads, Non-repeatable Reads, Phantom Reads, and more subtle anomalies like Write Skew.
• How it works: The database enforces rules that prevent any situation that could not have occurred if the transactions ran sequentially. If it detects a potential conflict (a "dangerous structure" in the sequence of operations), it will force one of the transactions to abort and retry.
• Trade-off: This is the safest level, but it comes at the highest performance cost due to the complex detection mechanisms and the higher likelihood of transaction rollbacks.

Let's see what happens when we try to create a conflict at this level.

The 'Subtle' Bugs Serializable Prevents: Write Skew

Repeatable Read seems very safe, so why would you ever need Serializable? It's to prevent subtle bugs that Repeatable Read still allows. The classic example is a write skew.

The "Exploring Database Isolation Levels" article has a perfect example of this with an on-call doctor scheduling system.

This is a powerful example of why the strictest isolation level is sometimes necessary. Even though the two transactions didn't update the same row, their actions combined led to an inconsistent state. Only Serializable isolation is guaranteed to detect and prevent this.

Summary: A Visual Guide

The following image provides an excellent summary table that connects the isolation levels to the anomalies they prevent and the locking strategies often used.

Conclusion

You've now explored the spectrum of transaction isolation, from the wild west of Read Uncommitted to the fortress of Serializable. For you as a backend developer using a framework like Django, this knowledge is not just academic. When you write code that interacts with the database, the default isolation level of that database (e.g., Read Committed for PostgreSQL) dictates the subtle race conditions your application is or isn't protected from.

Key Takeaways:

• Isolation is a Trade-off: You are constantly balancing the need for data correctness against the need for application performance and concurrency.
• Read Committed is the Common Default: It prevents reading uncommitted data and offers good performance, but doesn't protect against data changing mid-transaction.
• Repeatable Read Provides a Snapshot: It guarantees that reads are stable within a transaction, preventing non-repeatable reads, but can still be vulnerable to phantom reads.
• Serializable is the Gold Standard for Safety: It makes concurrent transactions behave as if they were executed in a sequence, preventing all anomalies, but at the highest performance cost.
• Know Your Default: Always be aware of the default isolation level of the database you are using, as it defines the baseline guarantees for all your transactions.

Preview of the Next Lesson:

We've just spent two lessons on the intricate work involved in executing a single transaction correctly. But a real-world server handles thousands of requests per second. How does an application efficiently manage all the connections needed to execute these transactions? In the next lesson, we will answer that by exploring connection pooling: what it is, why it's absolutely critical for backend performance, and how it works.