Isolation

Introduction

Isolation is a transaction property that defines how concurrently running transactions see each other's data. It establishes the visibility semantics of the transaction, i.e., how and when parts of the transaction should be visible to other concurrently running transactions.

There are different isolation levels, each of which caters to different types of use cases and scenarios. The following section covers different types of isolation levels in detail and their implications.

Terminology

Before we discuss the different isolation level guarantees, let’s go over some terminology.

Dirty reads

If a transaction includes a series of write operations and executes certain write operations, and if another transaction reads the values written by the first transaction before the commit, it is a dirty read. Dirty reads are problematic because, if the first transaction aborts, the second transaction will have read values that do not exist later.

• At time T1, transaction A begins. The balance of account A is $500.

• At time T2, transaction A deducts $100 from account A. At the same time, transaction B begins.

• At time T3, transaction B reads the balance of account A. If the database allows dirty reads, the value returned would be $400.

• At time T4, the database rolls back transaction A.

Thus, the database allows reading an uncommitted value, which is later rolled back in a dirty read. This scenario leads to inconsistent results.

Dirty writes

Let’s consider two concurrently running transactions that are writing data values to the same set of rows. Suppose the first transaction executes some write operations, followed by the second transaction overwriting the values written by the first transaction before the commit. In that case, the situation is considered a dirty write. Dirty writes are problematic because if the first transaction aborts, the second transaction would have written values using the first transaction value that does not exist later.

• At time T1, transaction A begins. The balance of account A is $500.

• At time T2, transaction A adds $100 from account A. At the same time, transaction B begins.

• At time T3, transaction B adds $100 from account A. If the database allows dirty writes, the value returned would be $700. ...