Deadlock on the same object?
I encountered the following log in 8.4.1 and HEAD. The deadlock occured
on the same object (relation 17498 of database 17497). Is it reasonable?
ERROR: deadlock detected
DETAIL: Process 6313 waits for ExclusiveLock on relation 17498 of database 17497; blocked by process 6312.
Process 6312 waits for ExclusiveLock on relation 17498 of database 17497; blocked by process 6313.
Process 6313: SELECT test()
Process 6312: SELECT test()
HINT: See server log for query details.
CONTEXT: SQL function "test" statement 1
STATEMENT: SELECT test()
(relation 17498 is table 'a')
A reproducible test set is:
----
CREATE TABLE a (i integer PRIMARY KEY);
CREATE TABLE b (i integer REFERENCES a(i));
CREATE FUNCTION test() RETURNS VOID AS
$$
LOCK a IN EXCLUSIVE MODE;
LOCK b IN EXCLUSIVE MODE;
DELETE FROM a;
$$
LANGUAGE sql STRICT;
----
# Repeat the following commands in shell.
psql -c "SELECT test()" &
psql -c "SELECT test()" &
psql -c "SELECT test()" &
wait
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
I encountered the following log in 8.4.1 and HEAD. The deadlock occured
on the same object (relation 17498 of database 17497). Is it reasonable?
I think this is an artifact of the fact that SQL functions parse the
whole querystring before executing any of it. Parsing of "DELETE FROM
a" will result in acquiring ROW EXCLUSIVE lock on a, and then when the
LOCK commands are executed, you have a lock-upgrade scenario and the
deadlock is unsurprising.
There was some discussion of changing that awhile ago, but I forget
what the conclusion was. In any case nothing's been done about it.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think this is an artifact of the fact that SQL functions parse the
whole querystring before executing any of it. Parsing of "DELETE FROM
a" will result in acquiring ROW EXCLUSIVE lock on a, and then when the
LOCK commands are executed, you have a lock-upgrade scenario and the
deadlock is unsurprising.
Thanks. It's a surprise for me :-).
There was some discussion of changing that awhile ago, but I forget
what the conclusion was. In any case nothing's been done about it.
We cannot make a package of locking controls in a function under
the current behavior. It would be good to improve this area.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center