Postgres Pro
Downloads
Home   >   mailing lists

Thread: Deadlock on the same object?

Deadlock on the same object?

From
Itagaki Takahiro
Date:
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

Re: Deadlock on the same object?

From
Tom Lane
Date:
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

Re: Deadlock on the same object?

From
Itagaki Takahiro
Date:
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