Postgres: Why is an update on another table blocking my select for update?

Published: December 11, 2024

Recently at work I was implementing a piece of functionality that required cross-process synchronization where the shared resource was a row in a Postgres table.

Having used select for update¹ for this purpose in the past with good results, I quickly reached for it again. This is an explicit row locking mode that prevents other transactions from acquiring a lock on the same row until the current transaction commits or rolls back.

However, when a colleague of mine was QAing the feature, they noticed a strange interaction with another feature. Essentially, the new feature appeared to be blocked by the other feature when they were used concurrently. However, we knew for sure that the other feature was not locking the row we were acquiring the lock on when using select for update.

After some debugging I arrived at the following facts:

• One transaction issues a select for update for a row on table_a
• Another transaction is issuing two updates for the same row on table_b, this table has a foreign key to table_a

At this point, I didn’t understand exactly why this would cause one of the transactions to be blocked by the other, but I could reliably reproduce it with a simple example.

So I decided to set a low statement_timeout to understand exactly which statement was being blocked:

-- transaction 1
begin;
select id from table_a where id = 1 for update;
select pg_sleep(20);
commit;

-- transaction 2
begin;
set local statement_timeout to '5s';
-- NOTE: table_b has a foreign key to table_a, this row has table_a_id = 1
update table_b set val = 1 where table_b.id = 2;
update table_b set val = 2 where table_b.id = 2;
commit;

And here’s what happened after the statement timed out on transaction 2:

canceling statement due to statement timeout
CONTEXT:  while locking tuple (0,29) in relation "table_a"
SQL statement "
  select 1
  from only "public"."table_a" x
  where "id" operator(pg_catalog.=) $1
  for key share of x
"

This didn’t tell me much, but after asking Claude, it pointed out that this was a foreign key constraint check resulting from the update. I then read some more into what for key share² was exactly, but also found it odd that this would happen for an update that did not touch any foreign key columns.

After some googling I came across a Stack Exchange DBA answer by Paul White³. Paul’s answer explains when this happens and why.

1. It happens on insert, which makes sense since the foreign key constraint must be validated;
2. It happens on update, as long as it updates the column that has the foreign key constraint;
3. It happens on updates where Postgres cannot detect that the foreign key column has not been updated!

It seems that Postgres cannot detect that the foreign key column has not been updated when the same row is updated twice in the same transaction. This is what triggers the select 1 from only "public"."table_a" x where "id" operator(pg_catalog.=) $1 for key share of x we saw earlier, which ends up blocking (or being blocked by) the select for update on table_a.

I then looked into how to avoid this particular problem. One recommended way is to use select for no key update⁴ instead of select for update, if you don’t need to delete the row or update a column with a foreign key constraint. This is a lighter lock that does not conflict with for key share locks. Vlad Mihalcea⁵ has also recently written about this lock mode, but more in the context of inserts.

So there you have it, if you’re using select for update and you want to make sure that no child table’s insert or update statement can conflict with your lock, use select for no key update instead.