Thread: deadlock problem

hi,
I found a deadlock on 2 queries, DELETE and REINDEX TABLE

DELETE waits for RowExclusiveLock on table address_jd
and
REINDEX waits for AccessExclusiveLock on PRIMARY KEY address_jd_pk

log:
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-1]
host=10.0.5.144,user=ppstat,db=ppstat ERROR:  deadlock detected
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-2]
host=10.0.5.144,user=ppstat,db=ppstat DETAIL:  Process 25576 waits for
RowExclusiveLock on relation 16613 of database 16388;
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-3]  blocked by process 26649.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-4]         Process 26649 waits
for AccessExclusiveLock on relation 17776 of database 16388; blocked by
process 25576.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-5]
host=10.0.5.144,user=ppstat,db=ppstat CONTEXT:  SQL statement "DELETE FROM
dsa_nas.address_jd WHERE exp_id = 7"
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-6]         PL/pgSQL
function "delete_export" line 16 at EXECUTE statement
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-7]         SQL statement "SELECT
transform_functions.delete_export('dsa_nas', $1 )"
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-8]         PL/pgSQL
function "transform_nas" line 152 at PERFORM
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-9]
host=10.0.5.144,user=ppstat,db=ppstat STATEMENT:  SELECT
transform_functions.transform_nas(7)
Mar 19 00:30:57 ch08bl09 postgres[26649]: [3-1]
host=[local],user=postgres,db=ppstat LOG:  duration: 865522.949 ms
statement: REINDEX TABLE dsa_nas.address_jd

where can be problem?

thanks,
    tashunko

Milos Findura <findura@gmail.com> writes:
> where can be problem?

Presumably, one statement or the other is inside a transaction that
already had a lower-grade lock on the target table.

            regards, tom lane

Not so,
    statements have diffrent pid. Delete is called in application, bud REINDEX is
called via some cron admin script.

t.

On Thursday 19 of March 2009 13:35:03 Tom Lane wrote:
> Milos Findura <findura@gmail.com> writes:
> > where can be problem?
>
> Presumably, one statement or the other is inside a transaction that
> already had a lower-grade lock on the target table.
>
>             regards, tom lane