Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Question Unanswered: Lock Escalation Question

    This is for DB2 v7.1 on OS/390

    We have two batch processes A and B (both bound with CS and not using UR) running in parallel and accessing the same table (locksize set to row, lockmax set to system).

    Process A is doing updates to the table while B is only reading. When the locks/user or locks/table is exceeded, A tries to escalate.
    If B is still holding shared row locks, will process A:
    a) run into a timeout (-911) or
    b) would it try and keep acquiring more locks or
    c) would it fail with another message?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know the answer to your question, but it seems less likely that B would hold locks for a very long period of time since Cursor Stability is used. I would think that as soon as B moved off the current row and tried to fetch the next row, that A would step in with the tablespace lock. Of course if this were true, then B might timeout.

    But according the SQL Reference, "LOCKMAX specifies the maximum number of page, row, or LOB locks an application process can hold simultaneously in the table space." So if A is also using CS, then it seems less likely that it could acquire so many simultaneous locks on a tablespace that would cause it to escalate per the LOCKMAX setting. But I suppose it depends on the complexity of the SQL statements involved.

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    Originally posted by Marcus_A
    I don't know the answer to your question, but it seems less likely that B would hold locks for a very long period of time since Cursor Stability is used. I would think that as soon as B moved off the current row and tried to fetch the next row, that A would step in with the tablespace lock. Of course if this were true, then B might timeout.

    But according the SQL Reference, "LOCKMAX specifies the maximum number of page, row, or LOB locks an application process can hold simultaneously in the table space." So if A is also using CS, then it seems less likely that it could acquire so many simultaneous locks on a tablespace that would cause it to escalate per the LOCKMAX setting. But I suppose it depends on the complexity of the SQL statements involved.
    Thanks for the reply. Here's more information (I was able to go back and find the actual error on the log) - I've modified confidential information...:

    10.45.52 STC49012 DSNT376I -SSID PLAN=PlanNameA WITH 253
    253 CORRELATION-ID=0000*******
    253 CONNECTION-ID=Process A
    253 LUW-ID=###############
    253 THREAD-INFO=######:*:*:*
    253 IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=PlanNameB
    253 WITH
    253 CORRELATION-ID=0000*******
    253 CONNECTION-ID=Process B
    253 LUW-ID=###############
    253 THREAD-INFO=######:*:*:*
    253 ON MEMBER SSID
    253 .
    10.45.52 STC49012 DSNT501I -SSID DSNILMCL RESOURCE UNAVAILABLE 254
    254 CORRELATION-ID=0000*******
    254 CONNECTION-ID=Process A
    254 LUW-ID=*
    254 REASON 00C9008E
    254 TYPE 00000D01
    254 NAME 00000273.00000944

    I verified that the 00000273.00000944 does correspond to the DBID/ OBID from SYSIBM.SYSTABLES for the table under question.

    MVS/ QuickRef says that TYPE 00000D01 applied to the DBID/OBID (listed under v6 - nothing listed for v7).

    What does that imply though if the resource in contention was the DBID/OBID as indicated by TYPE...?
    Thanks
    Last edited by RMahajan; 09-23-03 at 20:16.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think it would be useful to see what value you set MAXLOCKS to at the system level. Also, what is the SQL statement on that table from A and B. Is it possbile that B escalated to a tablespace lock?

  5. #5
    Join Date
    Sep 2003
    Posts
    22
    Originally posted by Marcus_A
    I think it would be useful to see what value you set MAXLOCKS to at the system level. Also, what is the SQL statement on that table from A and B. Is it possbile that B escalated to a tablespace lock?
    We have Max locks per user @ 10000 and Max locks per table @ 1000. We know for sure that B did not cause any escalation - and the theory about escalation is actually that - a theory... Just wanted to know if it makes sense (it does and still doesn't to me somehow).

    I would expect some other error message (don't know what that would be).

    Thanks

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would assume that you had either:

    1. escalation to tablespace, or
    2. complex SQL statements that took so long they caused a timeout due to lock contention (even with CS), or
    3. a true deadlock (deadly embrace) between A and B

Posting Permissions

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