Re: [HACKERS] FOR SHARE LOCK clause ? - Mailing list pgsql-hackers

On Tue, 5 Jan 1999, Bruce Momjian wrote:

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

Making two assumption here...first is that your explanation of MVCC is
correct, second one being that my understanding of your explannation is
correct...

If you are going to set the 'table lock' at 2...why not just do the table
lock period?  From what youexplain above, a table lock won't affect a
read, only other writes...?

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org