What kind of deadlock the deadlock_timeout config parameter detects in Postgresql?

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 erro

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.

StackExchange Database Administrators Q#195477, answer score: 3