Re: Behavior of shared/exclusive row locks

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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