Posted on Tuesday March 29, 2022

One thing I wish I had understood better earlier on in my experience with
PostgreSQL is how transactions and locks can be used together to provide
serializable logic.

An easy way to illustrate this is with a simple bank account system. Suppose we
create an accounts table and populate it like this:

create table accounts (
  name text primary key,
  balance int not null
);
insert into accounts (name, balance) values ('A', 10), ('B', 0);

Now we have two bank accounts, A with a balance of $10, and B with a balance
of $0.

In order to be a useful bank, we want to be able to move money from one
account to another. In pseudocode, the way to move money from one account to
another might look something like:

function moveMoney(from, to, amount):
  # Start a transaction.
  txn = db.begin()
  # Update the balances.
  txn.execute('update accounts set balance = balance - $amount where name = $from')
  txn.execute('update accounts set balance = balance + $amount where name = $to')
  # Commit the transaction.
  txn.commit()

We use a transaction here to make sure that either both updates succeed, or both
updates fail. In other words, we want to avoid the situation where money is
deducted from A but never deposited to B.

There’s another situation that we might want to avoid in our bank too: we might
want a rule that account balances can never be negative. To enforce this rule,
we can update our moveMoney function:

function moveMoney(from, to, amount):
  # Moving a negative amount of money from A to B is equivalent to moving the
  # corresponding positive amount from B to A.
  if amount < 0:
    moveMoney(to, from, -1*amount)
    return

  # Start a transaction so that all of our queries/updates succeed or fail as a
  # unit.
  txn = db.begin()

  # Make sure the $from account has a balance of at least $amount.
  currBalance = txn.query('select balance from accounts where name = $from')
  if currBalance < amount:
    txn.rollback()
    throw exception

  # Move the money as before.
  txn.execute('update accounts set balance = balance - $amount where name = $from')
  txn.execute('update accounts set balance = balance + $amount where name = $to')

  # Commit the transaction.
  txn.commit()

But there’s a problem with this! Using a transaction only ensures that all of
the writes succeed or fail together, it does not provide any guarantees that
all of the statements in the transaction execute “at the same time” (i.e. the
transactions are not serializable).

Preventing concurrency bugs

Let’s simulate two different actors calling moveMoney('A', 'B', 10)
concurrently, again with A having an initial balance of $10 and B having $0:

Now, if we check the account balances, we can see a problem:

postgres=# select * from accounts ;
 name | balance
------+---------
 A    |     -10
 B    |      20

Both actors read the initial balance as $10, and therefore allowed the
operations to proceed. The transaction is ensu