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...

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

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...

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...

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.

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

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...