Re: SIREAD lock versus ACCESS EXCLUSIVE lock

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Simon Riggs" <simon(at)2ndQuadrant(dot)com>
Cc: "Dan Ports" <drkp(at)csail(dot)mit(dot)edu>, "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SIREAD lock versus ACCESS EXCLUSIVE lock
Date: 2011-04-29 22:04:10
Message-ID: 4DBAEF8A020000250003D07C@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> This'll take some study.

I've gone through the list of commands in the development docs with
an eye toward exposing anything else we might have missed in dealing
with the SSI predicate locking. Some of this needs further
research, but I'm posting what I have so far so that people have a
chance to argue about what I think needs doing or to point out
anything else they can think of which I'm still missing. And, of
course, so people know where we are with it.

I tested file_fdw at the serializable isolation level and found that
no predicate locks were taken and no problems showed up. I think
that's all correct -- I don't see how we can expect to include FDW
data in serializability.

I haven't dug into ALTER INDEX enough to know whether it can ever
cause an index to be rebuilt. If so, we need to treat it like DROP
INDEX and REINDEX -- which should change all predicate locks of any
granularity on the index into relation locks on the associated
table.

CLUSTER or an ALTER TABLE which causes a rewrite should change all
predicate locks on the table and all indexes into relation locks on
the associated table. (Obviously, an existing relation lock on the
table doesn't require any action.)

TRUNCATE TABLE and DROP TABLE should generate a rw-conflict *in* to
the enclosing transaction (if it is serializable) from all
transactions holding predicate locks on the table or its indexes.
Note that this could cause a transactions which is running one of
these statements to roll back with a serialization error. This seems
correct to me, since these operations essentially delete all rows.
If you don't want the potential rollback, these operations should be
run at another isolation level. The difference between these two
statements is that I think that TRUNCATE TABLE should also move the
existing predicate locks to relation locks on the table while DROP
TABLE (for obvious reasons) should just delete the predicate locks.

DROP DATABASE should quietly clean up any predicate locks from
committed transactions which haven't yet hit their cleanup point
because of overlapping transactions in other databases.

DROP OWNED or DROP SCHEMA could CASCADE to some of the above, in
which case the above rules would apply.

I need to do some testing with Large Objects to see what state those
are in with SSI, unless someone else has already looked at that.

SAVEPOINTs have been considered and there is a lot of coding to try
to handle them correctly, but they probably merit a bit more
attention and testing. On some quick testing everything seemed to
be in line with previous discussions and with what seems logical to
me, but our SSI regression tests in src/test/isolation lack any
coverage for them and I don't know how much others have beat up on
them. At a minimum we should add a couple tests.

I couldn't find anything else which hadn't already been considered
and covered.

More to come as I finish investigating.

-Kevin

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2011-04-29 23:28:09 Re: EXPLAIN Node Docs?
Previous Message Robert Haas 2011-04-29 21:59:45 Re: branching for 9.2devel