Relational Database & ACID Transactions : Part 2

By Pradyumna Chippigiri

December 05, 2025

Series

• Relational Databases & ACID Transactions

In Part 1 of the System Design from first principles series we discussed relational databases, their history, and the “A” and “C” of ACID.

In this one, we will continue the same and discuss more about the “I” and “D” in ACID with some practical examples as well.

3. Isolation

When multiple transactions are executing in parallel, the isolation levels determine how much changes of one transaction are visible to others. Isolation ensures that concurrently running transactions do not interfere with each other’s intermediate states.

If two users are updating the same row and column at the same time, whose update should “win,” and when should the other user be allowed to see that new value?

Before we talk about isolation levels, we need to understand the concurrency anomalies they’re trying to prevent. These are the weird, unintuitive things that can happen when multiple transactions run at the same time.

Dirty read

• Transaction B reads data that Transaction A has modified but not yet committed.
• If Transaction A then rolls back, Transaction B ends up holding an invalid or dirty value that never truly existed in the committed state.

Non-repeatable read

• Within a single transaction A, the same row is read more than once, while Transaction B modifies and commits that row in between those reads.
• Transaction A then sees different values for the same row during its lifetime, so transaction A cannot repeat the same read and get the same result.

Phantom read

• Transaction A runs a query that returns a set of rows matching some condition.
• Transaction B then inserts, updates, or deletes rows that satisfy that condition, so when Transaction A runs the same query again, it sees a different set of rows, extra or missing phantoms within the same transaction.

Note: This is different from non-repeatable reads because in non-repeatable reads, values in the same row change, but in phantom reads, new rows are added or deleted.

Isolation levels

Now that we’ve seen the anomalies (dirty reads, non-repeatable reads, lost updates, phantom reads), we can finally talk about isolation levels.

When multiple transactions run in parallel, the isolation level is basically the database’s answer to:

“How much of Transaction B’s work is Transaction A allowed to see (and when), and which anomalies are we willing to tolerate for the sake of performance?”

Repeatable read

Consistent reads within the same transaction. The idea is: “If I read a row once in my transaction, I want to see the same value every time I read that row again.”

At repeatable read:

• Dirty reads are prevented.
• Non-repeatable reads are prevented (A’s view of a given row is stable).
• Phantom reads can occur.

Now I start two transactions T1 and T2, concurrently. In the image below, let’s say the left transaction is T1 and the right transaction is T2.

This is how we check the isolation level (I’ve used MySQL; you can check the documentation for other databases.)

Here I update a row in T1 (and I haven’t committed yet), and if I read the same row from T2, T2 would still show the older value of the row (consistent read).

And now if I commit T1, and re-read the same row in T2 then again it would still read the same old value (haven’t committed T2 yet). So this proves that repeatable read is opaque to whatever has been done elsewhere in the system it still gives a stable consistent read value.

Read committed

• T1 can only see changes from T2 after T2 has committed.
• T1 will never read uncommitted data from T2.
• The idea here is: “Only let me see committed data.”

So at read committed:

• No dirty reads (blocked).
• But still:
  • Non-repeatable reads can happen.
  • Phantom reads can happen.

Setting the isolation level for both transactions to READ COMMITTED.

Read uncommitted

• Reads even uncommitted values from the transaction.
• Idea: “I don’t care, just let me read whatever is there, even if it’s not committed yet.”
• T1 is allowed to read changes that T2 has made but not committed yet.
• If T2 rolls back, T1 has already used a value that never truly existed.

So at read uncommitted, the database allows:

• Dirty reads
• Non-repeatable reads
• Phantom reads

Now again we will set the isolation level to READ UNCOMMITTED.

As you see we haven’t committed yet in T2.

Serializable

• This is the strictest and the strongest isolation level because when one transaction reads, it blocks the others, so others will have to wait.
• Every read is a locking read (it just blocks everything).
• Idea: “Pretend all transactions ran one after another, not in parallel.”

So at serializable:

• Dirty reads: not allowed.
• Non-repeatable reads: not allowed.
• Phantom reads: not allowed.

As you can see this is what we mean by strict isolation level, as we haven’t committed or rolled back T1 yet, T2 just waits there until a COMMIT or a ROLLBACK operation happens.

So to summarize this is how the isolation level and concurrency anomalies relate to:

Now, coming to durability.

Durability

Once a transaction commits successfully, its changes must survive crashes, restarts, and power failures. The database is not allowed to forget a committed transaction.

How databases ensure durability

• Transaction logs (write-ahead logging):
  Most relational databases rely on a Write-Ahead Log (WAL) to preserve changes before they’re written to the main data files.

You can read more about WAL here: WAL article

• Replication

Synchronous replication

Writes are sent to multiple nodes or data centers immediately. A transaction is only marked as committed when the primary and at least one replica (or a quorum, depending on the system) confirm that the data is safely stored. This reduces the risk of data loss if the primary fails right after commit.

Asynchronous replication

The primary node commits locally first and then ships changes to replicas in the background. This improves latency, but creates a small window where recent writes may be lost if the primary fails before the replicas have been updated.

• Backups and snapshots

Regular backups provide a safety net beyond logs and replication. In case of severe corruption, human error, or catastrophic failure:

• Full backups: capture the entire database at a specific point in time.
• Incremental/differential backups: store only the changes since the last full (or previous) backup, making backups faster and more frequent.
• Off-site storage: keeps backups in a separate location so they survive localized disasters and hardware failures.

Hope you got a clear understanding of what ACID transactions are and how all of this works behind the scenes.

If you learned something out of this then please do like and subscribe, this really would give me more motivation to create such in depth deep dives.

Take care and see you in the next one.