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

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.

Yes, I use Oracle documentation to learn about MVCC.
Nevertheless, thank you for this book - there are so many
ideas there!

> 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

Only one running transaction can mark row for update, SHARE LOCK
could be acquired by many transactions and so, using
referential integrity for example, many transactions could
work with the same foreign key simultaneously.

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

Easiest to do is don't worry about # of locks -:)
Let's be on this way for 6.5

Vadim