Re: SHARED locks barging behaviour

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.

Yours,
Laurenz Albe