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.
[…]
Whenlog_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 normaldeadlock_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
Best Answer
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.No. It seems you're confusing locks and deadlocks here.
It's automatic. If that error does not arise, it's because there is no deadlock.
By creating an actual deadlock with at least two transactions conflicting. See postgres deadlock without explicit locking on S.O. for a simple example.