There are four isolation levels, and it can be useful to define them in terms of the problems they prevent1. Taking a look at these problems:
- Dirty read: One database transaction begins and updates some rows. Another transaction comes along before the earlier transaction has committed, reads the rows affected by the earlier update, then the earlier transaction is aborted or rolled back. The second transaction therefore acted on data that is considered to never have existed2, therefore violating consistency.
- Non-repeatable read: A transaction reads a row in one statement, another transaction updates that row, then the first transaction reads that same row again. The first transaction received differing values within the row the first and second times it read the row.
- Phantom read: Similar to non-repeatable read, but instead of column values within a row differing, it is when entirely different sets of rows are returned.3
There are four isolation levels, each of which can prevent different subsets of the above.
- Read uncommitted: Transactions can see changes made by other transactions before they commit.
- Read committed: Transactions can only see changes that other transactions have committed. The default in Postgres.
- Repeatable read: Data within rows is guaranteed not to change within a transaction, even across separate statements within the transaction.
- Serializable: Transactions are serial, or run as if4 they take place in series (i.e., sequentially).
Note that these are listed as an ordered list; the higher the number, the higher the degree of isolation. The isolation levels solve the problems above per the following matrix1:
| Isolation Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read uncommitted | May occur | May occur | May occur |
| Read committed | Don’t occur | May occur | May occur |
| Repeatable read | Don’t occur | Don’t occur | May occur |
| Serializable | Don’t occur | Don’t occur | Don’t occur |
So why not always pick serializable if it solves all these potential problems? As with most things in software—it depends. Serializable is the safest option of the four, but also the slowest. Its extra checks slow down or block concurrency altogether.
It is wise to pick an isolation level based on the minimum specific concurrency issues that need to be prevented, to take advantage of higher concurrency levels as much as possible.
Further topics for continued learning:
- Justin Jaffray – What Does Write Skew Look Like? (mirror)
- Multiversion concurrency control (MVCC) and its relation to Postgres
VACUUM
- GeeksForGeeks – Transaction Isolation Levels in DBMS↩
- StackOverflow – Dirty Reads↩
- StackOverflow – Non-repeatable vs Phantom Reads↩
- The database does not necessarily literally have to run the transactions in series; it’s fine if it runs them concurrently for performance optimisation, but the end result under this isolation level is that it must behave as if the transactions were run in series.↩