Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2010
    Posts
    19

    Unanswered: DB2 locks and deadlocks

    Hi,
    We are using DB2 UDB 8.1 as the backend for our e-commerce application(Websphere commerce server).For quite some time we are experiencing deadlocks in DB2 and we are using the tool db2pe to detect and monitor the deadlock events.
    Most of the deadlocks which are happening are related to UPDATE followed by SELECT statements. It means in the report which is generated we see there is a UPDATE statement on some X table and another transaction is using SELECT clause on the same X table and because of which deadlock happens and 2 transaction is rolled backed.
    When we saw the report I was stunned by the lock types and lock modes on each of these UPDATE and SELECT statements. On SELECT statements there was Exclusive lock and on UPDATE it was NEXT-KEY SHARE LOCK.
    I am not clear on how come UPDATE and SELECT statements can result into deadlock.I am attaching the report.In the report you will find 4 transactions 2 are related to XSOLDTO table which is used in UPDATE clause and 2 ORGENTITY table on which SELECT clause is used.In the SELECT statement XSOLDTO is one of the tables.
    If anybody can make be understand how come deadlock happens between UPDATE and SELECT clause and also on how come a select clause can have EXCLUSIVE lock.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are really using DB2 LUW 8.1, then you need to upgrade to 8.2 by applying the latest fixpack to 8.1 from the IBM fixpack website (there is no charge for this). Then you need to do an offline reorg of all tables. This will convert all indexes from type 1 and type 2, which improves concurrency of most applications by reducing the Next-key Share Locks.

    A select statement does not take an exclusive lock, unless it is select for update. Unfortunately, the information you provided is not detailed enough for me to give any other analysis, but maybe others can see something to help you.

    If you upgrade to the latest 8.2 fixpack, you can create a detailed deadlock event monitor, which will provide better information on what is happening during a deadlock (when you run the event monitor formatting report on the deadlock files). Just in case you are not aware, a deadlock is -911 RC=2, whereas a locktimeout is -911 RC=68. You will not be able to get a detailed report on a locktimeout in version 8.2 (version 9.5 has very good locktimeout reporting if you set it up correctly).

    I am not sure why you are using version 8, since it is long out of support by IBM.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2010
    Posts
    19
    Hi Marcus,
    Thanks for the update.
    The SELECT statement which we are using is not having any SELECT FOR UPDATE clause.
    It is taking the default isolation level as CS, Will this result into EXCLUSIVE lock.
    Did you check the report which i uploaded.
    Actually version 8.1 is being used by client and they are in no mood to change it now so we have to live with it.

    Thanks
    Ravi

  4. #4
    Join Date
    Apr 2010
    Posts
    19
    I am not sure on what actually NEXT KEY SHARE lock means.Can anybody explain me with a small example.

  5. #5
    Join Date
    Apr 2010
    Posts
    19
    Yes we are getting error with " -911 RC=2" which is a deadlock.
    Still not clear on how come UPDATE and SELECT (without FOR UPDATE CLAUSE) can result into deadlock

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by infyravi View Post
    I am not sure on what actually NEXT KEY SHARE lock means.Can anybody explain me with a small example.
    You don't need to know that information. If you disagree, then google it. If you are indeed running 8.1 then there is no excuse for not upgrading to 8.2 which can be accomplished by installing the latest 8.1 fixpack, which is about FP 18 (or something close to that).
    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
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by infyravi View Post
    Yes we are getting error with " -911 RC=2" which is a deadlock.
    Still not clear on how come UPDATE and SELECT (without FOR UPDATE CLAUSE) can result into deadlock
    In DB2, an application cannot update a row if another application has a an active S lock (share lock from a SELECT).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Apr 2010
    Posts
    19
    Hi Markus,
    Thanks for the information but how can we achieve the following concurrency requirement in DB2

    Even if SELECT ( without FOR UPDATE CLAUSE) or UPDATE is happening on table rows as part of one transaction, in another transaction I should be able to either UPDATE or SELECT on the same table rows.
    Means SELECT + UPDATE should be possible and UPDATE + SELECT should be possible.
    What if I suffic SELECT query with "WITH UR" clause.
    Will this avoid deadlocks conditions which we are facing ( Update followed by Select ) or ( select followed by update).

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by infyravi View Post
    Hi Markus,
    Thanks for the information but how can we achieve the following concurrency requirement in DB2

    Even if SELECT ( without FOR UPDATE CLAUSE) or UPDATE is happening on table rows as part of one transaction, in another transaction I should be able to either UPDATE or SELECT on the same table rows.
    Means SELECT + UPDATE should be possible and UPDATE + SELECT should be possible.
    What if I suffic SELECT query with "WITH UR" clause.
    Will this avoid deadlocks conditions which we are facing ( Update followed by Select ) or ( select followed by update).
    You might avoid a deadlock by changing the select + update to select for update + update. This will casue a lock wait for update + select, but probably not a deadlock. To avoid the locking problem altogether, you could try WITH UR on the select (when you do slelect + Update).

    DB2 locking is quite a bit different than Oracle, so what you think should happen, may be different than what happens.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Apr 2010
    Posts
    19
    Hi Marcus,
    Thanks a lot for providing the valuable information.

    We are having 2 trypes of queries.
    1) transactional queries
    2) report queries

    For report queries we can easily suffix WITH UR clause to the SELECT statements and avoid the deadlock issue.Since the reports are not that mission critical we can show the uncommited data which results from WITH UR clause.

    Now for transacational queries we want to avoid the lock timeouts as well as deadlocks to maximum extent.Now if we use WITH UR clause there is a possibility of reading uncomitted data and if we use SELECT FOR UPDATE +UPDATE combination then it may result into timeouts.
    So what should be the best approach for transactional level queries.Can you please clarify?

    Thanks
    Ravi

  11. #11
    Join Date
    Apr 2010
    Location
    USA
    Posts
    10
    oh,yes,i think the profile var. value is small.change it!

  12. #12
    Join Date
    Apr 2010
    Posts
    19
    Sorry,I am not able to get what I need to change and where?

Posting Permissions

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