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@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