3

The situation, with PostgreSQL 9.6:

  • table A with integer primary key
  • table B with foreign key constraint on its primary key referencing table A's primary key

SELECT id FROM A FOR UPDATE; blocks UPDATE B SET x=y; until the lock on A is released.

I assume this is because the referenced value might change. What can I to avoid the first statement blocking execution of the second one without dropping the foreign key constraint?

If I drop the foreign key constraint, what bad things should I expect? In the actual database where this problem occurs, it wouldn't be a significant issue if table B had rows left over after deleting from A. We also don't ever update primary keys.

Edit with code:

-- Setup
create table a (id int primary key);
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 update;
/* Keep the transaction open */

-- Right
begin;
update b set x = 'abc' where id = 1;
update b set x = 'xyz'; /* It blocks here /*
  • You should try SELECT .. FOR NO KEY UPDATE;. It's a weaker lock. – ypercubeᵀᴹ Oct 11 '17 at 12:32
5

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 block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR 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 ;
  • Amazing, thank you! I was afraid I'd have to drop foreign keys :) – Julien Oct 11 '17 at 14:35
0

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for the update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE of these rows will be blocked until the current transaction ends.

SO just remove the lock by removing FOR UPDATE..I couldn't get why do you require the "FOR UPDATE" on the first statement.

  • The FOR UPDATE is really intended; I do want any other update on table A to be blocked. I don't want updates on table B to be blocked though. – Julien Oct 11 '17 at 5:19
  • @Julien do you want to lock a specific row or the whole table? – ypercubeᵀᴹ Oct 11 '17 at 12:14
  • @ypercubeᵀᴹ I want to lock specific rows, just to avoid deadlocks. The actual statement in the application is something like SELECT 1 FROM table WHERE ... ORDER BY id FOR UPDATE; – Julien Oct 11 '17 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.