Postgres: SELECT FOR NO KEY UPDATE

Published: December 11, 2024

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

Recently at work I was using select for update¹ and found that it was possible for this statement to be blocked by an update statement on a separate table that had a foreign key to the row I was locking.

This happened despite the fact that the update statement itself did not touch the column with a foreign key constraint.

Here’s a minimal example that reproduces the problem:

-- 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;
-- 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
-- "
commit;

The reason this happens is 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 triggers a select 1 from only "public"."table_a" x where "id" operator(pg_catalog.=) $1 for key share of x which ends up blocking (or being blocked by) the select for update on table_a.

One way around this problem, besides not doing two updates on the same row in the same transaction, is to use select for no key update² instead of select for update when you don’t need to delete the row or update a column with a foreign key constraint.