Re: SHARED locks barging behaviour

Hi,

On 2023-09-30 00:50:11 +0200, Laurenz Albe wrote:
> On Fri, 2023-09-29 at 17:45 -0400, Bruce Momjian wrote:
> > On Tue, Jan 17, 2023 at 12:18:28PM -0500, Arul Ajmani wrote:
> > > I'm trying to better understand the following barging behaviour with SHARED
> > > locks.
> > ...
> > > Given there is a transaction waiting to acquire a FOR UPDATE lock, I was
> > > surprised to see the second FOR SHARE transaction return immediately instead of
> > > waiting. I have two questions:
> > >
> > > 1) Could this barging behaviour potentially starve out the transaction waiting
> > > to acquire the FOR UPDATE lock, if there is a continuous queue of transactions
> > > that acquire a FOR SHARE lock briefly?
> >
> > Yes, see below.
> >
> > > 2) Assuming this is by design, I couldn't find (in code) where this explicit
> > > policy choice is made. I was looking around LockAcquireExtended, but it seems
> > > like the decision is made above this layer. Could someone more familiar with
> > > this code point me at the right place? 
> >
> > I know this from January, but I do have an answer. [...]
>
> You answer the question where this is implemented. But the more important question
> is whether this is intentional. This code was added by 0ac5ad5134f (introducing
> FOR KEY SHARE and FOR NO KEY UPDATE). My feeling is that it is not intentional that
> a continuous stream of share row locks can starve out an exclusive row lock, since
> PostgreSQL behaves differently with other locks.
>
> On the other hand, if nobody has complained about it in these ten years, perhaps
> it is just fine the way it is, if by design or not.

I'd be very hesitant to change the behaviour at this point - the likelihood of
existing workloads slowing down substantially, or even breaking due to an
additional source of deadlocks, seems substantial.

Greetings,

Andres Freund