SELECT FOR UPDATE locking other tables in PostgreSQL
We also don't ever update primary keys.
In that case, I think you can use FOR NO KEY UPDATE
instead of FOR UPDATE
. It's a weaker lock, as explained in Postgres docs about Explicit Locking:
FOR NO KEY UPDATE
Behaves similarly to
FOR UPDATE
, except that the lock acquired is weaker: this lock will not blockSELECT FOR KEY SHARE
commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by anyUPDATE
that does not acquire aFOR UPDATE
lock.
Test:
-- Setup
create table a (id int primary key,
x varchar);
create table b (id int primary key
references a (id) on update cascade on delete cascade,
x varchar);
insert into a values (1), (2);
insert into b values (1, null), (2, null);
-- Left
begin;
select 1 from a for no key update;
/* Keep the transaction open */
-- Right
begin;
update b set x = 'abc' where id = 1;
update b set x = 'xyz'; -- doesn't block
-- Left
update a set x = 'left' where id = 1;
commit ;
-- Right
commit ;