Thread: Behavior of shared/exclusive row locks

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

On Wed, Apr 27, 2005 at 11:19:34AM -0400, Tom Lane wrote:

> 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.)

I agree with both.  The reason the patch does not deal with either is
because I didn't really think about those cases.  My bad.

> 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.

As I already mentioned in private email, I think this could be handled
via locking the MultiXactId itself, using the regular lock manager, just
before going to sleep.  There's no harm in bloating the lock tables too
much because one backend can sleep on only one MultiXactId.  So, any
would-be locker first gets the MultiXactId and then it sleeps on it, to
be awaken when the exclusive locker finishes.

This requires having an additional bogus relation like XactLockTable ...
I see this as introducing more cruft than I originally thought about
removing with.  (Remember, I originally thought about getting rid of
XactLockTableWait and friends entirely.)

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Los dioses no protegen a los insensatos.  Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Wed, Apr 27, 2005 at 11:19:34AM -0400, Tom Lane wrote:
>> 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.

> As I already mentioned in private email, I think this could be handled
> via locking the MultiXactId itself, using the regular lock manager, just
> before going to sleep.  There's no harm in bloating the lock tables too
> much because one backend can sleep on only one MultiXactId.  So, any
> would-be locker first gets the MultiXactId and then it sleeps on it, to
> be awaken when the exclusive locker finishes.

Well, I'd like to fix also the starvation cases that can arise from
SELECT FOR UPDATE without any MultiXactId involved --- that is, if there
are multiple backends all trying to lock the same tuple, one of them
could lose repeatedly, perhaps indefinitely if the kernel scheduler
isn't quite fair.

Suppose that we redo the LOCKTAGs per previous discussion (which I would
like to do anyway), so that it is possible to define an lmgr lock on a
particular tuple.  The objection to this was that there could be too
many locks held for lmgr to cope, but your idea above shows the way out.
Once a backend realizes that it's got to wait for a tuple, it releases
the page context lock and then gets the lmgr lock representing the
target tuple, with either a shared or exclusive lock mode as
appropriate.  After it gets that, it waits on the current tuple holder
as in your patch.  After it gets that, it updates the tuple state as
needed, and only then releases the lmgr lock.

A backend that thinks it can join an existing shared locker also has to
get the lmgr lock in the same way, so that it will block if there is
a pending exclusive locker.

This gives us full lmgr semantics for resolving who-should-wake-up-first,
but we have no more than one active lmgr lock per backend --- the bulk
of the state is on disk, as in your patch.  And the "normal" paths where
there is no conflict for a tuple don't have any added overhead, because
we don't have to take out a lock in those paths.
        regards, tom lane

On Wed, Apr 27, 2005 at 05:51:47PM -0400, Tom Lane wrote:

> Suppose that we redo the LOCKTAGs per previous discussion (which I would
> like to do anyway), so that it is possible to define an lmgr lock on a
> particular tuple.

Hm.  If you want I can give you the part of the patch that dealt with
changing LOCKTAG.  It's not a big deal but it might save you some
effort.  (On the other hand, if you want to do a wholesale revamp of
LOCKTAG, it will only slow you down.)

> The objection to this was that there could be too
> many locks held for lmgr to cope, but your idea above shows the way out.
> Once a backend realizes that it's got to wait for a tuple, it releases
> the page context lock and then gets the lmgr lock representing the
> target tuple, with either a shared or exclusive lock mode as
> appropriate.  After it gets that, it waits on the current tuple holder
> as in your patch.  After it gets that, it updates the tuple state as
> needed, and only then releases the lmgr lock.
> 
> A backend that thinks it can join an existing shared locker also has to
> get the lmgr lock in the same way, so that it will block if there is
> a pending exclusive locker.

Sounds like an excellent plan to me.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)