Maxlocks

Karl & Betty Schendel  wrote:
> At 1:43 PM -0700 4/23/99, Melvin_J_Joe_Schmidt@RL.gov wrote:
> >E_DM9063_LK_TABLE_MAXLOCKS       Current logical lock count ( 11 ) on
> >resource qchistory in database lims3 exceeds the specified maximum ( 10
> >). Escalating to table level lock.
> >
> >..... Everything
> >I read in the manuals says to increase the maxlocks. Where are the
> >settings for maxlocks set at?
> 
> You set it programmatically with the SET command.  In 2.0, there is
> a configuration default in config.dat that you can set.  Alas, I don't
> recall if this was ever back-ported to 1.2.  You can try putting a
> line for ii..dbms.*.system_maxlocks into your config.dat,
> or you can set the ING_SET environment variable.

Something to keep in mind.  Even with maxlocks set Ingres will
take a table level lock if the optimizer thinks that the number of
locks will exceed maxlocks.  The way to force Ingres to attempt
to use the page level locks is with "set lockmode session where
maxlocks = X, level = page".

This is really to test and prove by setting lock_trace, setting 
maxlocks (without level= page being specified), selecting * 
from a table, starting a new session, set maxlocks 
(with level = page), and selecting * from a table.

Chip Nickolett              ChipN@Comp-Soln.com
Comprehensive Consulting Solutions, Inc.  (www.Comp-Soln.com)
Phone:  414-544-9954  Fax: 414-544-1236

Vice President - NAIUA  ( Vice_President@NAIUA.org )




> I want to increase maxlocks installation wide, beacuse
> i am getting lots of lock escalation.Right now it is
> set to default of 10.
> 
> i want to set it to 15.
> 
> my questions are.
> 
> 1. How to set it..
> 2. What will be impact of this change.
> 
> lock escalaton to table lavel is occuring at mostly
> 11. will it be advisible to set to 12 instead of 15.
> not to waste to many lock resources.
> 
> I am already using ING_SET to set readlock statement.
> can I use ING_SYSTEM_SET for this purpose.
> 
> Comments and suggestions are highly appericiated.
> 
> Thanks
> 
> ajay



Hi Ajay,

    Quite correct, ING_SYSTEM_SET is the way to go to set this up as it
    applies globally and will be picked up by all connections to the server
    not just SQL/ISQL sessions.

    You can get ING_SYSTEM_SET to read a file so that you can easialy
    incorporate more than one set statement. I normally use this feature, even
    if I only have the one set statement.

    eg.
    ingsetenv  ING_SYSTEM_SET "include //global"

    The file //global contains the line:
    set lockmode session where maxlocks = 15;

    Only you can tell what an acceptable value of maxlocks will be for your
    installation. For instance in mine I allow any session to take 2000 locks
    and have maxlocks set to 990. However, I would be very surprised if
    lifting from 10 to 12 or 15 would buy you a lot. I'd suggest setting the
    value to something about half to three-quarters of your maximum number of
    locks per transaction figure. Then monitor the locking situation and see
    what shakes loose.

    Martin Bowes
Ingres Q & A
Back to William's Home Page
Email William