Re: SIREAD lock versus ACCESS EXCLUSIVE lock

On Wed, Apr 27, 2011 at 02:59:19PM -0500, Kevin Grittner wrote:
> For correct serializable behavior in the face of concurrent DDL
> execution, I think that a request for a heavyweight ACCESS EXCLUSIVE
> lock might need to block until all SIREAD locks on the relation have
> been released. Picture, for example, what might happen if one

I think you're correct about this, but also agree that it would be
reasonable to document the limitation for now and punt on a fix until
9.2.

On Wed, Apr 27, 2011 at 04:09:38PM -0500, Kevin Grittner wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > Hmm, could we upgrade all predicate locks to relation-level
> > predicate locks instead?
>
> Tied to what backend? This only comes into play with transactions
> which have committed and are not yet able to release their predicate
> locks because of overlapping READ WRITE serializable transactions
> which are still active. Until that point the ACCESS SHARE lock (or
> stronger) is still there protecting against this.

I think Heikki was suggesting to upgrade to relation-level SIREAD
locks. This seems like it would work, but might cause a lot of aborts
immediately afterwards. I do wonder if it might be necessary to upgrade
index locks to relation-level locks on the heap relation, in cases of
dropping the index.

> One way we could push this entirely into the heavyweight locking
> system would be for a committing transaction with predicate locks to
> somehow cause all overlapping read write serializable transactions
> which are still active to acquire an ACCESS SHARE lock on each
> relation for which the committing transaction has any predicate
> lock(s).

I'm not entirely clear on how this would work, but it sounds like it
could also have a significant performance cost.

> As an alternative, perhaps we could find a way to leave the relation
> locks for a serializable transaction until it's safe to clean up the
> predicate locks? They could be downgraded to ACCESS SHARE if they
> are stronger. They would need to survive beyond not only the commit
> of the transaction, but also the termination of the connection.
> They would need to be immune to being chosen as deadlock victims.

This sounds like it would require major changes to the heavyweight lock
manager. There's already a notion of keeping locks past backend
termination for 2PC prepared transactions, but it would be hard to
apply here.

The most practical solutions I see here are to, when acquiring an
AccessExclusive lock, either wait for any associated SIREAD locks to go
away, or to promote them to relation level.

Dan

--
Dan R. K. Ports MIT CSAIL http://drkp.net/