Results 1 to 7 of 7

Thread: Deadlock Error

  1. #1
    Join Date
    Feb 2013
    Posts
    10

    Deadlock Error

    I am having 2 tables.First is Master table and the second is child table. Master table has primary keys and the child table has foreign keys reffering to the primary key of the Master table.There is one "Application A" which inserts,updates and deletes data from Master and Child table.

    There are triggers and stored procedures applied on Master and Child table for insert,update and delete operations, which insert,update and delete data in 4 other tables.These 4 tables are also updated by "Application B".

    Now I am receiving deadlock exception when the "Application A' is trying to insert/update/delete data from the Master and Child table.

    This deadlock is happening once or twice a day.And Application A in a day processes around 50K records.

    What can be the rfeason for deadlock and how to rectify it and check for the cause of deadlock...

  2. #2
    Join Date
    Feb 2012
    Posts
    23
    Hi prat31,

    Are you sure that a deadlock is occurring and not a lock with timeout?
    One way to test is to have to CLP's with auto-commit off and test updating the same tables; that is Application B updating the 4 tables and Application A updating the same records in the same tables.

    Kind regards,

    Bert

  3. #3
    Join Date
    Feb 2013
    Posts
    10
    Hi, thanks for the reply. Yes it is deadlock(SQLSTATE:, 40001, SQLCODE:, -1, SQLNATIVEERROR:, -911, Reason code "2". SQLSTATE=40001) in Error.

    Earlier I used to get Timeout Error(Reason code 68). So I had applied some Indexes.
    But now I am getting deadlock Error.

    What can be the reason and what can be done.If you can tell in detail.

    Thanks in Advance...

  4. #4
    Join Date
    Feb 2012
    Posts
    23
    Hi Prat31,

    The reason for a deadlock is created when two applications are each locking data needed by the other, resulting in a situation when neither application can continue execution. So, Application A and B are both locking data that is needed by the other to complete the transaction.

    What you can do about it or locate the deadlock details:

    1) You could try to commit more often.

    2) Use the FOR UPDATE clause when performing a select operation on rows that are intended to be updated. This clause ensures that a U lock is set when a process attempts to read data, and it does not allow row blocking.
    The U mode lock is used for intent to update locking. It allows deadlock prevention as it is not compatible with itself. DB2 could acquire a U lock for application 1, when a row is retrieved for a cursor with the FOR UPDATE clause. If application 2 also tries to access the same row using a cursor defined FOR UPDATE, the request for a U lock for the row will force application 2 to wait for the first application to release the lock.

    3) To log more information about deadlocks, set the value of the diaglevel database manager configuration parameter to 4. The logged information includes the name of the locked object, the lock mode, and the application that is holding the lock. The current dynamic SQL and XQuery statement or static package name might also be logged.

    4) Enable deadlock event monitoring and inspect the detailed output file to see the details of the deadlock that occurred
    DB2 9.7 - Lock events for DB2 for Linux, UNIX, and Windows, Part 3: Use the lock event monitor in DB2 9.7 to solve concurrency issues
    DB2 8 and 9 - IBM Default deadlock event monitor - United States

    Hope this helps...

  5. #5
    Join Date
    Feb 2013
    Posts
    10
    Hi we monitored the deadlock report and found that when Application B is using a view to select from Master table Child table and other tables and that time if Application A is trying to update the Child table the deadlock Error is thrown for the Application A. Also the view takes 30 seconds to select from the tables and perform its operation.

    So I wanted to ask that can the select operation lock the table and cause deadlock for the other operations???

    ANd How can I tune the opeartions done by Application B using the Views.

    For Application A I have already created Index for the columns in where clause.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,248
    deadlocks need to be analyzed..
    request details about both applications
    check if intermediate commits can be applied
    check with explain the access path being used by queries and act accordingly
    or use db2advis with these queries..
    many reasons for deadlock.. and it needs investigation...
    maybe this can help
    Creating a detailed deadlock event monitor
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Feb 2012
    Posts
    23
    Yes, a select can lock the table (or rows from the table).
    You can tune the view, with like przytula_guy mentioned dvadvis. Besides that if the select result is not used for updates I would add the 'FOR READ ONLY' clause to the query to let DB2 know you are not gonna update the rows so that it can select blocks iso using a cursor (this can improve preformance). Besides that you could have a look at statistical views...

Posting Permissions

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