Deadlock in Db2

alwayssathya · #1 (**permalink**) 09-14-10, 01:45

hi,

I saw a thread already posted on Deadlock. but which din give me any clarity.

here i have a few question can some help me.

1.how to identify/detect a deadlock in db2
2.suppose if the deadlock is not rectified by db2, how can i resolve the deadlock.
3.In the db2detaildeadlock, there are some log files. how can it be reviewd.
4.how to get the snapshot of a particular databse to find the deadlock.
5.what is locktimeout & locklist ? when do we increase the locktimeout and lock list ? what is the use of increasing it.

thanks !

ARWinner · #2 (**permalink**) 09-14-10, 08:13

1) DB2 automatically identifies a deadlock. It is built in and always works.
2) deadlocks are always rectified by DB2. It will rollback one of the applications, and return the "Deadlock" error.
3) Text editor
4) The snapshot monitor will not give you much about deadlocks. Use the Event Monitor.
5) LOCKTIMEOUT is the number of seconds a DB2 agent will wait for a lock to release before it returns an error back to the application. If it is zero, then wait time is not in effect and applications will wait forever. LOCKLIST is the size of the DB2 cache for storing lock information. Increasing LOCKTIMEOUT can cause longer response times for database access. Increasing locklist can prevent lock escalation, but at the expense of taking away RAM from other heaps that might have better use for it.

Andy

Marcus_A · #3 (**permalink**) 09-14-10, 13:12

Even though both deadlock and locktimeout give a -911 return code, they are different. Deadlock is reason code 2 and locktimeout is reason code 68.

Basically a deadlock is a lock wait situation that DB2 determines will never be resolved, no matter how long you wait. Also known as a deadly embrace.

You can turn on locktimeout monitoring to get reports on that (which is different than a deadlock). How you do this depends on the DB2 release you have.

alwayssathya · #4 (**permalink**) 09-15-10, 00:40

Thanks a lot Winner & Marcus...

i got a clear view of it.

one more question is ,
if there is a deadlock and Db2 also throwed the error "Deadlock" , In such case how can i find which process is causing this situation ?

for this case can we event monitor, if so how to use it ?

Thanks!

Marcus_A · #5 (**permalink**) 09-15-10, 01:23

Recent versions of DB2 LUW automatically have a deadlock event monitor created when a database is created. You should be able to find out which event monitors have been created (and which are active) by querying the event monitor tables in the DB2 system catalog views.

If you have a detailed deadlock event monitor created (recommended), it will show you the SQL statements involved in the deadlock.

If you don't already have a deadlock event monitor created, then the DB2 documentation provides a lot of information on how to do it.

infyravi · #6 (**permalink**) 09-15-10, 02:50

Hi,
We are getting a wierd deadlock.
Deadlock is happening between DB2RBIND process and java.

Following is the mail which we got from db2pe tool.

IBM (C) DB2 Performance Expert.
A DB2 deadlock exception occurred at '2010-09-15 02:13:54.03628' on host 'dbpdadc60' on DB2 instance 'wcspi'.

Deadlock details:

Database name : WCSP
Partition number : N/P
Number of involved applications : 2
Rolled back application ID : GA141A3D.AAAE.14CD35001000
Rolled back agent ID : 932
Rolled back participant : 2

Application details:

Participant : 2
Application ID : GA141A3D.AAAE.14CD35001000
Agent ID : 932
Application name : java
User login ID : wcuser
Database alias used : WCSP
Table name :
Schema name :
Tablespace name :
Statement type : Non SQL
Statement operation : Execute
Statement text :

Participant : 1
Application ID : *LOCAL.wcspi.100915001310
Agent ID : 33
Application name : db2rbind
User login ID : wcspi
Database alias used : WCSP
Table name :
Schema name :
Tablespace name :
Statement type : Non SQL
Statement operation : Rebind
Statement text :

*****************************************
db2rbind process runs daily and we never got the deadlock before.
Not sure how db2rbind process and java can lead to deadlock.
Is it that when db2rbind is re-binding the packages , java application is also trying to use the same package.
What should be done to avoid such deadlocks.

Thanks
Ravi

db2girl · #7 (**permalink**) 09-15-10, 18:51

Quote:

Originally Posted by infyravi

Deadlock is happening between DB2RBIND process and java.

It could be on some catalog table (ie. sysibm.sysplan). You should have a deadlock monitor called "db2detaildeadlock". You can format it using db2evmon. Check this link for some info about this event monitor:
IBM - Default deadlock event monitor

Mike Anderson · #8 (**permalink**) 09-16-10, 12:17

There is a deadlock event monitor called DB2DETAILDEADLOCK. Just format it to see the deadlock events and the SQL statements that are causing the issue. Set the event monitor state to 0 before using db2evmon to get the records. If you don't do this you will not get all the recoprds

alwayssathya · #9 (**permalink**) 09-20-10, 01:33

I have a Table in my db, which will have all the data and people will normally only select the data from this table.

often if i fire the select query it is just processing and the result is not produced.

Is this some case of deadlock,

how to come over this situation, because it happen often and after some time it comes back to normal.

but i need to fix this issue...

ARWinner · #10 (**permalink**) 09-20-10, 08:05

What is the value of the DB configuration parameter LOCKTIMEOUT?

Andy

alwayssathya · #11 (**permalink**) 09-20-10, 08:25

Locktimout = -1,

ARWinner · #12 (**permalink**) 09-20-10, 08:39

Your lock time out value is set to "Turned off". This means that all requests for a lock that have to wait for another lock to be released will wait for as long as the other lock is held or until a deadlock occurs. What you are experiencing is not a deadlock. You are experiencing what is called a lock wait. Your query is waiting for a lock that eventually gets released and then the query proceeds.

You can fix this one of two ways. You can set LOCKTIMEOUT to a positive value, which will be the maximum number of seconds you want the lock waits to occur. If, for example, you set it to 30, then a statement that ends up waiting for a lock will only wait up to 30 seconds for that lock to release. If the lock is not released, then an error will be returned to the application waiting for the lock that it timed out. Note, this error is the same error that is given when a deadlock occurs. The only difference is the "Reason Code". Deadlock is 2 and timeout is 68.

The other way to fix this, and still may need to be done, is to determine what is holding the locks for so long and fix them so they do not hold the lock for a long period.

Andy

alwayssathya · #13 (**permalink**) 09-22-10, 00:53

i have changed the locktimout to 30 as you mentioned. but you

when im deleting a table which has 12000 rows it is not deleting..it comes out after 30 second saying there is an deadlock / rollback..

and also select query with conditions not working some times.

any other suggestion to come over...

Thanks!

ARWinner · #14 (**permalink**) 09-22-10, 08:25

The error message you are getting is very often misread. You are not getting a deadlock, you had a timeout. Here is the error message:

Code:

SQL0911N  The current transaction has been rolled back because of a
      deadlock or timeout. Reason code "<reason-code>".

Explanation:

The current unit of work was involved in an unresolved contention for
use of an object and had to be rolled back.

The reason codes are as follows:

2

         The transaction was rolled back due to a deadlock.


68

         The transaction was rolled back due to a lock timeout.


72

         The transaction was rolled back due to a DB2 Data Links Manager
         error during the transaction.


73

         The transaction was rolled back because a queuing threshold
         such as the CONCURRENTDBCOORDACTIVITIES threshold caused two or
         more activities to reach a deadlock state. For more
         information, see "CONCURRENTDBCOORDACTIVITIES threshold" in the
         DB2 Information Center.

The application was rolled back to the previous COMMIT.

User response:

The changes associated with the unit of work must be entered again.

To help avoid deadlock or lock timeout, issue frequent COMMIT
operations, if possible, for a long-running application, or for an
application likely to encounter a deadlock.

Federated system users: the deadlock can occur at the federated server
or at the data source. There is no mechanism to detect deadlocks that
span data sources and potentially the federated system. It is possible
to identify the data source failing the request (refer to the problem
determination guide to determine which data source is failing to process
the SQL statement).

Deadlocks are often normal or expected while processing certain
combinations of SQL statements. It is recommended that you design
applications to avoid deadlocks to the extent possible.

For more detailed information about preventing deadlocks or lock
timeouts search the DB2 Information Center
(http://publib.boulder.ibm.com/infocenter/db2luw/v9) using phrases such
as "deadlock prevention", and terms such as "deadlocks" and "lock
timeouts".

If a deadlock state was reached because of a queuing threshold such as
the CONCURRENTDBCOORDACTIVITIES threshod, increase the value of the
queuing threshold.

sqlcode: -911

 sqlstate: 40001

Your problem is that you have locks that are being held longer than 30 seconds. This usually indicates poor transaction design. You need to determine who is holding the locks for more than 30 seconds and fix the transaction to eliminate the situation.

Andy

alwayssathya · #15 (**permalink**) 09-24-10, 00:33

Andy, thanks for the info,

i dont know how to determine which is acquring the lock for than 30seconds,

what is the way to fix this ?

Internet Services

Web Development

Digital Marketing

Consumer Tech