Transaction Anomalies: Dirty Read, Non-Repeatable Read, Phantom Read

Let’s go over transaction types one more time! In the world of database transactions, there are three main “villains” that can totally ruin your day: Dirty Read, Non-Repeatable Read, and Phantom Read. These “anomalies” pop up because of not-so-great transaction isolation levels. Today, we’ll figure out who these villains are, how they show up, and—most importantly—how to fight them.

Before we jump into examples, let’s remember what they mean.

• Dirty Read:
  You’re reading data that’s been changed, but the transaction that changed it hasn’t been committed (COMMIT) yet—or worse, it might get rolled back (ROLLBACK). It’s like you sent money to a friend, checked your balance and saw you’re broke, but then changed your mind and took the money back. Magic!

• Non-Repeatable Read:
  You read the same data twice in one transaction, but between those reads, another transaction changes the data, so you see two different results. It’s like you checked your birth date in your passport, then handed it to a friend who changed the numbers, and when you looked again, your birthday was different.

• Phantom Read:
  You run the same query twice, but the second time you see extra rows that another transaction added. It’s like you’re counting people in a room, and someone quietly sneaks in more friends.

The Dirty Read Problem

Let’s say we have a table called accounts:

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    owner TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL
);
INSERT INTO accounts (owner, balance) VALUES ('Alice', 1000), ('Bob', 500);

Transaction 1 changes the balance but hasn’t finished yet, and Transaction 2 tries to read that data at the same time.

Transaction 1:

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE owner = 'Alice';
-- Alice's balance is now 800, but the transaction isn’t done yet.

Transaction 2:

BEGIN;
SELECT balance FROM accounts WHERE owner = 'Alice'; -- We see balance: 800 (dirty read).
ROLLBACK; -- Transaction 1 rolls back.

Now Transaction 2 is working with bogus data, because Transaction 1 undid its changes. You can avoid this by using the READ COMMITTED isolation level, which won’t let you see changes from uncommitted transactions.

The Non-Repeatable Read Problem

Imagine Transaction 1 reads some data, another transaction changes it, and Transaction 1 reads it again. The data is different.

Transaction 1:

BEGIN;
SELECT balance FROM accounts WHERE owner = 'Bob'; -- We see balance: 500.

Transaction 2 in parallel:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Bob';
COMMIT;

Transaction 1 again:

SELECT balance FROM accounts WHERE owner = 'Bob'; -- We see balance: 400.
COMMIT;

Notice how the data changed inside a single transaction. In real life, this can be a big deal, like for financial reports. You can fix this by using a higher isolation level, like REPEATABLE READ.

The Phantom Read Problem

Let’s say we have a table called orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer TEXT NOT NULL,
    total NUMERIC(10, 2) NOT NULL
);
INSERT INTO orders (customer, total) VALUES ('Alice', 100), ('Bob', 200);

Transaction 1 counts the number of orders, another transaction adds a new order, and Transaction 1 counts again.

Transaction 1:

BEGIN;
SELECT COUNT(*) FROM orders; -- We see: 2.

Transaction 2 in parallel:

BEGIN;
INSERT INTO orders (customer, total) VALUES ('Charlie', 300);
COMMIT;

Transaction 1 again:

SELECT COUNT(*) FROM orders; -- We see: 3. A new “phantom” order appeared!
COMMIT;

To get rid of phantom reads, you’ll need the SERIALIZABLE isolation level, which totally blocks parallel changes that could mess with your results.

Ways to Prevent Anomalies

Isolation Levels vs. Anomalies

How to Pick the Right Isolation Level?

1. If you need fast data access and anomalies aren’t a big deal (like analytics on old data)—use READ COMMITTED.
2. If you care about data staying the same inside a transaction—use REPEATABLE READ.
3. If you want max isolation and consistency—go with SERIALIZABLE. Heads up: performance might take a hit.

Practical Tips

Use transactions with isolation levels that fit your case. For example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT ...;
COMMIT;

Add indexes to minimize table locks and speed up your queries.

Use optimized queries to avoid long locks, especially at the SERIALIZABLE level.

Specific Errors and How to Avoid Them

Sometimes picking the wrong isolation level causes conflicts and slows things down. For example, using SERIALIZABLE in a system with tons of parallel transactions can lead to locks and transaction “starvation.”

To avoid this, analyze your queries, test performance at different isolation levels, and use the right indexes.