Postgres isolation levels in (mal)practice

Designing Data-Intensive Applications is a great expository book on many topics related to backend engineering. As is usually the case, rereading and putting the theory in practice solidifies our understanding. Thus, let’s showcase various database (DB) isolation levels from its Chapter 7, on transactions. We’ll use Postgres (PG) as our DB and Elixir as its client.

But first let’s summarise the constituents of the well known acronym ACID in the following table. We also contrast the terms with their overloaded meanings outside of the database field.

With ACID summarised, let’s now demonstrate various isolation levels within PG. There are 4, ANSI-defined, isolation levels:

• Read uncommitted: not possible in PG, as ‘dirty reads’ are never allowed.

• Read committed: the default level, which guarantees

  • no ‘dirty reads’: non-committed data can not be read by another transaction. Implemented by remembering and serving the previous, already committed value. Using locks would’ve made the whole DB too easily destabilized by a single long-running transaction.
  • no ‘dirty writes’: only committed data can be overwritten by another transaction. Implemented by row-level locks.

• Repeatable read: previous level’s no-‘dirty reads’-guarantee still doesn’t prevent a non-repeatable read or ‘read skew’. An example of which is a transaction with 2 reads, each reading from two separate bank accounts, both reading only committed data, yet their sum results in an invalid balance, observing the DB in an inconsistent state. If the transaction were repeated the sum would likely be correct, but this is unacceptable for some uses, f.e. backups. Thus this isolation level’s name: all reads must be repeatable, unlike the just described example.

  This level is also referred to as snapshot isolation: standard’s definitions of isolation levels are ambiguous, and implementations between DBs differing, especially for this and serializable level.

• Serializable:

Isolation levels

READ COMMITTED isolation level

Setup (ignoring setting a primary key etc.):

create table accounts(id int, balance int);
insert into accounts values(1, 0);

defmodule Account do
  use Ecto.Schema
  import Ecto.Changeset
  schema "accounts" do
    field :balance, :integer
  end
  def changeset(account, attrs), do: cast(account, attrs, [:balance])
end

Set iterations large enough to make the concurrent transactions interleave, f.e. with iterations = 10000.

We’ll now demonstrate the ‘lost update’ problem that occurs on this isolation level. The problem happens even if the following read-modify-write (RMW) cycle is wrapped in a transaction.

Without a lock

Run the following in two terminals, at roughly the same time:

import Ecto.Query
for _ <- 1..iterations do
  Repo.transaction(fn ->
    a = Account
      |> where([a], a.id == ^1)
      |> Repo.one

    a |> Account.changeset(%{balance: a.balance + 1})
      |> Repo.update!
  end)
end

After runs in both terminals complete, check the balance with

select balance from accounts where id=1;

The resulting balance will be less than the expected 2 * iterations, but rather merely iterations + number-of-updates-when-transactions-did-not-interleave.

Atomic update

Since our RMW does a simple increment, a better solution would be to avoid the read-modify-write cycle altogether. Let’s update the balance atomically.

With a row-level lock

But if the RMW cycle’s change can’t be done with PG’s built-in atomic operation, such as inc in the previous section, we can request a lock on the application side, highlighted in the following code.

for _ <- 1..iterations do
  Repo.transaction(fn ->
    a = Account
      |> where([a], a.id == ^1)
      |> lock("FOR UPDATE")      |> Repo.one

    a |> Account.changeset(%{balance: a.balance + 1})
      |> Repo.update!
  end)
end

Note that we still do the RMW in a transaction, as the lock only exists for a transaction’s lifetime. As before, run the code in two terminals at the same time, but this time with an FOR UPDATE lock.

After runs in both terminals are done, check the balance.

select balance from accounts where id=1;

The balance will be exactly 2 * iterations, as intended.

for _ <- 1..iterations do
  Account |> where(id: ^1) |> update(inc: [balance: 1]) |> Repo.update_all([])
end

Now, with the more stringent isolation level of , the two concurrent transactions shouldn’t be able to undo each other’s updates. Let’s test this out empirically, using the same procedure as above:

# same 2-terminals setup as above
# (dramatic pause)

Indeed, the end balance is exactly 2 * iterations. For a proof, refer to: