Re: An example of bugs for Hot Standby

Hot Standby node can freeze when startup process calls LockBufferForCleanup().
This bug can be reproduced by the following procedure.

0. start Hot Standby, with one active node(node A) and one standby node(node B)
1. create table X and table Y in node A
2. insert several rows in table X in node A
3. delete one row from table X in node A
4. begin xact 1 in node A, execute following commands, and leave xact 1 open
4.1 LOCK table Y IN ACCESS EXCLUSIVE MODE
5. wait until WAL's for above actions are applied in node B
6. begin xact 2 in node B, and execute following commands
6.1 DECLARE CURSOR test_cursor FOR SELECT * FROM table X;
6.2 FETCH test_cursor;
6.3 SELECT * FROM table Y;
7. execute VACUUM FREEZE table A in node A
8. commit xact 1 in node A

...then in node B occurs following "deadlock" situation, which is not detected by deadlock check.
* startup process waits for xact 2 to release buffers in table X (in LockBufferForCleanup())
* xact 2 waits for startup process to release ACCESS EXCLUSIVE lock in table Y

This situation can occur when
a) a transaction in the standby node tries to acquire ACCESS SHARE lock while holding some buffers
b) startup process calls LockBufferForCleanup() for any of the buffers

regards,

--
Hiroyuki YAMADA
Kokolink Corporation
yamada(at)kokolink(dot)net

On Tue, 2009-12-15 at 20:25 +0900, Hiroyuki Yamada wrote:
> Hot Standby node can freeze when startup process calls LockBufferForCleanup().
> This bug can be reproduced by the following procedure.

Interesting. Looks like this can happen, which is a shame cos I just
removed the wait checking code after not ever having seen a wait.

Thanks for the report.

Must-fix item for HS.

--
Simon Riggs www.2ndQuadrant.com

On Wed, 2009-12-16 at 10:33 +0000, Simon Riggs wrote:
> On Tue, 2009-12-15 at 20:25 +0900, Hiroyuki Yamada wrote:
> > Hot Standby node can freeze when startup process calls LockBufferForCleanup().
> > This bug can be reproduced by the following procedure.
>
> Interesting. Looks like this can happen, which is a shame cos I just
> removed the wait checking code after not ever having seen a wait.
>
> Thanks for the report.
>
> Must-fix item for HS.

So this deadlock can happen at two places:

1. When a relation lock waits behind an AccessExclusiveLock and then
Startup runs LockBufferForCleanup()

2. When Startup is a pin count waiter and a lock acquire begins to wait
on a relation lock

So we must put in direct deadlock detection in both places. We can't use
the normal deadlock detector because in case (1) the backend might
already have exceeded deadlock_timeout.

Proposal:

Make Startup wait on a well-known semaphore rather than on its
proc->sem. This means we can skip the spinlock check in
ProcSendSignal().

For (1) if Startup runs LockBufferForCleanup and can't get cleanup lock
then it marks itself waiting. It then checks for any lock waiters. If
there are >0 lock waiters then it waits for up to max_standby_delay and
then aborts all current lock waiters, none of whom would ever wake if we
continue waiting.

For (2) If a normal backend goes into a lock wait in HS then it will
check to see if Startup is waiting, if so, throw ERROR. This can happen
immediately because if Startup is already waiting then to wait for the
lock would cause deadlock.

--
Simon Riggs www.2ndQuadrant.com

On Wed, 2009-12-16 at 14:05 +0000, Simon Riggs wrote:
> On Wed, 2009-12-16 at 10:33 +0000, Simon Riggs wrote:
> > On Tue, 2009-12-15 at 20:25 +0900, Hiroyuki Yamada wrote:
> > > Hot Standby node can freeze when startup process calls LockBufferForCleanup().
> > > This bug can be reproduced by the following procedure.
> >
> > Interesting. Looks like this can happen, which is a shame cos I just
> > removed the wait checking code after not ever having seen a wait.
> >
> > Thanks for the report.
> >
> > Must-fix item for HS.
>
> So this deadlock can happen at two places:
>
> 1. When a relation lock waits behind an AccessExclusiveLock and then
> Startup runs LockBufferForCleanup()
>
> 2. When Startup is a pin count waiter and a lock acquire begins to wait
> on a relation lock
>
> So we must put in direct deadlock detection in both places. We can't use
> the normal deadlock detector because in case (1) the backend might
> already have exceeded deadlock_timeout.
>
> Proposal:

Better proposal

* It's possible for 3-way deadlocks to occur in Hot Standby mode.
* If a user backend sleeps on a lock while it holds a buffer pin that
* leaves open the risk of deadlock. The user backend will only sleep
* if it waits behind an AccessExclusiveLock held by Startup process.
* If the Startup process then tries to access any buffer that is pinned
* then it too will sleep and neither process will ever wake.
*
* We need to make a deadlock check in two places: in the user backend
* when we sleep on a lock, and in the Startup process when we sleep
* on a buffer pin. We need both checks because the deadlock can occur
* from both directions.
*
* Just before a user backend sleeps on a lock, we accumulate a list of
* buffers pinned by the backend. We then grab the an LWlock
* and then check each of the buffers to see if the Startup process is
* waiting on them. If so, we release the lock and throw deadlock error.
* If Startup process is not waiting we then record the pinned buffers
* in the BufferDeadlockRisk data structure and release the lock.
* When we later get the lock we remove the deadlock risk.
*
* When the Startup process is about to wait on a buffer pin it checks
* the buffer it is about to pin in the BufferDeadlockRisk list. If the
* buffer is already held by one or more lock waiters then we send a
* conflict cancel to them and wait for them to die before rechecking
* the buffer lock.

This way we only cancel direct deadlocks.

It doesn't solve general problem of buffer waits, but they may be
solvable by different mechanism.

--
Simon Riggs www.2ndQuadrant.com

>This way we only cancel direct deadlocks.
>
>It doesn't solve general problem of buffer waits, but they may be
>solvable by different mechanism.
>

Following question may be redundant. Just a confirmation.

Deadlock example is catstrophic while it's rather a rare event.
On the other hand, LockBufferForCleanup() can cause another
problem.

* One idle pin-holder backend can freeze startup process().

This problem is not catstrophic, but it seems a similar problem
which StandbyAcquireAccessExclusiveLock() tries to avoid.

...Is this the problem you call "general problem" above ?

regards,

--
Hiroyuki YAMADA
Kokolink Corporation
yamada(at)kokolink(dot)net

>Following question may be redundant. Just a confirmation.
>
>Deadlock example is catstrophic while it's rather a rare event.
>On the other hand, LockBufferForCleanup() can cause another
>problem.
>
> * One idle pin-holder backend can freeze startup process().
>
>This problem is not catstrophic, but it seems a similar problem
>which StandbyAcquireAccessExclusiveLock() tries to avoid.
>
>...Is this the problem you call "general problem" above ?
>

Here is a typical scenario in which startup process freezes until the end of
a certain transaction.

1. Consider a table A, which has pages with HOT chain tuples old enough to be vacuumed.
2. Xact 1 in the standby node declares a cursor for table A, fetches the page
which contains the HOT chain, and becomes idle for some reason.
3. Xact 2 in the active node reads the table A and calls heap_page_prune()
for HOT pruning, which create XLOG_HEAP2_CLEAN record.
4. Startup process tries to redo XLOG_HEAP2_CLEAN record, calls
LockBufferForCleanup() and freezes until the Xact 1 ends.

Note that with HOT pruning, we do not need VACUUM command, and most tables,
which has long history of updation, can be table A.

--
Hiroyuki YAMADA
Kokolink Corporation
yamada(at)kokolink(dot)net