3

My overall goal is to get a 40P01/ERRCODE_T_R_DEADLOCK_DETECTED from postgres when an deadlock is detected. (To be more precise, I would like to get a LockAcquisitionException in Hibernate, which is the exception the 40P01/ERRCODE_T_R_DEADLOCK_DETECTED error code is translated in PostgreSQL81Dialect.java) I use Postgresql 9.6.

For this I thought I should set the deadlock_timeout and log_lock_waits config variables as suggested by 19.12. Lock Management

deadlock_timeout (integer) This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition. [...] When log_lock_waits is set, this parameter also determines the length of time to wait before a log message is issued about the lock wait. If you are trying to investigate locking delays you might want to set a shorter than normal deadlock_timeout.

I've set the following values in postgresql.conf

log_lock_waits = on         # log lock waits >= deadlock_timeout
deadlock_timeout = 5s

Now, when I create a deadlock situtation (from Java using Hibernate) I find the following in the postgresql.log

LOG:  process 17493 still waiting for ShareLock on transaction 322815 after 5000.464 ms
DETAIL:  Process holding the lock: 17495. Wait queue: 17493.
CONTEXT:  while updating tuple (132,35) in relation "publication"

However no 40P01/ERRCODE_T_R_DEADLOCK_DETECTED error is generated (and sent to the JDBC driver).

I digged a little but into the postgres source code and found that the dead lock detection done by setting deadlock_timeout/log_lock_waits is a different mechanism than the one generating 40P01/ERRCODE_T_R_DEADLOCK_DETECTED. The deadlock_timeout case is handled in backend/storage/lmgr/proc.c , while the 40P01/ERRCODE_T_R_DEADLOCK_DETECTED case in backend/storage/lmgr/deadlock.c

So, my questions are:

  • Are these actually two different types of deadlocks that are detected?
  • Is there a way to get an error when deadlock_timeout based deadlock detection happens?
  • How can actually a ERRCODE_T_R_DEADLOCK_DETECTED error be forced to happen?

UPDATE: the code I use to get into the deadlock situation goes like this (Spring/Java):

// This is in a Transaction managed by spring
Publication p = em.find(Publication.class, id);
p.setComment("Outer "+new Date());
em.flush();

// This utility method runs the lambda expression in a new Transaction
// using Spring's TransactionTemplate and tries to update
// the same Publication that is about to be updated by the 
// "outer" transaction
Utils.runInSeparateTransaction(status -> {
    Publication p2 = em.find(p.getClass(), p.getMtid());
    p2.setComment("Inner "+new Date());
    return p2;
    // Would flush/commit after the return, but will "hang" instead.
    // Here I would expect the deadlock error but only get the
    // deadlock log.
};

// Outer transaction would commit at this point but will
Balázs E. Pataki
  • 95
  • 1
  • 2
  • 6

1 Answers1

3

A deadlock is a situation where multiple transactions conflict which each other in the locks they have cross-acquired. That situation is impossible to solve without aborting one of the transactions. The engine aborts such a transaction with ERRCODE_T_R_DEADLOCK_DETECTED as the error.

The log_lock_waits is not primarily related to deadlocks, it's meant to alert about the situation that some queries are stuck waiting for locks, meaning that other transactions keep locks for too long.

Are these actually two different types of deadlocks that are detected?

No. It seems you're confusing locks and deadlocks here.

Is there a way to get an error when deadlock_timeout based deadlock detection happens?

It's automatic. If that error does not arise, it's because there is no deadlock.

How can actually a ERRCODE_T_R_DEADLOCK_DETECTED error be forced to happen?

By creating an actual deadlock with at least two transactions conflicting. See postgres deadlock without explicit locking on S.O. for a simple example.

Daniel Vérité
  • 32,662
  • 3
  • 78
  • 84