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.

Meaning in ACIDDifferent meaning as an overloaded term
AFor a single process, a group of writes is atomic, i.e. either committed or aborted as a whole, regardless of any fault occurring. Abortability would be a fit name for this concept as well.Outside of databases, an atomic operation refers to ability of multiple threads accessing the same data, without any of the threads accessing that data in an invalid/in-between state.
CHaving certain properties of data be consistent/invariant. In general, not within the purview of a database, but rather something that only an application as a whole can provide and guarantee - C was supposedly only added to make the acronym ‘work’.Also within the field of databases, a property of asynchronously replicated, eventually consistent storages.
IConcurrent transactions are isolated from each other. Their database usage may be taken as if each occurred serially, which is a property of an aptly named isolation level SERIALIZABLE. As we’ll demonstrate, most databases, including PG, run with a weaker isolation level READ COMMITTED by default.(No other potentially confusing overloaded meaning of this term exists in this context.)
DA committed transaction’s data is durable/stored, even in presence of hardware faults. (Obviously, in practice this can not be guaranteed. F.e. if the underlying systems, say, SSD’s firmware, are faulty themselves all bets are off.)(Ditto.)

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:

At least this isn't a full screen popup

That'd be more annoying. Anyways, subscribe to my newsletter to get new posts by email! I write about AWS, Elixir, and more.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.