Practical Transaction Isolation in Relational Databases

When several transactions touch the same data, correctness is no longer just about writing valid SQL. The database also has to decide what each transaction is allowed to see, which writes must wait, and when a read should use a stable snapshot instead of the latest physical row value.

That is the job of the Isolation part of ACID. Higher isolation usually gives stronger protection against concurrency bugs, but it can also reduce throughput because more operations wait, block, or conflict. Lower isolation can be faster, but it exposes application logic to states that are difficult to reason about.

This article focuses on common relational database isolation behavior, using MySQL with the default InnoDB engine as the reference point. We will walk through the classic anomalies, add two important write-related cases, then connect them to locks and isolation-level rules. 🛠️

If concurrent transactions had no locking or isolation behavior at all, several classes of data anomalies could appear.

The frequently discussed ones are:

• Dirty reads

• Non-repeatable reads

• Phantom reads

Two more are especially important in real systems:

• Lost updates

• Dirty writes: this one is effectively blocked even at the weakest isolation level, so it should not occur in InnoDB under normal transaction rules.

A dirty read happens when one transaction reads data written by another transaction that has not committed yet.

Consider this sequence:

1. T1 reads A = 100

2. T1 updates A = 150

3. T2 reads A = 150

4. T1 rolls back

5. T2 continues making decisions from A = 150

The value 150 never truly became durable database state because T1 rolled back. If T2 uses that value to compute a new price, account balance, stock count, or invoice amount, the resulting decision is based on data that should not have existed from its point of view.

For example, if T2 intended to add 50 to the current price of item A, the correct result should have been 150 from the original 100. But because it read the uncommitted 150, it might add another 50 and produce 200. In financial systems, that kind of intermediate-state read is not a harmless edge case. ⚠️

A non-repeatable read occurs when a transaction reads the same row twice and gets different committed values because another transaction updated and committed the row in between.

Example:

1. T1 reads A = 100

2. T2 updates A = 10

3. T2 commits

4. T1 reads A = 10

At first glance, this can look reasonable. After all, T2 committed, so why should T1 not see the newer value?

The problem is that T1 may already be executing business logic based on the earlier value. If a customer-facing workflow or financial calculation begins from A = 100 and later sees A = 10 inside the same transaction, the application can become internally inconsistent. In domains where the transaction needs a stable view, this must be prevented.

Phantom reads are about sets of rows, not repeated reads of the same row.

They usually happen when one transaction runs a range query, another transaction inserts rows matching that range, and the first transaction runs the same range query again. The second query can return more rows than the first.

For example:

1. T1 queries all rows matching a condition

2. T2 inserts a new matching row

3. T2 commits

4. T1 repeats the same range query and sees an extra row

This article does not go deeply into phantom reads because the examples here focus mostly on concurrent changes to the same row. The key point: phantom prevention in InnoDB involves stronger isolation behavior such as Serializable and mechanisms such as gap locks.

A lost update happens when two transactions read the same value, each computes a different update, and one write overwrites the effect of the other.

Example:

1. T1 reads A = 100, while T2 also reads A = 100

2. T1 adds 10, producing A = 110

3. T1 commits A = 110

4. T2 subtracts 100, producing A = 0

5. T2 commits A = 0

The correct business outcome depends on both operations. Imagine a stock count of 100: one customer cancels an order for 10, so inventory should increase to 110; another customer buys 100, so the final stock should be 10.

Instead, the transaction that subtracts 100 used the old value 100 and committed 0. The earlier addition effectively disappeared. The database did not lose bytes, but the application lost a business event. 📌

A dirty write means one transaction overwrites a value written by another transaction before that other transaction has committed.

Example:

1. T1 reads A = 100

2. T1 sets A = 150

3. T2 sets A = 200

4. T1 commits

5. T2 rolls back

If dirty writes were allowed, the final value could reflect the wrong transaction. The expected value would be A = 150, because T2 never committed. But if T2 had been able to write over T1 before rollback handling, the result could become inconsistent.

In practice, InnoDB prevents this even under the lowest isolation level by using write locks.

Relational databases commonly expose these isolation levels:

• Read Uncommitted

• Read Committed

• Repeatable Read

• Serializable

The rough behavior for the anomalies covered here is:

• Dirty writes: cannot happen at any of these levels.

• Dirty reads: can happen in Read Uncommitted, but not in Read Committed, Repeatable Read, or Serializable.

• Non-repeatable reads: can happen in Read Uncommitted and Read Committed, but not in Repeatable Read or Serializable.

• Phantom reads: may appear below Serializable; Serializable is the level that prevents them in the scope discussed here.

• Lost updates: can happen in Read Uncommitted, Read Committed, and Repeatable Read for the non-locking-read scenario described above; Serializable prevents it, though the prevention may appear as a deadlock.

Before matching isolation levels to behavior, it helps to define the lock types.

Intention locks are table-level locks that announce a transaction’s plan to acquire more granular row locks later. They are not blocking in the normal row-level sense; they exist to coordinate lock compatibility.

In InnoDB terminology:

• IS means intent shared

• IX means intent exclusive

A shared lock, shown as S, is used for reading a row in a locking-read mode.

An exclusive lock, shown as X, is used when modifying a row. An UPDATE is a typical example.

A locking read is a SELECT that participates in locking, such as acquiring shared or intention-style locks depending on the statement form. In MySQL, examples include reads with FOR SHARE or FOR UPDATE.

A consistent non-locking read uses a snapshot instead of locking the current row. The snapshot represents the database state at a specific point in time.

This distinction matters because a plain SELECT in several isolation levels does not necessarily block writers or wait behind a writer. It may read from a snapshot instead.

Some lock behavior is stable across the isolation levels discussed here:

• X locks are held until the transaction ends.

• IS and IX locks are also held until the transaction finishes.

• Once one transaction holds an X lock for a row, another transaction cannot acquire a conflicting X lock for the same row until the first transaction commits or rolls back.

That last rule is why dirty writes do not happen. Even when reads are weak, writes still need exclusive ownership before modifying the row.

Now let’s look at the variable part: how each isolation level handles snapshots and plain reads.

At this level, reading a row does not rely on a consistent snapshot. A transaction can observe intermediate values written by another transaction before commit.

However, X, IS, and IX locks are still retained until the transaction ends.

In Read Committed, each consistent read uses a fresh snapshot. Even two reads inside the same transaction may see different data if another transaction commits between them.

A plain SELECT is a non-locking read by default here, so it does not acquire a read lock.

As with the other levels, X, IS, and IX locks remain held until the transaction completes.

In Repeatable Read, a transaction’s consistent reads use the snapshot established by the first consistent read in that transaction. If another transaction commits a change afterward, repeated consistent reads inside the original transaction still see the earlier snapshot.

There is one important exception: if the same transaction performs its own update, later reads in that transaction can see its own modified value.

Like Read Committed, plain SELECT statements are non-locking reads by default in this mode. X, IS, and IX locks are still held until transaction end.

Serializable is stricter. When autocommit is off, InnoDB automatically turns plain non-locking reads into locking reads, effectively behaving like:

SELECT ... FOR SHARE

When autocommit is on, each plain SELECT is its own transaction, so it is treated as a non-locking read.

Again, X, IS, and IX locks remain active until the transaction ends.

With the fixed and variable rules in place, the anomaly behavior becomes easier to reason about.

Sequence:

1. T1 reads A = 100

2. T1 sets A = 150

3. T2 sets A = 200

4. T1 commits

5. T2 rolls back

Read Uncommitted, Read Committed, Repeatable Read, and Serializable all prevent this.

At step 2, T1 obtains an X lock and keeps it until transaction end. Because another transaction cannot acquire a conflicting X lock on that row, T2 cannot perform the write in step 3 until T1 releases its lock. That blocks the dirty-write scenario.

Sequence:

1. T1 reads A = 100

2. T1 sets A = 150

3. T2 reads A = 150

4. T1 rolls back

5. T2 continues based on A = 150

Read Uncommitted allows the issue because reads are not snapshot-based. T2 can observe the temporary value from T1.

Read Committed and Repeatable Read avoid the dirty read because T2 reads committed snapshot data. At step 3, it still sees A = 100, not the uncommitted 150. If step 4 were a commit instead of a rollback, a lost-update pattern might still be possible later, but that would be a different anomaly.

Serializable prevents the dirty read through locking. Since T1 holds an X lock after step 2, T2’s locking read at step 3 is incompatible and must wait until T1 commits or rolls back. ✅

Sequence:

1. T1 reads A = 100

2. T2 sets A = 10

3. T2 commits

4. T1 reads A = 10

Read Uncommitted can produce different values across the two reads because it does not use snapshot-based reads.

Read Committed can also produce different values because each read refreshes its snapshot. The first read sees 100; after T2 commits, the second read can see 10.

Repeatable Read fixes the issue for consistent reads. T1 establishes its snapshot at the first read, so step 4 sees the same value from step 1.

Serializable prevents the pattern through locks. The plain read in step 1 becomes a locking read when autocommit is off, so T2 cannot update the row at step 2 until T1 finishes.

Phantom reads involve newly inserted rows appearing in a repeated range query. Since this article is centered on same-row concurrent updates, we will keep this part scoped.

The important takeaway: this anomaly is prevented by Serializable isolation in the scenario considered here, with gap-lock behavior involved under InnoDB.

Sequence:

1. T1 reads A = 100, and T2 reads A = 100

2. T1 adds 10, so A = 110

3. T1 commits A = 110

4. T2 subtracts 100, so A = 0

5. T2 commits A = 0

Read Uncommitted, Read Committed, and Repeatable Read can allow this scenario when step 1 uses non-locking reads. Both transactions can read the same starting value and then apply independent writes. The later write overwrites the effect of the earlier one.

Serializable changes the outcome. At step 1, both reads become locking reads. When T1 later needs an X lock at step 2, it is blocked by T2’s IS lock. When T2 later needs an X lock at step 4, it is blocked by T1’s IS lock.

Neither transaction releases its IS lock before the transaction ends, so the system reaches a deadlock. In InnoDB, T2 would receive a deadlock error at step 4, while T1 would proceed. The lost update is prevented, but the application must handle the deadlock path correctly. ⚠️

These commands are handy when experimenting with isolation levels, locks, and transaction behavior:

set session transaction isolation level read uncommitted; -- To set session isolation level
select @@transaction_isolation; -- To check the set session isolation level
select @@global.transaction_isolation; -- To check global isolation level
show engine innodb status; -- To check if any transaction is waiting on a lock
start transaction; -- To start a transaction
select * from x where y = z for share; -- To get IS lock
select * from x where y = z for update; -- To get IX lock
rollback; -- To rollback the transaction
commit; -- To commit the transaction

Isolation levels are not just documentation labels. They encode very different tradeoffs between snapshot visibility, read locking, write blocking, and deadlock risk.

For practical application code, the key is to decide what kind of correctness the operation needs:

• If stale or shifting reads are acceptable, lower isolation may be enough.

• If the workflow requires a stable snapshot, Repeatable Read can help.

• If range-level correctness or strict ordering matters, Serializable may be required.

• If lost updates are the concern, Serializable is one option, but not the only possible approach; explicit locking and update patterns can also solve it without raising the entire transaction to Serializable.

The examples above were intended for hands-on testing and can be reproduced with multiple database sessions.

• Isolation controls what concurrent transactions are allowed to see and modify.

• Dirty writes are blocked across all four isolation levels because write locks are held until transaction end.

• Dirty reads are possible only in Read Uncommitted among the levels discussed here.

• Non-repeatable reads can occur in Read Uncommitted and Read Committed, but Repeatable Read stabilizes consistent reads through snapshots.

• Serializable turns plain reads into locking reads when autocommit is off, which prevents lost updates but can surface deadlocks.

• Phantom reads involve range queries and inserted rows; in this scope, Serializable is the level that prevents them.