Re: Exposing the lock manager's WaitForLockers() to SQL

Hi,

On 2023-01-12 19:21:00 -0800, Will Mortensen wrote:
> FWIW re: deadlocks in general, I probably didn't highlight it well in my
> original email, but the existing solution for this use case (as Marco
> described in his blog post) is to actually lock the table momentarily.
> Marco's blog post uses ShareRowExclusiveLock, but I think ShareLock is
> sufficient for us; in any case, that's stronger than the AccessShareLock that
> we need to merely wait.
>
> And actually locking the table with e.g. ShareLock seems perhaps *more*
> likely to cause deadlocks (and hurts performance), since it not only waits for
> existing conflicting lockers (e.g. RowExclusiveLock) as desired, but also
> undesirably blocks other transactions from newly acquiring conflicting locks
> in the meantime. Hence the motivation for this feature. :-)
>
> I'm sure I may be missing something though. Thanks for all your feedback. :-)

From a deadlock risk pov, it's worse to hold an AccessShareLock and then wait
for other transaction to end, than to just wait for ShareRowExclusiveLock,
without holding any locks.

If you don't hold any locks (*) and wait for a lock, you cannot participate in
a deadlock, because nobody will wait for you. A deadlock is a cycle in the
lock graph, a node can't participate in a deadlock if it doesn't have any
incoming edges, and there can't be incoming edges if there's nothing to wait
on.

Consider a scenario like this:

tx 1: acquires RowExclusiveLock on tbl1 to insert rows
tx 2: acquires AccessShareLock on tbl1
tx 2: WaitForLockers(ShareRowExclusiveLock, tbl1) ends up waiting for tx1
tx 1: truncate tbl1 needs an AccessExclusiveLock

Boom, a simple deadlock. tx1 can't progress, because it can't get
AccessExclusiveLock, and tx2 can't progress because tx1 didn't finish.

But if tx2 directly waited for ShareRowExclusiveLock, there'd not been any
cycle in the lock graph, and everything would have worked.

Regards,

Andres

(*) If you define holding locks expansive, it's impossible to wait for a lock
without holding a lock, since every transaction holds a lock on its own
virtual transactionid. But normally nobody just waits for a transaction that
hasn't done anything.