Learning About Transaction Isolation Levels in Databases

I have been working at a database company for over seven years and I must admit I haven't quite figured out isolation levels yet. But I was in a meeting recently where we were discussing materialized views and snapshot isolation, and I just couldn't understand how these two could possibly be related.

So, I decided to dedicate some time to properly learning transaction isolation levels. In doing so, I found lots of great content (which I'll link to at the bottom), but I wanted to write a little summary for my future self in case I ever forget some of this.

Notice, however, that I didn't go full depth on this. If you want the absolute guide to all this, you can read Jepsen's Consistency page. That is the holy grail. I've only personally studied the basic isolation levels (Serializable, Snapshot Isolation, Repeatable Read, Read Committed, Read Uncomitted).

Dangerous Read Phenomenons and Dangerous Write Phenomenons

Let's look at four different criteria that are used to evaluate the isolation level of a database management system. We have to understand each one before we can categorize any specific system.

Dirty Reads

A dirty read is when a transaction is able to read a row that has been updated by another transaction that hasn't yet been committed.

Non-Repeatable Reads

Let's say a transaction reads some row. And then later on in the same transaction, the same row gets read but its value has changed. That's a non-repeatable read.

Phantom Read

Now, let's say a transaction reads a set of rows based on some condition (typically a WHERE filter). Then, later on, it performs the same read query, but the set of rows returned is different (larger or smaller). That's a phantom read.

Write Skew

This one is a bit harder to understand. One of the classical examples is as follows:

Imagine a hospital where there always has to be at least one doctor oncall. And then, two doctors which are both oncall try to cancel their shift. The database transaction starts by checking that there are least 2 oncall doctors, so that the doctor can cancel his/her shift safely.

Because the transactions ran concurrently, both doctors are able to cancel their shifts leaving nobody oncall. Oh no!

That is what is widely considered to be a "write skew".

This example, and others, are explained in great depth in a book called "Designing Data-Intensive Applications". I highly recommending buying it, reading it once and then keeping it around for reference. However, you can also find the same explanation on this Stack Overflow answer.

Categorizing DMBSs Based on Their Isolation Levels

	Dirty Read	Non-Repeatable Read	Phantom Read	Write Skew
Read Uncommitted	✅	✅	✅	✅
Read Committed	❌	✅	✅	✅
Repeatable Read	❌	❌	✅	✅
Snapshot Isolation	❌	❌	❌	✅
Serializable	❌	❌	❌	❌

(✅ = Can Happen, ❌ = Cannot Happen)

We can see that certain systems abort transactions when some of these phenomenons could occur. How can one then read this table?

A system is said to be Read Uncomitted if Dirty Reads can happen.
A system is said to be Read Committed if Dirty Reads cannot happen, but Non-Repetable Reads can happen.
A system is said to have Repeatable Reads if Non-Repeatable Reads cannot happen, but Phantom Reads can occur.
A system is said to implement Snapshot Isolation if none of the "bad" Read Phenomena can occur. Coincidentally, this also means that read-only transactions can never be aborted.
A system is said to be Serializable (or to have Serializable Snapshot Isolation) if none of those "bad things" can happen. However, there is a much more succint definition of "Serializable" that I prefer: "A system is said to be serializable if any set of transactions are executed and the end state of the data is exactly the same, whether the transactions are executed in sequence, or concurrently".
- Many databases support a FOR UPDATE clause which can be used to achieve a sort of serializability even in Read Committed mode.

That's it!

Now, there's a few notes and caveats to all this...

As I already mentioned, I did not appropriately cover all serialization levels.
I talked about Read Phenomena (Dirty Reads, Non-Repeatable Reads, Phantom Reads) as well as Write Phenomena (Write Skew) under the same "umbrella" of topics, but most literature distinctly separates these two things.
Some systems call themselves "Serializable" but they only actually implement "Snapshot Isolation".
All of these have different performance implications. And most database systems even allow different levels of safety to be configured at a global level, or even at a per-transaction level.
If you're wondering... how are materialized views related to snapshot isolation then? Well, even after learning isolation levels properly, I had to go and ask some people at work about this. What I've now learnt is that there are ways to build materialized views in such a way that they are dependent on the underlying system supporting snapshot isolation. One such method is explained in this paper from VLDB 2007.

Feel free to reach out on Twitter!