Обсуждение: 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. Process6312 waits for ExclusiveLock on relation 17498 of database 17497; blocked by process 6313. Process 6313: SELECTtest() 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