I've been reviewing Alvaro's patch for shared row locks, and come across
some corner cases that may need discussion. Does anyone disagree with
the following statements?
1. If several transactions are holding shared lock on a row, and one
of them wants to actually modify the row (or upgrade its lock to
exclusive), it must wait for the others to end but can then do so.
(I think the patch does this properly, but it's not documented in
the comments.)
2. If a transaction is holding exclusive lock on a row, but then issues
SELECT ... FOR SHARE on the row, its lock should remain exclusive.
(I don't think the patch gets this right.)
Another issue that we may need to think about is that there is no
protection against starvation: a would-be acquirer of a row lock
could wait forever, because there isn't any mechanism preventing
other processes from getting in front of him. This has always
been true of our SELECT FOR UPDATE code, but I think the risk is
much higher with SELECT FOR SHARE added to the mix --- one could
easily imagine an endless stream of share-lockers coming along and
effectively handing down the share lock, while a would-be exclusive
locker remains frozen out. I don't see any easy solution to this,
unfortunately.
regards, tom lane