Let me see if I understand what MVCC(Multi-level concurrency control
is). I looked in "Transaction Processing: Concepts and Techniques",
and saw some mention on pages 435-437, but not much more.
In MVCC, SELECT's do not share lock the table, allowing UPDATE's during
the SELECT. This is done by having the SELECT sequential scan look at
rows that are committed with transaction ids less than their own, or
superseded rows that have a superseded id greater than their own. The
only lock a SELECT does it to prevent a vacuum during its table scan.
My assumption is table writes still require an exclusive lock, but
because SELECT does not need a lock, both can occur at the same time.
(Sounds like my deadlock and lock queue code may need tweaking.)
Your stated problem is that someone in a transaction doing a SELECT is
not getting a shared lock on the rows he is selecting, so they could
change while inside the transaction. This is a valid concern.
Usually, doing the SELECT FOR UPDATE, even though you are not going to
update the table is used. You are suggesting SELECT FOR SHARE LOCK, but
because SELECT's don't need a lock anymore, isn't that the same as a FOR
UPDATE in an MVCC system? Is the problem that SHARE LOCK does not
modify the tuple, so it is harder to lock the rows?
> As you can see in old lock.c postgres supposed to do
> row --> table lock escalation if #rows locked in
> table equal 10 - very simple and bad -:)
>
> I wouldn't like to care about # of SHARE LOCKed rows,
> at least in 6.5. Actually, this number shouldn't be
> too big. In the case of referential integrity, FOR SHARE LOCK
> should be used only on primary table and only if user
> inserts/updates foreign table, but primary key is unique...
I think lock escalation is nice. Locking every row makes for lock
resource problems. I would recommend locking a single row, and if a
second row needs to be locked, just escalate to lock the whole table...
if that can be done. This would seem to be the most reasonable and
easiest to do.
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
