What happens when two people book a vacation at the exact same time? Each vacation booking is made up of multiple steps: loyalty accounts have points added, account credit needs to be subtracted, and rooms need to be allocated.

We represent bookings as transactions - groups of queries that are meant to be executed atomically (as if they were one query).

When mulitple transactions happen at once, the database maintains sets of rules which govern what is allowed or not allowed to read and write at the same time. A set of rules is known as a database isolation level. On one isolation level, reads and writes on the same table may be able to haphazardly interlay into each other as both transactions execute concurrently. On another, one transactions may grab a full table lock for the resources it needs, making every other transaction wait for it to complete. These are two extreme examples, and there exist many transaction levels between them.

Levels are defined by the level to which different transactions can "interact" with each other. These interactions come in three flavours. This article focuses on the dirty read.

A dirty read comes when one transaction reads records that another transaction is updating in the middle of a transaction. These records that are updated during a transaction before the entire transaction is marked as completed are known as uncommitted. The issue with dirty reads comes when the uncommitted record updates are rolled back. In this case, the transaction that executed the dirty read is left reading an update that never actually happened.

Take these two transactions which represent vacation bookings from the same user. One attempts to pay with cash, and one with loyalty points.

In the following interleaving, we have a problem. The green transaction goes through, but it only goes though because of a dirty read on the number of loyalty points. When the purple transaction is rolled back, it ends up leaving the account with a negative amount of loyalty points, but with a free vacation!

We can specify isolation levels in our database in order to prevent this from happening. The lowest transaction level that handles dirty reads is read committed. In a read-committed scenario, transactions acquire read locks when they need to read data, and write locks for affected records until the transaction is finished. Let's see how the same interleaving reacts to this.

It worked! Dirty reads are handled by the read-committed transaction level, and no free vacations are being handed out.