Re: [HACKERS] FOR SHARE LOCK clause ?

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026