This seem a bit strange to me. In short:

2 tables, one with has a foreign key to the other one;

CREATE TABLE test (id SERIAL primary key, data text);
CREATE TABLE testref(id SERIAL primary key, test_id integer references test(id) not null, data text);
INSERT INTO test(data) values('something');
INSERT INTO testref(test_id,data) values(1,'something else');
CREATE OR REPLACE FUNCTION upd(data text) RETURNS text as $$ BEGIN PERFORM pg_sleep(3); return data; END; $$ LANGUAGE 'plpgsql' immutable;

Then start 2 transactions (from different psql prompts):

TXN1: update test set data = upd('something');
TXN2: update testref set data = upd('something'); (within 3 seconds of the other update, so they are overlapping in execution) <this one blocks on a lock-wait of the other transaction even after this other command is done>

Wether this is correct or not I'm not sure, but if you remove the "upd()" calls
in both above so the update isn't exactly executed at the same time
then both commands succeed without interfering with each other.

While waiting on TXN2's update, pg_locks shows that there is a ShareLock that cannot be granted, which will first get further when TXN1 commits or rolls back.

It seems as the lock isn't released if some other process is actually waiting
for it?

It may be related to the plpgsql function, I have a similar one (doing more
clever things than sleep) in the production system, but I cannot figure out how
to get the updates overlapping in execution in other ways.

A hugely trimmed down example of something I currently see in a production
system. (8.4.7) but above is on 9.1HEAD

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to