Обсуждение: Locking when concurrent updated of foreign references

Hi.

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: BEGIN;
TXN1: update test set data = upd('something');
TXN2: BEGIN;
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
-- 
Jesper

On 2011-04-11 20:18, Jesper Krogh wrote:
> Hi.
>
> This seem a bit strange to me. In short:
Not any more I.. I guess what made me a bit confused was that
a "update table set key = value" would acually block out changes
on tables referencing this tuple even if the referenced column wasn't
effected by the update.

But when the locking is done "row-level" then it is correct
to do it that way. It would allthough be nice with a weaker
locklevel for that kind of updates (I have no clue if that is
a hard problem).

-- 
Jesper

Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011:

> But when the locking is done "row-level" then it is correct
> to do it that way. It would allthough be nice with a weaker
> locklevel for that kind of updates (I have no clue if that is
> a hard problem).

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 2011-04-11 23:30, Alvaro Herrera wrote:
> Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011:
>
>> But when the locking is done "row-level" then it is correct
>> to do it that way. It would allthough be nice with a weaker
>> locklevel for that kind of updates (I have no clue if that is
>> a hard problem).
> http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
>
That looks exactly what I have been seeing.

Naive suggestion (at least to part of the problem):
Would it be possible to identify updates that never
can violate any constraints and not do any verification
of foreign keys on the update and only pick a lock
that block concurrent updates of the same tuple?

UPDATE table set <something which is neither referenced or a reference>;
would all be of that type.

Would allthough require the database to examine
the UPDATE statement and in comparison with the
table definition figure out which of the column are
"safe" to update.

There might actually be a potential speedup since the update
would require to go visit the foreign table at all.

Jesper
-- 
Jesper


-- 
Jesper