Page locking problem

From: Roy Hann 
Subject: Re: Page locking problem

On Sat, 23 Mar 1996, Naresh Malhotra wrote:

> one should not be surprised if modify to btree also does not solve the
> problem, that is because if a page has the capacity to hold more than one
> record, user no 2 might have to wait till the user no 1 has committed the
> transaction. the reasons for the possible wait are two :

Perfectly true, although we have no idea how the table is indexed.  From
the description of the application I would not be surprised if it isn't;
the vendor could be counting on records appearing in the heap in
chronological order like the error log file.  Changing to any other 
structure might also break things--possibly including the right to get 
support from the vendor.

> you should also check the ingres errlog.log file, if it is escalating to 
> table level lock, if so you must in any case increase the number of locks per
> transaction and the total number of locks.

Such escalation is not reported in the log file unless it results in
deadlock.  Also, even if lock escalation is a problem, raising the number
of locks per transaction won't fix it, you must raise the number of
page locks allowed per table (ie SET LOCKMODE ON table WHERE MAXLOCKS = 
n).  It may also be necessary to add lock resources to the system but 
that is not often required unless MAXLOCKS is given a really big value.  
IPM will tell you for sure.

========================================================================
Roy Hann                                   rhann@tnc.com
BKB Engineering Ltd.,                      NAIUA Member-at-Large
11211-76th Avenue,                 
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================


From: Roy Hann 

On Tue, 26 Mar 1996, Liam McCauley wrote:

> Funny, I'm sure I've seen lock escalation reported in the log file on a client
> site, and there was no deadlock.  That was with 6.4/05.

OK, OK, so _occasionally_ lock escalation is reported for some reason
other than deadlock, but 99,999 times out of 100,000 page level locks that
are converted to a table lock generate no report.  IPM displays a count of
the number of page locks escalated to a table lock.  You will see that
count go into the hundreds of thousands, even the millions, and no record
of an escalation in the log file.  I repeat: the log file is not the place
to look for evidence of lock escalation. 

> Also, it *is* the number of locks per transaction (set during iistartup -init)
> that you want to increase.  MAXLOCKS sets the max number of page level
> locks before escalation *per query*, not per transaction.  

Fair enough, if the reason for the escalation is that you hit the limit on
the number of locks per transaction.  But that limit is probably a limit
of a few hundred (or more) in most installations.  By contrast, the
default number of page locks on a table that will cause escalation is just
10.  You will almost always hit that limit first.  Again, IPM will show 
what's going on.

Raising the number of locks allowed per transaction will do no good at all
if you are escalating to a table level lock because the default number of
page locks allowed per table is so low.  Raise the number of locks per
transaction to 10,000 if you like; but if you take more than 10 page locks
on a table it will still escalate to a table level lock.  And it is really
easy to lock 10 pages.  For instance, note that traversing an overflow
chain causes every page in the chain to be locked as it is visited. 

Of course what all this boils down to is that there could be several
things going on in the original application (which I have long ago
forgotten about), and none of us necessarily knows what the right medicine
is right now, but it sure is fun to yap on about someone else's problems!
:-)

========================================================================
Roy Hann                                   rhann@tnc.com
BKB Engineering Ltd.,                      NAIUA Member-at-Large
11211-76th Avenue,                 
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================
Ingres Q & A
To William's Home Page
Email William