"Ryo Yamaji (Fujitsu)" <yamaji....@fujitsu.com> writes: > In pgql-general, I reported that the queue order changed in > the following cases. [1] > ・瘢雹Multiple sessions request row locks for the same tuple > ・瘢雹Update 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/tyapr01mb6073506eccd7b8f51da807f68a...@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
