Skip site navigation (1) Skip section navigation (2)

Re: SIREAD lock versus ACCESS EXCLUSIVE lock

From: Dan Ports <drkp(at)csail(dot)mit(dot)edu>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org,Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Subject: Re: SIREAD lock versus ACCESS EXCLUSIVE lock
Date: 2011-04-27 22:11:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

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

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 R. K. Ports              MIT CSAIL      

In response to


pgsql-hackers by date

Next:From: Noah MischDate: 2011-04-27 22:14:31
Subject: Re: pg_upgrade automatic testing
Previous:From: Peter EisentrautDate: 2011-04-27 21:57:28
Subject: Re: make world fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group