SIREAD lock versus ACCESS EXCLUSIVE lock

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Dan Ports" <drkp(at)csail(dot)mit(dot)edu>,<pgsql-hackers(at)postgresql(dot)org>
Subject: SIREAD lock versus ACCESS EXCLUSIVE lock
Date: 2011-04-27 19:59:19
Message-ID: 4DB82F47020000250003CF6E@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Composing my rather long-winded response to Simon got me thinking --
which just led me to realize there is probably a need to fix another
thing related to SSI.

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. :-(

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.

We're pretty aggressive about cleaning up SIREAD locks as soon as
allowable after transaction completion, so this probably wouldn't
happen very often.

-Kevin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2011-04-27 20:10:37 Re: pgsql: Fix pg_size_pretty() to avoid overflow for inputs close to INT64
Previous Message Greg Stark 2011-04-27 19:46:26 Re: "stored procedures" - use cases?