Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Unanswered: locks data at snapshot


    I am using DB2 8.1.3 on unix AIX 5.2, with 8 GB memory

    since we used this machine we have this following locks ,

    from snapshot :

    Locks held currently = 1
    Lock waits = 67609
    Time database waited on locks (ms) = 5730
    Lock list memory in use (Bytes) = 18446744073705157496
    Deadlocks detected = 0
    Lock escalations = 21
    Exclusive lock escalations = 21
    Agents currently waiting on locks = 0
    Lock Timeouts = 98
    Number of indoubt transactions = 0

    and any applications have this :

    Time application waited on locks (ms) = 18446744073709551614
    Time application waited on locks (ms) = 18446744073709551213
    Time application waited on locks (ms) = 18446744073709550251

    the configurations parameter values is:

    Max storage for lock list (4KB) (LOCKLIST) = 128
    Lock timeout (sec) (LOCKTIMEOUT) = 10

    at the db2diag log we got msgs like this:

    ADM5502W The escalation of "1011" locks on table "SYSIBM .SYSCOLDIST" to lock
    intent "X" was successful.

    can any one tell me if this is a problem and what i need to do.
    in our applicathion log i dont see errors about this.


  2. #2
    Join Date
    Sep 2002
    All those big numbers i.e. "lock list memory in use" do not translate into any real values going by the memory allocated to the server. Try resetting the monitor and collect data again. If still no luck contact IBM Support.


  3. #3
    Join Date
    Feb 2002
    It seems you need to increase your LOCKLIST.

    My be you can set this to 1000.

  4. #4
    Join Date
    Mar 2004
    An important concept that can have a significant impact on concurrency is lock
    escalation, which is the process of replacing large number of row locks with a
    single table lock. Escalation occurs from row locks to a table lock when the
    number of locks held exceed the thresholds defined by the database
    configuration parameters locklist and maxlocks.

    locklist parameter specifies the maximum amount of storage available to store all the locks concurrently held within a database.

    maxlocks is the percentage of locks held by a single application, after that the locks escalation also starts.

    DB2 32-bit mode 72 bytes First lock on an object
    36 bytes Subsequent locks on an object

    DB2 64-bit mode 112 bytes First lock on an object
    56 bytes Subsequent locks on an object

    Compute the average number of locks per application (ANLA) as:
    ANLA = (Locks held currently / Applications connected currently)

    Choose a locklist value between the following lower and upper limits:
    Lower limit can be computed as:

    ((ANLA * (36 or 56 bytes) * maxappls) / 4096)

    Upper limit can be computed as:
    ((ANLA * (72 or 112 bytes) * maxappls) / 4096)

    also reset the monitor so that the snapshot will give you some meaning ful result
    also remember that locklist at snapshot is a guage not a high water mark.

    try to tune your applications by using any insert/update and delete at the end of work and committing every now and then.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts