Simple locking use-cases in PostgreSQL

Intro

Although relying on techniques based on optimistic locking (like MVCC for example) is usually a better choice, especially in terms of performance, there are other locking mechanisms that are worth consideration. Contrary to optimistic strategies, where it is assumed that conflicting updates tend to happen rather rarely, locking introduces contention. But it can also make the code simpler and easier to reason about.

What’s more, in case of PostgreSQL it automatically acquires exclusive locks on rows when they are updated or deleted so you might not get away without blocking anyway.

Lost updates problem

When running in READ COMMITTED mode you may stumble upon the lost updates problem. In the most basic scenario it can occur if you select a record, run some computation and then update it with the results of that computation.

For example, transferring 100$ to Bob’s account could be implemented in 2 steps:

-- 1) read the current balance
SELECT balance
FROM accounts
WHERE owner = 'Bob'; -- balance: 500$

-- 2) increase the balance by 100$
UPDATE accounts
SET balance = 600 -- 500$ + 100$
WHERE owner = 'Bob';

If two transactions fetch the same balance before one of them updates and commits the updated account the value will be overwritten and an update will be lost (hence the name of this phenomena - lost updates).

And this can be simply solved by levelling up to REPEATABLE READ as described in more detail in my previous post.

Explicit locking as an alternative

But we could also take an alternative approach and use some form of explicit locking while still running in READ COMMITTED.

PostgreSQL (like many other RDBMS) has a quite useful SELECT … FOR UPDATE construct to exclusively lock only the selected record(s) until the transaction finishes:

SELECT balance
FROM accounts
WHERE owner = 'Bob'
FOR UPDATE; -- locks the row

We then just need to update the locked row and commit the transaction:

UPDATE accounts
SET balance = 600
WHERE owner = 'Bob';
COMMIT; -- releases the lock

It has a similar effect to UPDATE or DELETE which acquires an exclusive row-level lock for each of the affected row for the duration of the transaction.

Atomic increment

This works fine, but we can actually make it better. Operations such as incrementation can be usually done in an atomic fashion, so we can get the job done with just one statement:

UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Bob';

Please bear in mind that since it modifies rows in a table it also implicitly makes use of locks, which can have some undesirable side effects if not used carefully (but more on that in a second).

Transfer money between accounts

Let’s complicate our example a little bit by bringing another account into the picture. Imagine we need to transfer 100$ from Bob to Alice. We could take advantage of what we have used so far and implement it like this:

UPDATE accounts
SET balance = balance - 100
WHERE owner = 'Bob';

UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Alice';

COMMIT;

Deadlock

It pleases the eye with its simplicity, but there is one caveat. If two transactions interleave in a certain way we could run into a deadlock:

-- TRANSACTION #1

UPDATE accounts
SET balance = balance - 100
WHERE owner = 'Bob';
-- locks Bob's account





UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Alice';
-- waits for a lock on Alice's account

-- TRANSACTION #2





UPDATE accounts
SET balance = balance + 300
WHERE owner = 'Alice';
-- locks Alice's account

UPDATE accounts
SET balance = balance - 300
WHERE owner = 'Bob';
-- waits for a lock on Bob's account

To automatically unstuck in such situation you would need to run a database that actively looks out for deadlocks.

For example, PostgreSQL would discover that you are in trouble and it would cancel one of the transactions allowing the second one to finish successfully:

ERROR:  deadlock detected
DETAIL:  Process 57351 waits for ShareLock on transaction 1269; blocked by process 57321.
Process 57321 waits for ShareLock on transaction 1270; blocked by process 57351.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"
SQL state: 40P01

If you would like to prevent deadlocks from ever happening in your application then you should acquire the locks in a certain order.

As an example, we could first try to take lock on an account with a higher id or owned by a user whose name is alphabetically higher on the list of accounts (assuming names are unique).

The end

I hope you have enjoyed this post. But before we part ways here is a link to an information-rich article about locks in PostgreSQL that you might be interested in.