Results 1 to 10 of 10

Thread: Why table lock?

  1. #1
    Join Date
    Jul 2005
    Posts
    102

    Unanswered: Why table lock?

    Hi Gurus,

    I have been to trying find the cause of lock waits in one of the databases. The OS is AIX 5.3 and DB2 UDB v8.2 FP 7.

    I am pasting the output of db2pd -locks waits -db <database name>

    Locks:
    Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse
    0x078000002070B8C0 33 00060004000000000000000054 Table NON G 33 0 0 0 0x40
    0x0780000020712480 8 00060004000000000000000054 Table NON G 8 0 0 0 0x40
    0x07800000206E98C0 33 00060010000000000000000054 Table NON G 33 0 0 0 0x40
    0x07800000206F3740 16 00060010000000000000000054 Table NON G 16 0 0 0 0x40
    0x0780000020726C00 33 00060012000000000000000054 Table NON G 33 0 0 0 0x40
    0x0780000020710D00 8 00060012000000000000000054 Table NON G 8 0 0 0 0x0
    0x078000002070B740 36 00060012000000000000000054 Table NON G 36 0 0 0 0x0
    0x07800000206FDE80 36 00060016000000000000000054 Table NON G 36 0 0 0 0x40
    0x0780000020726380 31 00060016000000000000000054 Table NON G 31 0 0 0 0x40
    0x07800000207175C0 29 00060016000000000000000054 Table NON G 29 0 0 0 0x40
    0x07800000206F28C0 36 0003000c000000000000000054 Table NON G 36 0 0 0 0x40
    0x07800000207293C0 31 0003000c000000000000000054 Table NON G 31 0 0 0 0x40
    0x0780000020717C80 29 0003000c000000000000000054 Table NON G 29 0 0 0 0x40
    0x07800000206FB440 36 0003000e000000000000000054 Table NON G 36 0 0 0 0x7f
    0x07800000206EBDC0 31 0003000e000000000000000054 Table NON G 31 0 0 0 0x7f
    0x07800000206F0B00 29 0003000e000000000000000054 Table NON G 29 0 0 0 0x7f
    0x078000002070AB40 8 0003000e000000000000000054 Table NON G 8 0 0 0 0x7f
    0x07800000206FCF80 24 0003000e000000000000000054 Table NON G 24 0 0 0 0x7f
    0x078000002070B180 33 0003000e000000000000000054 Table NON G 33 0 0 0 0x0
    0x07800000206F3B40 16 0003000e000000000000000054 Table NON G 16 0 0 0 0x0
    0x078000002070C140 36 00030010000000000000000054 Table NON G 36 0 0 0 0x40
    0x07800000207266C0 31 00030010000000000000000054 Table NON G 31 0 0 0 0x40


    And here is the db cfg output:

    db2 get db cfg | grep LOCK

    Max storage for lock list (4KB) (LOCKLIST) = 35000
    Percent. of lock lists per application (MAXLOCKS) = 60
    Lock timeout (sec) (LOCKTIMEOUT) = 300

    Even though the locklist is pretty big why the row locks are getting converted into table locks is what i fail to understand.

    Thanks
    Anks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you experiencing lock escalation? What is the SQL that is causing the locks?

    Andy

  3. #3
    Join Date
    Jul 2005
    Posts
    102
    Yes, I do see lock escalations in the db2daig.log. How do I find out the SQL which is causing the locks?

    Thanks

    Anks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not 100% sure, but I think you need to set up an Event Monitor.

    Andy

  5. #5
    Join Date
    Jul 2005
    Posts
    102
    Doesn't the default event monitor catches the SQL causing lock problems? or it just records the deadlocks?

    Thanks

    Anks

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is a default deadlock event monitor, but not one for lock escalation, although lock escalation can cause deadlocks.

    To minimize lock escalation, increase to LOCKLIST to at least 4096 in the db config.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by anksagr

    And here is the db cfg output:

    db2 get db cfg | grep LOCK

    Max storage for lock list (4KB) (LOCKLIST) = 35000
    Percent. of lock lists per application (MAXLOCKS) = 60
    Lock timeout (sec) (LOCKTIMEOUT) = 300

    Even though the locklist is pretty big why the row locks are getting converted into table locks is what i fail to understand.

    Thanks
    Anks
    I am curious, are you getting the deadlock/timeout error, or are you just seeing some time spent in lock wait? The reason, I am asking is that your timeout time is 5 minutes. That is a long time to wait for a lock and have to time out. With you locklist so big, my guess is that you have some statements that affect a hugh amount of rows, which is what is causing your lock escalations.

    If you are not getting the deadlock/timeout error, I would not worry about it too much. You should expect some amout of time waiting for locks. If you are getting the error and the reason code = 68 (timeout) then you have a big issue that needs to be resolved. Something is holding locks for over 5 minutes, and that is bad for concurrency. If you are getting reason code = 2 (deadlock), use the event monitor to track down the cause.

    Andy

  8. #8
    Join Date
    Jul 2005
    Posts
    102
    Thanks Andy for the response. Well the application did report of an error saying "current transaction rolled back because of deadlock or lock time out". When I look at db2diag.log i don't see anything. I checked the default event monitor to look for a deadlock but nothing in there. So i thought its a lock time out situation.

    I took the database snapshot and here's the output:

    Locks held currently = 47
    Lock waits = 167
    Time database waited on locks (ms) = 117236
    Lock list memory in use (Bytes) = 39104
    Deadlocks detected = 1
    Lock escalations = 0
    Exclusive lock escalations = 0
    Agents currently waiting on locks = 0
    Lock Timeouts = 0
    Number of indoubt transactions = 0

    Now here the snaphot says that it has detected a deadlock, which i presume its a lock time out. I fail to understand why in the database snapshot i am seeing this.

    I am really confused as to what is going on.

    Thanks,
    Anks

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This is what I think you should do.

    1) setup an event monitor that track everything (not just the "default") -- In my opinion, it is better to have too much info than not enough.

    2) Start the event monitor.

    3) run the application

    4) Use the snapshot monitor to watch behavior

    5) record the entire error messages in the app. You need to know whether it is encountering timeouts or deadlocks. Make sure you have the timestamp of when these occurred--you will need this to compare to data from event monitor.

    6) run the entire thing until you are experiencing your problem.

    7) analize all the data gathered to determine where problem is occurring.

    Note: here is a good GUI snapshot monitor: http://chuzhoi_files.tripod.com/index.html

    Andy

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by ARWinner
    Hi,
    I got "ERROR: The requested URL could not be retrieved" and error message said: "Illegal character in hostname; underscores are not allowed". I use Firefox 2.0 on Windows XP SP2.

    I know Tripod Web pages structure because I have a web page on it for at least a decade. Tripod has two addresses to one web page. So the same address that is working on my browser is: http://members.tripod.com/chuzhoi_files/index.html

    Hope this helps,
    Grofaty

Posting Permissions

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