Ok, in multi-version systems readers never lock
selected rows and so never block writers. Nice but
cause problems in some cases: if you want
to implement referential integrity at the
application level then you'll have to use
LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
prevent updation of primary keys etc. Not so good...

So, what about FOR SHARE LOCK clause in SELECTs?
Like FOR UPDATE clause, FOR SHARE LOCK clause
will lock selected rows, but in _share_ mode.
This is some kind of read-locking on demand.

Disadvantages:

1. FOR SHARE LOCK will use lock manager to
   lock rows, but lmgr can't handle "too many" locks.
   (note that UPDATE, DELETE & SELECT FOR UPDATE use only
   one entry in lmgr table for ALL updated/marked_for_update
   rows).

2. UPDATE/DELETE will go slower than now: server will have to
   exclusively lock (using lmgr) each row being updated, update/delete
   it and release lmgr' lock after that (note that currently
   lmgr locking is not used for rows). On the other hand, 
   SELECT FOR SHARE LOCK could mark locked rows in buffer
   (t_infomask |= HEAP_MARKED_FOR_SHARE) and so UPDATE/DELETE
   could acquire lmgr lock only if row is marked...

Comments ?

Vadim

Tue, 05 Jan 1999 20:37:58 +0700
Vadim Mikheev <vadim@krs.ru>
FOR SHARE LOCK clause ?
> So, what about FOR SHARE LOCK clause in SELECTs?
> Like FOR UPDATE clause, FOR SHARE LOCK clause
> will lock selected rows, but in _share_ mode.
> This is some kind of read-locking on demand.
> 1. FOR SHARE LOCK will use lock manager to
>    lock rows, but lmgr can't handle "too many" locks.
>    (note that UPDATE, DELETE & SELECT FOR UPDATE use only
>    one entry in lmgr table for ALL updated/marked_for_update
>    rows).
> 2. UPDATE/DELETE will go slower than now: server will have to
>    exclusively lock (using lmgr) each row being updated, update/delete
>    it and release lmgr' lock after that (note that currently
>    lmgr locking is not used for rows). On the other hand,
>    SELECT FOR SHARE LOCK could mark locked rows in buffer
>    (t_infomask |= HEAP_MARKED_FOR_SHARE) and so UPDATE/DELETE
>    could acquire lmgr lock only if row is marked...

If one did not specify FOR SHARE LOCK then the performance would be
similar to the current performance? Then if you want to use the feature,
you pay the performance penalty. Or do you think that the checking
should/will happen irrespective of any query settings?

Also, my commercial Ingres system would escalate row-level locks to
page- and table-level locks when the number of row-level locks exceeded
a (settable) threshold. That seems like a nice compromise between
features and performance, but it can lead to deadlock during the lock
escalation...

                     - Tom

Tue, 05 Jan 1999 14:55:40 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] FOR SHARE LOCK clause ?
"Thomas G. Lockhart" wrote:
> 
> > So, what about FOR SHARE LOCK clause in SELECTs?
> > Like FOR UPDATE clause, FOR SHARE LOCK clause
> > will lock selected rows, but in _share_ mode.
> > This is some kind of read-locking on demand.
> > 1. FOR SHARE LOCK will use lock manager to
> >    lock rows, but lmgr can't handle "too many" locks.
> >    (note that UPDATE, DELETE & SELECT FOR UPDATE use only
> >    one entry in lmgr table for ALL updated/marked_for_update
> >    rows).
> > 2. UPDATE/DELETE will go slower than now: server will have to
> >    exclusively lock (using lmgr) each row being updated, update/delete
> >    it and release lmgr' lock after that (note that currently
> >    lmgr locking is not used for rows). On the other hand,
> >    SELECT FOR SHARE LOCK could mark locked rows in buffer
> >    (t_infomask |= HEAP_MARKED_FOR_SHARE) and so UPDATE/DELETE
> >    could acquire lmgr lock only if row is marked...
> 
> If one did not specify FOR SHARE LOCK then the performance would be
> similar to the current performance? Then if you want to use the feature,
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Yes, if FOR SHARE LOCK will mark locked row in buffer
(and so - force buffer write)...

> you pay the performance penalty. Or do you think that the checking
> should/will happen irrespective of any query settings?
> 
> Also, my commercial Ingres system would escalate row-level locks to
> page- and table-level locks when the number of row-level locks exceeded
> a (settable) threshold. That seems like a nice compromise between
> features and performance, but it can lead to deadlock during the lock
> escalation...

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

Vadim

Tue, 05 Jan 1999 22:34:19 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] FOR SHARE LOCK clause ?
> Ok, in multi-version systems readers never lock
> selected rows and so never block writers. Nice but
> cause problems in some cases: if you want
> to implement referential integrity at the
> application level then you'll have to use
> LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> prevent updation of primary keys etc. Not so good...


I was wondering if there is another perspective
to look upon this problem.

Assertion:

The primary difference between "primary key" and
"unique key" is that primary keys are _never_
updated (during transaction processing) where 
unique keys may be. 

Question:

It seems that the techinical solution here may
not be better locking, but rather a mechinism
to prevent updates on primary keys unless the
entire table is locked, or some other dramatic
gesture for non-transaction processing.

This leaves the issue of delete looming, so 
mabye the suggestion won't help.

Clark

Tue, 05 Jan 1999 15:54:29 +0000
Clark Evans <clark.evans@manhattanproject.com>
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?


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

-- 
  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, Pennsylvania 19026

Tue, 5 Jan 1999 14:32:24 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?
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 


Tue, 5 Jan 1999 16:10:50 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
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, Pennsylvania 19026

Tue, 5 Jan 1999 15:42:35 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?
> > 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...?

Good point.  I am assuming he is doing some kind of row-level locking
for shared and write locks.  I can only guess this from his statement
that shared locking of every row would be a problem.

Sounds like my explaination may be wrong, because it is saying he has
some kind of row-locking going, perhaps for writes.  Maybe he is using
the fact that if a writer is going to update a row that has a
superceeded transaction id that is marked 'in progress' the writer has
to wait for the transaction to finish.  If you do this, muliple writers
can update at the same time, making MVCC better than row-level locking
systems.

Readers don't block writers, and multiple writers can write as long as
they are not touching the same rows.

In this scenario, shared locks are tricky, because the above system does
not work.  You have to do some explicit locking, because reading does
not set anything on the row.

Vadim is sleeping now, so I assume we will hear something from him this
evening.


-- 
  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, Pennsylvania 19026

Tue, 5 Jan 1999 15:58:34 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?]
OK, I have retrieved Vadim's original proposal for LLL from July.  (No
wonder I didn't remember it.)

He basically describes the MVCC system at the bottom, and at the top, I
am discussing a way to avoid a mapping of SCN's to XID's.  There are
other postings that finally resolved the issue.  At least Vadim didn't
say I was way off, so somehow he resolved it.

This is an great benefit.  We don't have to apologize about table-level
locking anymore.  I didn't think it could be done, and Vadim's changes
kind of came as a surprise to me, probably because I didn't understand
the ramifications when discussing it in July.

-- 
  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, Pennsylvania 19026

Re: [HACKERS] proposals for LLL, part 1

  ------------------------------------------------------------------------

   * From: Bruce Momjian <maillist@candle.pha.pa.us>
   * To: vadim@krs.ru (Vadim Mikheev)
   * Subject: Re: [HACKERS] proposals for LLL, part 1
   * Date: Thu, 16 Jul 1998 12:14:51 -0400 (EDT)

  ------------------------------------------------------------------------

I am retaining your entire message here for reference.

I have a good solution for this.  It will require only 4k of shared
memory, and will have no restrictions on the age or number of
transactions.

First, I think we only want to implement "read committed isolation
level", not serialized.  Not sure why someone would want serialized.

OK, when a backend is looking at a row that has been committed, it must
decide if the row was committed before or after my transaction started.
If the transaction commit id(xmin) is greater than our current xid, we
know we should not look at it because it is for a transaction that
started after our own transaction.

The problem is for transactions started before our own (have xmin's less
than our own), and may have committed before or after our transaction.

Here is my idea.  We add a field to the shared memory Proc structure
that can contain up to 32 transaction ids.  When a transaction starts,
we spin though all other open Proc structures, and record all
currently-running transaction ids in our own Proc field used to store up
to 32 transaction ids.  While we do this, we remember the lowest of
these open transaction ids.

This is our snapshot of current transactions at the time our transaction
starts.  While analyzing a row, if it is greater than our transaction
id, then the transaction was not even started before our transaction.
If the xmin is lower than the min transaction id that we remembered from
the Proc structures, it was committed before our transaction started.
If it is greater than or equal to the min remembered transaction id, we
must spin through our stored transaction ids.  If it is in the stored
list, we don't look at the row, because that transaction was not
committed when we started our transaction.  If it is not in the list, it
must have been committed before our transaction started.  We know this
because if any backend starting a transaction after ours would get a
transaction id higher than ours.

Comments?

> Ok, I'm not sure that LLL will appear in 6.4 but it's good time to
> discuss about it.
>
> First, PostgreSQL is multi-version system due to its
> non-overwriting storage manager. And so, first proposal is use
> this feature (multi-versioning) in LLL implementation.
>
> In multi-version systems access methods don't use locks to read
> consistent data and so readers don't block writers, writers don't
> block readers and only the same-row writers block writers. In such
> systems access methods returns snapshot of data as they were in
> _some_ point in time. For read committed isolation level this
> moment is the time when statement began. For serialized isolation
> level this is the time when current transaction began.
>
> Oracle uses rollback segments to reconstract blocks that were
> changed after statement/transaction began and so statement sees
> only data that were committed by then.
>
> In our case we have to analyze tuple xmin/xmax to determine _when_
> corresponding transaction was committed in regard to the last
> transaction (LCX) that was committed when statement/transaction
> began.
>
> If xmin/xmax was committed before LCX then tuple
> insertion/deletion is visible to statement, else - not visible.
>
> To achieve this, the second proposal is to use special SCN -
> System Change Number (C) Oracle :) - that will be incremented by 1
> by each transaction commit. Each commited transaction will have
> corresponding SCN (4 bytes -> equal to sizeof XID).
>
> We have to keep XID --> SCN mapping as long as there is running
> transaction that is "interested" in XID: when transaction begins
> it will determine the first (the oldest) running transaction XID
> and this will be the minimum XID whose SCN transaction would like
> to know.
>
> Access methods will have to determine SCN for xmin/xmax only if
> FRX <= xmin/xmax <= LSX, where FRX is XID of first (oldest)
> running transactions and LSX is last started transaction - in the
> moment when statement (for read committed) or transaction (for
> serialized) began. For such xmin/xmax their SCNs will be compared
> with SCN determined in the moment of statement/transaction
> begin...
>
> Changes made by xmin/xmax < FRX are visible to
> statement/transaction, and changes made by xmin/xmax > LSX are not
> visible. Without xmin/xmax SCN lookup.
>
> For XID --> SCN mapping I propose to use the simplest schema:
> ordered queue of SCNs (or something like this) - i.e. keep SCNs
> for all transactions from the first one whose SCN could be
> required by some running transaction to the last started.
>
> This queue must be shared!
>
> The size of this queue and average number of commits/aborts per
> second will define how long transactions will be able to run.  30
> xacts/sec and 400K of queue will enable 30 - 60 minuts running
> transactions...
>
> Keeping queue in shared memmory may be unacceptable in some
> cases... mmap or shared buffer pool could be used to access queue.
> We'll see...
>
> Also note that Oracle has special READ ONLY transactions mode.
> READ ONLY transactions are disallowed to change anything in the
> database. This is good mode for pg_dump (etc) long running
> applications. Because of no one will be "interested" in SCN of
> READ ONLY transactions - such transactions can make private copy
> of the queue part and after this queue could be truncated...
>
> Having 4 bytes per SCN enable to use special values to mark
> corresponding transaction as running or aborted and avoid pg_log
> lookup when we need in both SCN and state of transaction.
>
> ...Well, it's time to sleep :)
>
> To be continued...
>
> Comments ?
>
> Vadim
>
>

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

  ------------------------------------------------------------------------

   * Prev by Date: How about re-moderating pgsql-announce?
   * Next by Date: Re: [HACKERS] How about re-moderating pgsql-announce?
   * Prev by thread: proposals for LLL, part 1
   * Next by thread: Re: [HACKERS] proposals for LLL, part 1
   * Index(es):
        o Date
        o Thread

Home | Main Index | Thread Index
Tue, 5 Jan 1999 20:30:03 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?]
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

Wed, 06 Jan 1999 10:47:47 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] FOR SHARE LOCK clause ?
> > 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

You mean just share-lock the whole table.  I agree.  It is a pretty rare
situation.


-- 
  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, Pennsylvania 19026

Tue, 5 Jan 1999 22:50:36 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?
Bruce Momjian wrote:
> 
> > > 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
> 
> You mean just share-lock the whole table.  I agree.  It is a pretty rare
> situation.

No. User may use LOCK TABLE IN SHARE MODE for this.
I propose SELECT FOR SHARE LOCK as alternative to
LOCK TABLE IN SHARE MODE and SELECT FOR UPDATE and
would like to share lock each row selected with
FOR SHARE LOCK clause in use. I don't know what's
real limitations of # locks, but I think that
a tens of locks is Ok.

Vadim

Wed, 06 Jan 1999 11:16:10 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] FOR SHARE LOCK clause ?
> Bruce Momjian wrote:
> > 
> > > > 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
> > 
> > You mean just share-lock the whole table.  I agree.  It is a pretty rare
> > situation.
> 
> No. User may use LOCK TABLE IN SHARE MODE for this.
> I propose SELECT FOR SHARE LOCK as alternative to
> LOCK TABLE IN SHARE MODE and SELECT FOR UPDATE and
> would like to share lock each row selected with
> FOR SHARE LOCK clause in use. I don't know what's
> real limitations of # locks, but I think that
> a tens of locks is Ok.

So you are going to shared lock every row.  And if a user does a
sequential scan of the entire table using SELECT FOR SHARE LOCK, he
shared locks every row.  Isn't he going to run out of locks?

-- 
  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, Pennsylvania 19026

Tue, 5 Jan 1999 23:25:31 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?
Bruce Momjian wrote:
> 
> >
> > 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...?
> 
> Good point.  I am assuming he is doing some kind of row-level locking
> for shared and write locks.  I can only guess this from his statement
> that shared locking of every row would be a problem.
> 
> Sounds like my explaination may be wrong, because it is saying he has
> some kind of row-locking going, perhaps for writes.  Maybe he is using
> the fact that if a writer is going to update a row that has a
> superceeded transaction id that is marked 'in progress' the writer has
> to wait for the transaction to finish.  If you do this, muliple writers
> can update at the same time, making MVCC better than row-level locking
> systems.

Yes. When transaction begins it places exclusive lock
for transaction ID in pseudo-table (no wait because of ID
is unique). If other writer sees that t_xmax is valid 
(and not committed/aborted) transaction ID then it tries 
to place share lock for t_xmax in this pseudo-table.
If t_xmax is running then concurrent writer will wait for
t_xmax commit/abort when exclusive lock for t_xmax
is released. With this technique writers use single
lmgr entry for all updated rows - just to let other
the-same-row writers know when transaction commits/aborts.

> 
> Readers don't block writers, and multiple writers can write as long as
> they are not touching the same rows.

Yes.

> In this scenario, shared locks are tricky, because the above system does
> not work.  You have to do some explicit locking, because reading does
> not set anything on the row.

Yes, but this will be user' decision to use FOR SHARE LOCK
(to lock rows explicitly) or not.

Vadim

Wed, 06 Jan 1999 11:25:47 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] FOR SHARE LOCK clause ?]
Bruce Momjian wrote:
> 
> > > >
> > > > Easiest to do is don't worry about # of locks -:)
> > > > Let's be on this way for 6.5
> > >
> > > You mean just share-lock the whole table.  I agree.  It is a pretty rare
> > > situation.
> >
> > No. User may use LOCK TABLE IN SHARE MODE for this.
> > I propose SELECT FOR SHARE LOCK as alternative to
> > LOCK TABLE IN SHARE MODE and SELECT FOR UPDATE and
> > would like to share lock each row selected with
> > FOR SHARE LOCK clause in use. I don't know what's
> > real limitations of # locks, but I think that
> > a tens of locks is Ok.
> 
> So you are going to shared lock every row.  And if a user does a
> sequential scan of the entire table using SELECT FOR SHARE LOCK, he
> shared locks every row.  Isn't he going to run out of locks?

I would like to work with this issue after 6.5 and writes
some notes about FOR SHARE LOCK limitations/problems.

Vadim

Wed, 06 Jan 1999 11:28:27 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] FOR SHARE LOCK clause ?
Clark Evans wrote:
> 
> > Ok, in multi-version systems readers never lock
> > selected rows and so never block writers. Nice but
> > cause problems in some cases: if you want
> > to implement referential integrity at the
> > application level then you'll have to use
> > LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> > prevent updation of primary keys etc. Not so good...
> 
> I was wondering if there is another perspective
> to look upon this problem.
> 
> Assertion:
> 
> The primary difference between "primary key" and
> "unique key" is that primary keys are _never_
                       ^^^^^^^^^^^^^^^^^^^^^^^^
> updated (during transaction processing) where
  ^^^^^^^
> unique keys may be.

1. Is this standard requirement?
2. Note that foreign keys may reference unique key,
   not just primary one...
3. I told about implementing referential
   integrity _at_the_application_level_, not by the
   DB system itself - it's up to the user decide
   what's allowed and what's not, in this case.

> Question:
> 
> It seems that the techinical solution here may
> not be better locking, but rather a mechinism
> to prevent updates on primary keys unless the
> entire table is locked, or some other dramatic
> gesture for non-transaction processing.
> 
> This leaves the issue of delete looming, so
> mabye the suggestion won't help.

Yes. 

Vadim

Wed, 06 Jan 1999 11:40:53 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] FOR SHARE LOCK clause ?
> > So you are going to shared lock every row.  And if a user does a
> > sequential scan of the entire table using SELECT FOR SHARE LOCK, he
> > shared locks every row.  Isn't he going to run out of locks?
> 
> I would like to work with this issue after 6.5 and writes
> some notes about FOR SHARE LOCK limitations/problems.

OK, just checking.  You can't use the xid, so I see the problem with
shared-locking.  No way to know which rows were seen by SELECT, so no
way to shared-lock them without an explicit lock.

With Informix, UPDATE automatically locks every row, and you quickly run
out of locks.  You have to explicitly lock the table in EXCLUSIVE MODE
in a transaction to prevent your UPDATE from running out of locks on a
large transaction.  And you can still run out of log space or generate a
'large transaction' errror because the log got full before the
transaction finished.  With MVCC, these are not problems, and FOR SHARED
LOCK is just a special thing people can enable for special cases.

-- 
  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, Pennsylvania 19026

Tue, 5 Jan 1999 23:45:35 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?
Vadim Mikheev wrote:
> Clark Evans wrote:
> > > Ok, in multi-version systems readers never lock
> > > selected rows and so never block writers. Nice but
> > > cause problems in some cases: if you want
> > > to implement referential integrity at the
> > > application level then you'll have to use
> > > LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> > > prevent updation of primary keys etc. Not so good...
> >
> > I was wondering if there is another perspective
> > to look upon this problem.
> >
> > Assertion:
> >
> > The primary difference between "primary key" and
> > "unique key" is that primary keys are _never_
>                        ^^^^^^^^^^^^^^^^^^^^^^^^
> > updated (during transaction processing) where
>   ^^^^^^^
> > unique keys may be.
> 
> 1. Is this standard requirement?

A DBA at Ford that I had many afternoon chats with
wished he could make the Oracle database prevent
updates of primary keys, short of a full table 
lock -- which is a privilige.  I'm sure that there
are others that might disagree.

The other thing that the DBA said he'd love was
a "garbage collector".  Where a delete on row would
only "hide" the row after all constraint checks 
succeeded.  In this way, data referenced by systems
that are not attached to the database have a chance
to be checked for data integrety concerns.

Although you may be able to find DBA's that would
argue about these points, the idea that primary
keys are updated only on rare exception is a solid 
modeling practice (it's one of Oracle's Ten Commandments).

> 2. Note that foreign keys may reference unique key,
>    not just primary one...

You may be able to do it... but I'm not sure that
it makes sence.  Also, unique keys can have NULLS,
primary keys cannot have any NULL allowable columns

The idea of a primary key is to identify column(s)
as a constant pointer to the object in question,
so that other objects can use that pointer.

The idea of a unique index is to enforce 
constraints that a combination of columns must
be unique.  The mechinism does not necessarily
mean that the columns are "constant", and thus
make good pointers.  For instance, a full name 
may very well be unique in a small company - if
this assumption is made in the reporting code, 
then a unique key is warranted, with clear 
documentation explaining that the reporting
code assumes this fact.  However, it would 
be a bad idea to use the full name as a 
pointer, as a marriage could cause havoc.

> 3. I told about implementing referential
>    integrity _at_the_application_level_, not by the
>    DB system itself - it's up to the user decide
>    what's allowed and what's not, in this case.

I think you are right here, although allowing the
user to make an application level decision and
then configure the database to automagically 
enforce this decision is golden.

More e-mail to follow...

Wed, 06 Jan 1999 06:49:00 +0000
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] FOR SHARE LOCK clause ?
Vadim Mikheev wrote:
> Clark Evans wrote:
> > > Ok, in multi-version systems readers never lock
> > > selected rows and so never block writers. Nice but
> > > cause problems in some cases: if you want
> > > to implement referential integrity at the
> > > application level then you'll have to use
> > > LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> > > prevent updation of primary keys etc. Not so good...

I'm trying to understand.  Please excuse my ignorance
of database implementation issues.  From a application
developers's perspective:

Suppose that I have three tables: ORDER, PRODUCT, 
and ORDER_LINE. ORDER_NO is the primary key of ORDER,
PRODUCT_NO is the primary key for PRODUCT, and ORDER_NO
and PRODUCT_NO are foreign keys in ORDER_LINE.

Now picture a data entry person with an order entry
screen, with order information above, a grid with
a row for each order line, and a drop down list box
for each possible product.

The problem you identified above would happen if
while Lucy was inserting order lines for ORDER_NO=100
PRODUCT_NO=230, someone else changes the order_no
from 100 to 101 in the order table _or_ if someone 
else deletes from product where product_no = 230.

There are a number of solutions to this pattern.

a) Pessimistic -  In this pattern, when a child is
about to be edited or added, the parent record is
locked.   When the updates to the child table are
done, a commit frees the parent.  This is coupled
with code to check to see if the row is locked 
when it is read, if so, then the parent and the
child are brought back "read-only" with a status
of "being updated..."   This solution is great when
the foreign key points to a "natural" parent, such
as an order pointing to an order_line... where deleting
the order would cascade to the order lines.  The 
solution works wonderfully when the parent and child 
are on the same screen.

b) Optimistic - In this pattern, when a child is 
inserted/updated and a parent key is not found, 
an exception is thrown by the database - "foreign
key violation".  In this case, the client application
verifies which foreign key it was, and informs the
user about the failure: "Oval Vase (203) is no
longer a valid product.  Order line addition
canceled.  Updating product list..."  This solution
works well when the object being referenced is 
maintained by a different group or is configured
by a different part of the application.  In this
case, you wouldn't want the product database to
be constantly locked up as people are ordering 
products... fixing a product name would be a pain!
In general, if deleting the parent would not cascade
to the child, this pattern is the best one to use.
This solution works great if the parent/child are
on different screens.

In any case, I'm not really sure what this locking
in share mode would get you... could you explain
in the context of the above examples.

Thanks!

Clark

Wed, 06 Jan 1999 07:24:27 +0000
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] FOR SHARE LOCK clause ?
> In any case, I'm not really sure what this locking
> in share mode would get you... could you explain
> in the context of the above examples.

I will take a stab at it.

Locking in shared mode would cause any row read by the SELECT to be
remain unmodified until its transaction completes.  Other SELECTs can
read it, but writer have to wait and can not modify it.

-- 
  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, Pennsylvania 19026

Wed, 6 Jan 1999 02:43:48 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] FOR SHARE LOCK clause ?