> 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?
I hate to reply to my own posting, but I must.
I have just read the Date book, Introduction to Database Systems about
MVCC, and it confirms my above posting. Date states the MVCC
advantages:
* Reads are never delayed(in particular, they are not delayed by any concurrent long transaction)
* Reads never delay updates(in particular, they do not delay any concurrent long transaction)
* It is never necessary to roll back a read-only transaction
* Deadlock is possible only between update transactions
This is an amazing leap forward.
-- 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
