Please notice that it is not a first of April joke;)
Several times we and our customers are suffered from the problem that
Postgres got stuck in deadlock detection.
One scenario is YCSB workload with Zipf's distribution when many clients
are trying to update the same record and compete for it's lock.
Another scenario is large number of clients performing inserts in the
same table. In this case the source of the problem is relation extension
In both cases number of connection is large enough: several hundreds.
So what's happening? Due to high contention backends will not be able to
obtains requested lock in the specified deadlock detection timeout (1
second by default).
Wait it interrupted by timeout and backend tries to perform deadlock
detection. CheckDeadLock sets exclusive lock on all partitions locks...
Avalanche of deadlock timeout expiration
in backends and there contention of exclusive partition locks cause
Postgres to got stucks.
Speed falls down almost to zero and it is not possible even to login to
It is well known fact that Postgres is not scaling well for such larger
number of connections and it is necessary to use pgbouncer or some other
to limit number of backends. But modern systems has hundreds of CPU
cores. And to utilize all this resources we need to have hundreds of
So this is not an artificial problem, but real show stopper, which takes
place on real workloads.
There are several ways to solve this problem.
First is trivial: increase deadlock detection timeout. In case of YCSB
it helps. But in case of many concurrent inserts, some backends are
waiting for lock for several minutes.
So there is no any realistic value of deadlock detection timeout which
can completely solve the problem.
Also significant increasing of deadlock detection timeout may case
blocking applications for unacceptable amount of time in case of real
There is a patch in commitfest proposed by Yury Sokolov:
He make deadlock check in two phases: first under shared lock and second
under exclusive lock.
I am proposing much simpler patch (attached) which uses atomic flag to
prevent concurrent deadlock detection by more than one backend.
The obvious drawback of such solution is that detection of unrelated
deadlock loops may take larger amount of time. But deadlock is abnormal
situation in any case and I do not know applications which consider
deadlocks as normal behavior. Also I didn't see in my life situations
when more than one independent deadlocks are happen at the same time
(but obviously it is possible).
So, I see three possible ways to fix this problem:
1. Yury Sololov's patch with two phase deadlock check
2. Avoid concurrent deadlock detection
3. Avoid concurrent deadlock detection + let CheckDeadLock detect all
deadlocks, not only those in which current transaction is involved.
I want to know opinion of community concerning this approaches (or may
we there are some other solutions).
Thanks in advance,
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index bfa8499..6412184 100644
@@ -86,6 +86,8 @@ static LOCALLOCK *lockAwaited = NULL;
static DeadLockState deadlock_state = DS_NOT_YET_CHECKED;
+static bool inside_deadlock_check = false;
/* Is a deadlock check pending? */
static volatile sig_atomic_t got_deadlock_timeout;
@@ -186,6 +188,7 @@ InitProcGlobal(void)
ProcGlobal->walwriterLatch = NULL;
ProcGlobal->checkpointerLatch = NULL;
* Create and initialize all the PGPROC structures we'll need. There are
@@ -754,6 +757,14 @@ ProcReleaseLocks(bool isCommit)
+ /* Release deadlock detection flag is backend was interrupted inside deadlock check */
+ if (inside_deadlock_check)
+ inside_deadlock_check = false;
/* If waiting, get off wait queue (should only be needed after error) */
/* Release standard locks, including session-level if aborting */
@@ -1658,6 +1669,14 @@ CheckDeadLock(void)
+ * Ensure that only one backend is checking for deadlock.
+ * Otherwise under high load cascade of deadlock timeout expirations can cause stuck of Postgres.
+ if (!pg_atomic_test_set_flag(&ProcGlobal->activeDeadlockCheck))
+ inside_deadlock_check = true;
* Acquire exclusive lock on the entire shared lock data structures. Must
* grab LWLocks in partition-number order to avoid LWLock deadlock.
@@ -1732,6 +1751,9 @@ CheckDeadLock(void)
for (i = NUM_LOCK_PARTITIONS; --i >= 0;)
+ inside_deadlock_check = false;
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index e974f4e..f5c8b05 100644
@@ -253,6 +253,8 @@ typedef struct PROC_HDR
/* Buffer id of the buffer that Startup process waits for pin on, or -1 */
+ /* Deadlock detection is in progress */
+ pg_atomic_flag activeDeadlockCheck;
extern PGDLLIMPORT PROC_HDR *ProcGlobal;