Database isolation layers

Last time I wrote about ACID properties. Isolation is one of ACID transaction properties, along with atomicity, consistency and durability.

There are four isolation levels defined by ANSI/ISO SQL standard:

  • serializable,
  • repeatable reads,
  • read committed,
  • read uncommitted.

Read phenomena

The standard describes also three different read phenomena which behaviour and result varies depending on chosen isolation level:

  • a dirty read – a transaction can read not yet committed data modified by another transaction,
  • a non-repeatable read – during a transaction a row is being read twice and the values within the row are different,
  • a phantom read – another transaction make changes by adding or removing some rows. Depending on isolation level, the first transaction could return the same values as before, even if some of them were removed or added by the second transaction.

Isolation layers and read phenomena

So now when I mentioned isolation layers and read phenomena, how they meet each other?

  • serializable:
    • keeps read and write locks to the end of the transaction,
    • range-locks must be acquired when a SELECT query uses a range WHERE clause (to avoid the phantom reads).
  • repeatable reads:
    • keeps read and write locks to the end of the transaction,
    • range-locks are not managed (phantom reads are possible),
    • write skew (a phenomenon where two writes are allowed to the same column by two different writers) is possible at this isolation level in some systems.
  • read committed:
    • keeps only write locks to the end of the transaction (non-repeatable reads phenomenon can occur in this isolation level),
    • range-locks are not managed (phantom reads are possible).
  • read uncommitted:
    • one transaction may see not-yet-committed changes made by other transactions (dirty reads are possible).

Isolation layer in databases

Isolation layer is typically defined at database level. As I work mainly with PostgreSQL, let me focus on that database. PostgreSQL supports all four layers but internally only three of them are implemented (current for version 12th which I use and current also in version 14th that is the latest available version at the time I published this post): read uncommitted mode behaves like read committed. PostgreSQL also guarantees that on repeatable read level, phantom reads are not possible!

In PostgreSQL the default isolation level is read committed but you can set a different isolation level by using  SET TRANSACTION command.

Leave a comment

Your email address will not be published. Required fields are marked *