Search code examples
postgresqldeadlockpsql

How do I interpret postgresql deadlock message?


I'm running a Postgresql 9.5.2 server, and I'm occasionally seeing a message like:

ERROR: deadlock detected
Detail: Process 1234 waits for ShareLock on transaction 3042999324; blocked by process 5678.
Process 5678 waits for ShareLock on transaction 3042999328; blocked by process 1234.
Hint: See server log for query details.
Where: while locking tuple (5389,30) in relation "asset"

If it contains any information about the row or column that's causing the deadlock, it will help me debug the big ugly common-table expression that's causing the error in the first place.


Solution

  • I figured it out while looking up the correct terminology to use while asking my question: tuple refers to the row's ctid, a system column on every row indicating the physical location of the version of the row in question. (When a row is updated, Postgresql keeps the old version around for a while in order to fulfill ACID guarantees.)

    You can select the data simply with:

    SELECT * from "asset" where ctid = '(5389,30)';
    

    However, if you wait too long (like I did), an autovacuum job might clean up that version of the row if it's no longer in use.