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: 20110427221112.GC1432@csail.mit.edu (view raw or flat)
Thread:
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
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/

In response to

Responses

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-2014 The PostgreSQL Global Development Group