PostgreSQL – ExclusiveLock on Related Table Due to Update

lockingpostgresqlpostgresql-9.1

Recently ran into the following:

Consider the tables created by this:

create table table_a (
        id  bigserial not null,
        last_update timestamp,
        primary key (id)
);

create table table_b (
        id  bigserial not null,
        primary key (id)
);

alter table table_b add column a_id int8;
alter table table_b add constraint fk_c foreign key (a_id) references table_a;

And then, the following query(which takes a bit longer than the example shown here might have you suspect):

update table_a set last_update = now() where id in(<list of ids>);

I observed that this update query created an ExclusiveLock on table_b.

That seems really weird, as table_b isn't being used by the query at all. Or so I think.

What's going on here? Does where id in create this lock implicitly?

Either way, how can I avoid this? Where does my understanding fail?

Also, the query that showed me this lock being taken on table_b:

SELECT a.datname,
      c.relname,
      l.transactionid,
      l.mode,
      l.GRANTED,
      a.usename,
      a.current_query,
      a.query_start,
      age(now(), a.query_start) AS "age",
      a.procpid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.procpid
JOIN pg_class c ON c.oid = l.relation
WHERE pid <> pg_backend_pid()
ORDER BY a.query_start;

Best Answer

There is no way to fix this with PostgreSQL 9.1

Up until 9.2 (including) any referencing table was locked during an update.

This was fixed in the PostgreSQL 9.3 (also unsupported by now)

Quote from the release notes:

Prevent non-key-field row updates from blocking foreign key checks

Related Question