Re: SIREAD lock versus ACCESS EXCLUSIVE lock

On Wed, Apr 27, 2011 at 8:59 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> 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
> transaction acquires some predicate locks, then commits (releasing
> its heavyweight lock on the table), and before concurrent READ WRITE
> transactions complete there is a CLUSTER on the table. Or a DROP
> INDEX.  :-(

Sorry, I can't picture it. What will happen?

> Both require an ACCESS EXCLUSIVE lock.  Since an active transaction
> would already have an ACCESS SHARE lock when acquiring the SIREAD
> locks, this couldn't block in the other direction or with an active
> transaction.  That means that it couldn't cause any deadlocks if we
> added blocking to the acquisition of an ACCESS EXCLUSIVE based on
> this.
>
> If we don't do this I don't think that there is a more serious
> impact than inaccurate conflict detection for serializable
> transactions which are active when these operations are performed.
> Well, that and the possibility of seeing SIRead locks in the
> pg_locks view for indexes or tables which no longer exist.  So far I
> don't see any crash modes or effects on non-serializable
> transactions.  If this change is too destabilizing for this point in
> the release we could document it as a limitation and fix it in 9.2.

I don't think this should wait for 9.2

It either works, or it doesn't. Putting caveats in there will just
detract from people's belief in it.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services