Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: URGENT: deadlock issue

    Here is the problem description:

    If we edit a report using our custom application and save the same concurrently by 6-7 users application hangs and we receive a deadlock or timeout error (SQL0911N with reason code: "2"). On investigating further we realized there is an exclusive lock on a table and this result in a deadlock situation hanging the server. The application just fires a delete and select statements within a JTA. Each of these applications act on different rows all the time. Attached is the db snapshot of locks when the system failed.

    Any help is greatly appreciated. Please treat this as urgent.

    Thanks

    Note:
    We have set isolation level to "Uncommitted Read". DB and DBM configuration parameters are enclosed for further reference.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This appears to be a deadlock (deadly embrace) and not a timeout (simple lock contention). The locks all appear to be on the row level, not the table level. DB2 takes an exclusive lock (X lock on the row) when it needs to update. An exclusive lock is not necessarily a table lock.

    An example of a deadlock is when application A holds a row lock, and then tries to acquire another row lock that is held by application B, which is trying to acquire a lock on the row held by application A.

    You probably should read up about deadlocks and the causes, and the cures. Someone probably will need to look at the code in detail to see what needs to be done to fix it. Do not use UR isolation level. This is not used for updating. Use cursor stability, unless you need a level with more integrity with reading data (such as RS or RR).

    BTW, you database settings look like the default in most cases. These defaults are ridiculously small. You need to increase the amount of memory given to buffer pools and other objects such as application heaps, sort heaps, etc. The best way to do this is to run the Performance Wizard on the control center and give DB2 at least 25% of the real memory of the server. The Wizard will ask you questions about your application, number of users, etc that will provide a good starting point. Further tuning can be performed after 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

  3. #3
    Join Date
    Sep 2003
    Posts
    218

    URGENT: deadlock issue

    Hi Marcus

    Many thanks for the quick response.

    I agree with your assessment on deadlock issue. Owner of the specific application module is currently working on ways to improve concurrency.

    Meanwhile, the issue I am facing is that our application is performing most of the operations on couple of tables and they are performing Deletes and Inserts in a sequence. So, this should NOT ideally fulfill *deadlock* scenario that you explained. hmmm still wondering..

    I also agree with you on UR front. But, this is just a quick fix. Additionally, we are also testing our application at isolation level CS.

    The BUFFPAGE parameter typically has a default value of 250, and this value is overridden by issuing a SIZE command with CREATE BUFFER POOL or ALTER BUFFER POOL. So, we have allocated approx. around 300 MB for bufferpool.

    However, I will look into the autoconfigure option for increasing memory heaps in order to move away from default settings.

    Thanks once again for your help.

  4. #4
    Join Date
    Jun 2003
    Location
    Chennai
    Posts
    26
    hi,

    Just add some couple of points.. If ur application requries this, Increase ur deadchktime to 30000(ms). I think there is also a wait time in the locks while doing delete. It could be because of performance problem also. If u have resources still u can increase the buff page still more and logbufsz to 1024
    MS

  5. #5
    Join Date
    Sep 2003
    Posts
    218

    URGENT: deadlock issue

    We tried out with a solution at our end.

    What we did was where ever we had a transaction (delete , insert and select in a single transaction) we tried creating indexes for columns which were used in the predicate clause.
    This seems to work and we are not encountering the dead lock issues.
    Just need to confirm whether this is a right solution and will also work in long term i.e this solution will hold good when the data size increases.

    Also, we want to know in the first case when there were no indexes on these predicates - why we obtained deadlocks as opposed to no deadlocks after creating the index.

    Many thanks in advance.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Hard to say without seeing the DDL of table and indexes and the SQL statements involved.
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: URGENT: deadlock issue

    Originally posted by dr_suresh20

    Also, we want to know in the first case when there were no indexes on these predicates - why we obtained deadlocks as opposed to no deadlocks after creating the index.

    If an application does a table scan to locate certain row(s), and one of the table rows (not necessarily the ones that the app is looking for) is locked exclusively (as a result of an update by another application), the first application can't get past the locked row and the table scan waits.

    On the other hand, if an index access is used by the first application to get to the data the rows locked by the second app do not have any effect.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Sep 2003
    Posts
    218

    URGENT: deadlock issue

    Hi Marcus and N_i

    Thanks for responding.

    Marcus: Assume that i have three tables (tab_1 , tab_2 & tab_3) with 10-15 columns each. Here are the details:

    DELETE FROM tab_2 where <Col_1 = ?>
    SELECT <column_list> from tab_3 where <Col_1 = ?>
    DELETE FROM tab_1 where <Col_1 = ?>
    Note: Col_1 is an primary key column in the tab_1 and foriegn key in tab_2 and tab_3. Therefore, DB2 automatically creates index on the primary key. Now we went ahead and created explicit indexes on this column on the foreign key tables and some how we did not encounter deadlock situation. Moreover, we restored the database settings to CS and all other db parameters to their default values and nothing seem to matter!! Are we missing something?

    N_i: I am really tempted to think in the same way as to your doing currently - however, I am not sure if we are proceeding in the right direction...!! Besides, we were running our database on UR isolation level - so db2 should have ideally used the index in the first place?

    I am only worried that we are NOT running into any other data integrity issues here....!!

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: URGENT: deadlock issue

    Originally posted by dr_suresh20
    I am not sure if we are proceeding in the right direction...!! Besides, we were running our database on UR isolation level - so db2 should have ideally used the index in the first place?

    I am only worried that we are NOT running into any other data integrity issues here....!!
    Forcing indexed access is one of the common methods to avoid lock contention issues, as far as I understand.

    BTW, I don't think isolation level has anithyng to do with the optimizer selecting one or the other access path...
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Normally you should create indexes on foreign keys. DB2 does not do this automatically because sometimes they are not needed (if the parent row is never deleted).

    But in your case, you are deleting the parent row in tab1, which means that DB2 needs to search for dependent rows in tab2 and tab3 because of the referential constraint (either restrict or cascade). Therefore the indexes on the foreign keys are needed for improved performance and improved concurrency (as n_i noted).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2003
    Posts
    218

    URGENT: deadlock issue

    ......sounds great. We will review a list of all indexes that need to be created on FK columns.

    Thank you N_i and Marcus once again.

  12. #12
    Join Date
    Jul 2002
    Posts
    21

    Cool

    There can be more parameters involved in these things
    Take a snapshot of your database and calculate the bufferpool hit ratio. If it is less than 90% then your bufferpool has to be increased.

    Try with db2 registry variable by setting set db2_rr_to_rs=yes/on
    this will avoid lock on next row.

    And primarily give your sql with "for read only " suffix

    Check the access plan in db2 command centre for the sql's you are executing and taking the proper index path.

Posting Permissions

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