Hello,
I encountered a strange behavior of lightweight lock in PostgreSQL 9.2. That appears to apply to 9.6, too, as far as I
examinethe code. Could you tell me if the behavior is intended or needs fix?
Simply put, the unfair behavior is that waiters for exclusive mode are overtaken by share-mode lockers who arrive
later.
PROBLEM
====================
Under a heavy read/write workload on a big machine with dozens of CPUs and hundreds of GBs of RAM, psql sometimes took
morethan 30 seconds to connect to the database (and actually, it failed to connect due to our connect_timeout setting.)
The backend corresponding to the psql was waiting to acquire exclusive mode lock on ProcArrayLock. Some other backends
tookmore than 10 seconds to commit their transactions, waiting for exclusive mode lock on ProcArrayLock.
At that time, many backend processes (I forgot the number) were acquiring and releasing share mode lock on
ProcArrayLock,most of which were from TransactionIsInProgress().
CAUSE
====================
Going into the 9.2 code, I realized that those who request share mode don't pay attention to the wait queue. That is,
ifsome processes hold share mode lock and someone is waiting for exclusive mode in the wait queue, other processes who
comelater can get share mode overtaking those who are already waiting. If many processes repeatedly request share
mode,the waiters can't get exclusive mode for a long time.
Is this intentional, or should we make the later share-lockers if someone is in the wait queue?
Regards
Takayuki Tsunakawa