Why Transactions Don’t Eliminate Race Conditions: A Guide to Database Isolation Levels

If you work with relational databases, you’ve likely relied on transactions to ensure data consistency through ACID properties — especially atomicity, which guarantees that operations either complete entirely or fail completely. However, many developers assume that wrapping operations in transactions eliminates all concurrency issues, which is a dangerous misconception. While transactions provide atomicity and durability, the isolation level you choose determines how your application handles concurrent access, and different isolation levels can still permit various types of race conditions. Understanding these nuances is crucial for building robust, concurrent applications that maintain data integrity under load, and this article will explore how different isolation levels impact concurrency and what race conditions can still occur even within transactional boundaries.

AI generated image

By the way, if you often get frustrated with blocked websites or slow streaming, I’ve been using NordVPN lately, and it’s been smooth and reliable. I can access any site, stream without limits, and feel a lot safer online.

Right now, there’s a special offer — 70% OFF + 3 extra months + 1TB cloud storage. If you’ve been considering a VPN, this might be worth checking out.

👉 **Check the Offer Here**

A Quick Refresher on Transactions

At its core, a database transaction allows you to bundle multiple SQL statements into a single, atomic unit of work. This means that either all statements within the transaction succeed, or if any part fails, the entire transaction is rolled back, leaving the database unchanged. This property is known as Atomicity, one of the four key ACID properties.

Transactions are crucial for maintaining data integrity, especially when an action requires updates across multiple related tables. For example, if you’re transferring money from one bank account to another, you must ensure that a debit from one account and a credit to the other both happen successfully. If one operation fails, the entire transfer must be undone to prevent data inconsistencies.

While atomicity guarantees that a transaction’s operations are all-or-nothing, it’s the Isolation property that handles what happens when multiple transactions run at the same time. The isolation level of a transaction dictates how and when it can see changes made by other concurrent transactions. The isolation level is the key setting that determines which types of concurrency issues, or race conditions, a transaction can prevent.

The strongest and most secure isolation level is **SERIALIZABLE**. This level ensures that concurrent transactions behave as if they were executed in a sequential, non-overlapping manner, effectively preventing all common race conditions. While it offers the highest degree of data integrity, it often comes with a performance cost. For this reason, many applications use a lower, less restrictive isolation level that offers a better balance between consistency and performance. Understanding these different levels is essential for building robust applications.

Transaction Isolation Levels

Read Committed

The READ COMMITTED isolation level is the most common default setting for many databases (like PostgreSQL and Oracle). As the name suggests, a transaction running at this level is only allowed to see data that has already been committed by other transactions. This simple rule prevents one type of race condition: the dirty read.

A dirty read occurs when a transaction reads data that has been written by another, still-running transaction. If that second transaction later rolls back, the data you read would be “dirty” or invalid, and your work would be based on a non-existent state. By preventing this, READ COMMITTED ensures you only ever see finalized data.

However, READ COMMITTED does not prevent more complex race conditions because it offers only limited isolation. The biggest issue is that different SELECT queries within a single transaction can return different results. This is known as a non-repeatable read.

The Problem: Non-Repeatable Reads

Imagine a simple scenario where your transaction reads the same row multiple times:

Transaction 1: SELECT name FROM user WHERE id = 1; // Returns "Bob"
Transaction 2 (concurrently): UPDATE user SET name = 'Alice' WHERE id = 1; // COMMIT
Transaction 1: SELECT name FROM user WHERE id = 1; // Now returns "Alice"

Within the same transaction, you’ve read two different values for the same row. While this might seem harmless, it can lead to bigger problems, like the Lost Update race condition.

The Lost Update Race Condition

This is a classic problem that happens when two transactions read the same data, modify it, and then one of the updates “loses” by overwriting the other.

Consider a bidding system for an item, where the current bid is 100.

At t4, Transaction 1 tries to update the bid to 200 based on its outdated read from t1, completely overwriting the new bid of 200 from Transaction 2. The final bid is 200, but it should have been 300 (Person A's 100 increase + Person B's 100 increase, or a logical combination). This is a lost update.

Fixing Lost Updates under Read Committed

Since READ COMMITTED doesn't prevent this, developers must use application-level patterns to handle it.

• Pessimistic Locking (SELECT ... FOR UPDATE): This is the more direct solution. You acquire an exclusive lock on the row(s) you read, preventing any other transaction from modifying them until your transaction completes.

-- Transaction 1
BEGIN;
SELECT max(bid) FROM bidding WHERE item_id = 1 FOR UPDATE;
-- The database locks this row. Transaction 2 will wait if it tries to access it.
UPDATE bidding SET bid = <new_bid> WHERE item_id = 1;
COMMIT;

• Optimistic Locking (Compare and set): The alternative approach is to not lock the row but to check for changes just before you commit. This is often done by including the original value in the UPDATE statement. If another transaction has already changed the value, your update will affect no rows, and you can retry.

-- Transaction 1
SELECT max(bid) FROM bidding WHERE item_id = 1; // get original_bid = 100
-- ... application logic calculates new_bid = 200 ...
UPDATE bidding SET bid = 200 WHERE item_id = 1 AND bid = 100; -- this will fail if someone else updated it

Repeatable Read with Snapshot isolation

The REPEATABLE READ isolation level ensures that a transaction, when it reads a particular row, will see the same data throughout its entire duration, even if other transactions concurrently commit changes to that row. This is a higher level of isolation than READ COMMITTED because it specifically prevents the non-repeatable read anomaly.

However, there is a crucial distinction between the SQL standard’s definition of REPEATABLE READ and how it's implemented in modern databases.

• The SQL standard defines REPEATABLE READ as preventing non-repeatable reads but allowing for phantom reads. A phantom read occurs when a query executed twice within a transaction returns a different set of rows because another transaction has inserted or deleted rows that match the query's criteria.
• Snapshot Isolation is a specific concurrency control technique used by many databases (including PostgreSQL, Oracle, and some configurations of MySQL) to implement REPEATABLE READ and other levels. In this model, a transaction gets a consistent "snapshot" of the database at its start time. All subsequent reads within that transaction see this same snapshot, which automatically prevents both non-repeatable reads and phantom reads.

This is critical because Snapshot Isolation also prevents the lost update race condition. Let’s re-examine our bidding example with a database using Snapshot Isolation:

Practical Use Cases for Repeatable Read

The REPEATABLE READ isolation level is highly valuable for applications that need to perform long-running, consistent reads, such as:

• Data backups: A database backup script can run as a single transaction under REPEATABLE READ, ensuring that the entire backup file represents a perfectly consistent snapshot of the data at a single point in time.
• Index or report generation: A process that creates a new index or a large analytical report needs a consistent view of the data. REPEATABLE READ ensures that the data it's processing doesn't change from underneath it, guaranteeing the integrity of the output.

The Read Committed and Snapshot Isolation levels are designed to increase concurrency, but their read mechanisms differ. The behavior of Read Committed can vary depending on the database system. Traditional implementations use short-lived shared locks for each row during a read operation, preventing dirty reads. Many modern database systems, however, implement a variant called Read Committed Snapshot Isolation, which uses a technique called Multiversion Concurrency Control (MVCC) to avoid read locks entirely. In contrast, Snapshot Isolation consistently uses MVCC, providing each transaction with a consistent “snapshot” of the database as it existed at the start of that transaction. All subsequent read operations within the same transaction access this same version of the data. Both isolation levels acquire an exclusive lock on any row being modified, which is held until the transaction is committed or rolled back. We’ll look at MVCC in a dedicated future article.

A Practical Guide to Transaction Isolation Levels

The Serializable isolation level is theoretically the strictest, as it runs transactions one at a time. Because of this impracticality, it is not the default and is often avoided in high-concurrency systems.

When considering practical isolation levels, the most common choices are Read Committed and Repeatable Read. Read Committed is the default isolation level in many databases. While it works for most scenarios, it is prone to non-repeatable reads, which can lead to lost updates. This can sometimes be mitigated by using a combination of optimistic and pessimistic locking techniques within the application logic.

Snapshot Isolation is an advanced level that, while not as strict as Serializable, is the highest level that prevents non-repeatable reads and phantom reads. This is achieved by maintaining different versions of rows (Multiversion Concurrency Control or MVCC). The benefit of this is that no read locks are required, allowing for greater concurrency. The trade-off is that each transaction needs to maintain a snapshot ID, and a significant amount of bookkeeping is required. Snapshot Isolation is generally more resource-intensive than Read Committed. There are certain scenarios where it is a must to use snapshot isolation for accuracy. An example of such a scenario is doing a database backup or creating indexes, etc.

As always, it is essential to benchmark your application and database to determine the most suitable isolation level for your specific workload.

Thank you for being a part of the community

Before you go:

👉 Be sure to clap and follow the writer ️👏️️

👉 Follow us: **X | [Medium](https://medium.com/codetodeploy)**

👉 CodeToDeploy Tech Community is live on Discord — **Join now!**

👉 Follow our publication, CodeToDeploy

Note: This Post may contain affiliate links.