Thread: determine database and tables from deadlock
Hi Everyone
Some of our databases have been experiencing a sudden spike in deadlocks being detected.
as far as our knowledge is concerned, there have not been any new code taken live and no changes from what are aware off.
The databases are running postgresql 8.1
From the log files I am able to get the following information:
I am however not sure how to trace these back to a database, table, and maybe even a query to determine where things are going wrong.
I tried to look through the system tables however I can't seem to find the database (1502000) or the relation mentioned here, however, I am sure I am not looking in the right places as I do not know Postgresql that well.
I would really appreciate help on this as allt he deadlocks seems to be happening on the same database and relation, just different tuples and process id's.
Regards
Machiel
Some of our databases have been experiencing a sudden spike in deadlocks being detected.
as far as our knowledge is concerned, there have not been any new code taken live and no changes from what are aware off.
The databases are running postgresql 8.1
From the log files I am able to get the following information:
| 2011-05-30 00:19:05 SAST ERROR: deadlock detected 2011-05-30 00:19:05 SAST DETAIL: Process 534 waits for ShareLock on transaction 20417220; blocked by process 29184. Process 29184 waits for ExclusiveLock on tuple (127,56) of relation 1502070 of database 1502000; blocked by process 534. 2011-05-30 00:19:05 SAST STATEMENT: update Organisation set name=$1 where id=$2 2011-05-30 00:19:06 SAST ERROR: deadlock detected 2011-05-30 00:19:06 SAST DETAIL: Process 29389 waits for ShareLock on transaction 20417220; blocked by process 29184. Process 29184 waits for ExclusiveLock on tuple (127,56) of relation 1502070 of database 1502000; blocked by process 29389. 2011-05-30 00:19:06 SAST STATEMENT: update Organisation set name=$1 where id=$2 |
I tried to look through the system tables however I can't seem to find the database (1502000) or the relation mentioned here, however, I am sure I am not looking in the right places as I do not know Postgresql that well.
I would really appreciate help on this as allt he deadlocks seems to be happening on the same database and relation, just different tuples and process id's.
Regards
Machiel
On 05/30/2011 03:45 PM, Machiel Richards wrote: > I am however not sure how to trace these back to a database, table, and > maybe even a query to determine where things are going wrong. One of the deadlocking queries is shown in the deadlock error message, but one isn't really enough. IIRC, newer versions of PostgreSQL print both queries. You can use log_prefix to show the database, though I'm not sure it's in 8.1 . -- Craig Ringer