deadlock error messages

From: dan chak <chak(at)MIT(dot)EDU>
To: pgsql-general(at)postgresql(dot)org
Subject: deadlock error messages
Date: 2008-03-20 17:31:52
Message-ID: F208DC44-47A8-44C0-A8BE-C22DB4B4A142@mit.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When there's a deadlock detected, the error message recorded in the
log prints out the query being aborted, and the process id of the
other transaction involved in the deadlock. It would be great if more
context was printed for the other query (e.g. the query itself), as
opposed to just the process id. By the time the logs are parsed, the
process id isn't very useful. Example output we can parse out:

PGError: ERROR: deadlock detected
DETAIL: Process 29245 waits for ShareLock on transaction 136665841;
blocked by process 4483.
Process 4483 waits for ShareLock on transaction 136665996; blocked by
process 29245.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."forms" x WHERE
"id" = $1 FOR SHARE OF x"
SQL statement "INSERT INTO form_qualifications (arrival_id,
program_id, simple_form_id, associated_form_id, position, site_id,
created_at) select $1 , $2 [gs.ser], $3 [gs.ser], $4 , $5 , $6 ,
now() from generate_series(1, array_upper( $3 , 1)) as gs(ser)"
PL/pgSQL function "insert_form_qualifications" line 2 at SQL statement
:
select insert_form_qualifications(73404580,

array
[9997,9998,11334,2034,2051,10006,2053,6088,8966,2054,8967,2055,2056,8977,8978,2058,2052,2065
],

array
[6991,6991,6991,6831,6831,6831,6831,6831,6831,6831,6831,6831,6831,6992,6831,6992,6831,6831
],
1479,
2,
1)

What would be really great would be to know what the other query is,
as opposed to just the pid (not sure from this output if it's 4483 or
29245). Also not sure if this is the right list for this. But
potentially someone on here may have a good tip on debugging
deadlocking?

Thanks,

Dan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Clark 2008-03-20 17:52:58 cast problem 8.3.1
Previous Message Bjørn T Johansen 2008-03-20 17:12:58 Re: Which JDBC version to use with PostgreSQL 8.1.11?