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.
