If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Deadlock Error

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 19
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 19
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...
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2006
Location: Belgium
Posts: 2,080
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 19
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...
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On