"Ryo Yamaji (Fujitsu)" <yamaji.ryo@fujitsu.com> writes:
> In pgql-general, I reported that the queue order changed in
> the following cases. [1]
> $B!&(BMultiple sessions request row locks for the same tuple
> $B!&(BUpdate occurs for target tuple
> I would like to hear the opinion of experts on whether it is a
> specification or a bug.
> I think row locking is a FIFO specification, using tuple header
> and lock managers. Therefore, I think that the above is a bug.
> [1]
> https://www.postgresql.org/message-id/TYAPR01MB6073506ECCD7B8F51DA807F68A0F9@TYAPR01MB6073.jpnprd01.prod.outlook.com
I don't see a bug here, or at least I'm not willing to move the
goalposts to where you want them to be. I believe that we do guarantee
arrival-order locking of individual tuple versions. However, in the
example you show, a single row is being updated over and over. So,
initially we have a single "winner" transaction that got the tuple lock
first and updated the row. When it commits, each other transaction
serially comes off the wait queue for that tuple lock and discovers
that it now needs a lock on a different tuple version than it has got.
So it tries to get lock on whichever is the latest tuple version.
That might still appear serial as far as the original 100 sessions
go, because they were all queued on the same tuple lock to start with.
But when the new sessions come in, they effectively line-jump because
they will initially try to lock whichever tuple version is committed
live at that instant, and thus they get ahead of whichever remain of
the original 100 sessions for the lock on that tuple version (since
those are all still blocked on some older tuple version, whose lock is
held by whichever session is performing the next-to-commit update).
I don't see any way to make that more stable that doesn't involve
requiring sessions to take locks on already-dead-to-them tuples;
which sure seems like a nonstarter, not least because we don't even have
a way to find such tuples. The update chains only link forward not back.
regards, tom lane