Re: Deadlock between backend and recovery may not be detected - Mailing list pgsql-hackers

From Victor Yegorov
Subject Re: Deadlock between backend and recovery may not be detected
Date
Msg-id CAGnEbohzTqXperry5WaQY_S5AAGJ=+YiZ0BYZqtap2kVx-zvgw@mail.gmail.com
Whole thread Raw
In response to Deadlock between backend and recovery may not be detected  (Fujii Masao <masao.fujii@oss.nttdata.com>)
Responses Re: Deadlock between backend and recovery may not be detected
List pgsql-hackers
ср, 16 дек. 2020 г. в 13:49, Fujii Masao <masao.fujii@oss.nttdata.com>:
After doing this procedure, you can see the startup process and backend
wait for the table lock each other, i.e., deadlock. But this deadlock remains
even after deadlock_timeout passes.

This seems a bug to me.

> * Deadlocks involving the Startup process and an ordinary backend process
> * will be detected by the deadlock detector within the ordinary backend.

The cause of this issue seems that ResolveRecoveryConflictWithLock() that
the startup process calls when recovery conflict on lock happens doesn't
take care of deadlock case at all. You can see this fact by reading the above
source code comment for ResolveRecoveryConflictWithLock().

To fix this issue, I think that we should enable STANDBY_DEADLOCK_TIMEOUT
timer in ResolveRecoveryConflictWithLock() so that the startup process can
send PROCSIG_RECOVERY_CONFLICT_STARTUP_DEADLOCK signal to the backend.
Then if PROCSIG_RECOVERY_CONFLICT_STARTUP_DEADLOCK signal arrives,
the backend should check whether the deadlock actually happens or not.
Attached is the POC patch implimenting this.

I agree that this is a bug.

Unfortunately, we've been hit by it in production.
Such deadlock will, eventually, make all sessions wait on the startup process, making
streaming replica unusable. In case replica is used for balancing out RO queries from the primary,
it causes downtime for the project.

If I understand things right, session will release it's locks when max_standby_streaming_delay is reached.
But it'd be much better if conflict is resolved faster, around deadlock_timeout.

So — huge +1 from me for fixing it.


--
Victor Yegorov

pgsql-hackers by date:

Previous
From: Konstantin Knizhnik
Date:
Subject: Re: On login trigger: take three
Next
From: "Drouvot, Bertrand"
Date:
Subject: Re: Deadlock between backend and recovery may not be detected