Do I really need to think about isolation levels?

A problem

For a long time, I didn’t think about isolation levels at all. I opened a transaction, ran my queries, committed. The database took care of the rest. Right?

So when I first asked myself “do I need to care about isolation?”, my honest answer was: yes and no.

No, if your system has few concurrent users, or the data isn’t critical. If two people editing a blog draft at the same time occasionally see a slightly stale version, nobody gets hurt.

Yes, the moment money, stock or seats are involved. Transfers between accounts. A booking system where two users race for the last ticket. Anything where “two things happening at once” can produce a result that should be impossible.

When multiple users or services hit the same data at the same time, strange things can happen. Isolation levels are how you tell the database how strange you’re willing to let it get.

The three strange things

Before talking about fixes, I had to understand the problems. There are three classic ones.

Dirty read

Transaction A                      Transaction B
UPDATE accounts SET balance = 0
  WHERE id = 1;
                                   SELECT balance FROM accounts
                                     WHERE id = 1;     -- sees 0
ROLLBACK;

B has seen a value that never really existed. A changed its mind and rolled back, but B already acted on the gossip.

Non-repeatable read

Transaction A                      Transaction B
SELECT status FROM orders
  WHERE id = 7;     -- 'pending'
                                   UPDATE orders SET status = 'paid'
                                     WHERE id = 7;
                                   COMMIT;
SELECT status FROM orders
  WHERE id = 7;     -- 'paid' ???

Same query, same transaction, different answer. A read the row twice and it changed underneath it.

Phantom read

Transaction A                      Transaction B
SELECT count(*) FROM bookings
  WHERE show_id = 42;   -- 99
                                   INSERT INTO bookings (show_id, ...)
                                     VALUES (42, ...);
                                   COMMIT;
SELECT count(*) FROM bookings
  WHERE show_id = 42;   -- 100 ???

No existing row changed, but a new row appeared that matches A’s query. A “phantom”.

The four isolation levels

Each level is a promise about which of those strange things can happen:

level	dirty read	non-repeatable read	phantom read
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	✗	possible	possible
REPEATABLE READ	✗	✗	possible*
SERIALIZABLE	✗	✗	✗

* in the SQL standard. PostgreSQL is stricter here, more on that below.

The way I remember them:

READ COMMITTED: “Don’t trust gossip, only listen to confirmed news.” You only ever see committed data. Dirty reads are gone. This is PostgreSQL’s default.
REPEATABLE READ: “This is your view of the world. Nobody touches it until you’re done.” Once you’ve read something, it stays the same for the rest of your transaction.
SERIALIZABLE: the big guns. “Pretend no one else exists. My transaction is running alone.” The database guarantees the result is the same as if transactions had run one after another.

How PostgreSQL does it

Once I’d read about MVCC, the PostgreSQL side of this made a lot more sense. Isolation in PostgreSQL is mostly about when the snapshot is taken:

READ COMMITTED takes a new snapshot for every statement. That’s why the same SELECT can return different results within one transaction.
REPEATABLE READ takes one snapshot at the start of the transaction and keeps it. Because it’s a full snapshot, PostgreSQL’s REPEATABLE READ also prevents phantom reads, which is stricter than the standard requires.
READ UNCOMMITTED exists in PostgreSQL, but behaves exactly like READ COMMITTED. PostgreSQL simply never shows uncommitted data.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT ...;
SELECT ...;   -- guaranteed to see the same data as the first query
COMMIT;

The trade-off: don’t overkill

The obvious next thought was “so why not just use SERIALIZABLE everywhere?” Because safety isn’t free.

Higher isolation means more conflicts. At stricter levels, the database has to do more work to keep the promise, and when two transactions clash, one of them has to fail. In PostgreSQL that looks like:

ERROR:  could not serialize access due to concurrent update

That’s not a bug. It’s the database telling you “I couldn’t keep my promise for both of you, so I cancelled one.” Which means:

Your code has to retry. If you use REPEATABLE READ or SERIALIZABLE, the application needs to catch serialization failures (SQLSTATE 40001) and run the whole transaction again. Without retries, a stricter isolation level just turns race conditions into errors for your users.

Read-only transactions are cheaper. If a transaction only reads, say so:

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY;

The database can then make fewer guarantees about writes it’ll never see, and do less work.

Sometimes a lock is simpler. For the “last ticket” problem, I often don’t need a stricter isolation level for the whole transaction. A row lock on the one thing being fought over is enough:

SELECT * FROM seats WHERE id = 42 FOR UPDATE;
-- other transactions wanting seat 42 now wait here

What I took away

There’s no universal answer, and I’d be suspicious of anyone who gives one. What I do now:

Start with the default (READ COMMITTED) and ask: what’s the worst thing that can happen if two of these run at once?
If the answer is “nothing important”, stop there.
If the answer involves money, stock or seats, pick the lowest level that keeps the data correct, or use a targeted lock.
If I go stricter, add retries in the same pull request.

Be careful, and don’t overkill. Your use case matters more than any rule of thumb.

References

PostgreSQL docs: Transaction Isolation