How to get more details about the PostgreSQL transaction which has locked a row

Question

My Java+PostgreSQL application sometimes gets errors about not being able to lock a row. In these cases I'd like to log some information about the other transaction which keeps the row locked.

How can I get more information about this other transaction in a Java program? Can I somehow give names to the transactions I create in my app and make Postgres report the name of the other transaction in case of a locking failure?

Answer 1

Unless the row is locked by more that a single transaction (which is rare), you can find the transaction that locks the row with id = 1 in table locks like this:

SELECT xmax FROM locks WHERE id = 1;

 xmax 
══════
  761
(1 row)

The session that belongs to this transaction is found as follows:

SELECT pid FROM pg_locks
WHERE transactionid = 761 AND mode = 'ExclusiveLock';

 pid  
══════
 5460
(1 row)

You can terminate the locking session with

SELECT pg_terminate_backend(5460);