We are on AIX 4.33 UDB 7.2 Fixpack 10.

We are having deadlocks issues in our applications. I created a Deadlock Event Monitor in the enviroment and turned it on.

However the Deadlock Monitor report shows no Deadlocks. Its not capturing them.

The error I get on the app screen is
"SQL0911N The current transaction has been rolled back or timed out. Reason Code =68."

Reason Code 68 is for timeouts as per the manual. Is that the reason why the Event monitor is not capturing it ??

thanks

Manish

It might surprise you but lock timeouts and deadlocks are different animals. You are absolutely correct noticing that the "reason code 68 is for timeouts as per the manual". This is exactly the reason why the deadlock event monitor doesn't capture these events.

Hope this helps.

Nick

As noted, a deadlock (or deadly embrace) is not the same as a timeout, even though they both abend due to lock contention.

It seems that you have a timeout that is caused by a wait on a locked resource that has exceeded the time allowed per your lockwait time specified for the DB2 system. This could be caused by lock escalation, where DB2 has escalated an application from Row to Table level locks based on the amount of lock activity. Lock escalation can often be curtailed with the maxlocks and locksize parameters.

Lock contention could also be caused by applications using RR isolation level where they keep a lot of row locks until commit time. You might see if CS isolation level is acceptable for such applications, and use it to improve concurrency.

I surely have a time out issue.

The problem is that I am not able to determine that the time out problem is being caused what other thread. The lock list utilization is less than 10 percent.

If my thread is timing out, some other thrad must have the lock. How to capture that thread info ???

thanks

What is the current value of the lock timeout?
If it is large enough, you should be able to capture locked applications, tables, statements that are part of the "lock timeout" using regular snapshot mechanism (get snapshot for applications, get snapshot for lock for application).
Do not forget to turn statement and lock monitors on.

-dmitri

Ok this is the simplest solution and it worked.
I set the Locktimeout to -1. (so basically disabled it).
Set the DLCHKTIME = 20000 ms (=20sec which was my Locktimeout before).

So now the application will have the rollback in case of lock problem with Reason code 2 instead of 68.

AND the event monitor catches it nicely showing the contention threads easily...

By the way, this whole thing use to be easily visible in the db2 log in the mainframe without the need to set any thing.. it would be nice to see the deadlocks in the log file on UDB on AIX too..

thanks every one..

I think you can see them if you rise diaglevel to 4

Your are right about diaglevel 4. I tried that and it does show the deadlock. However the inofrmation provided is very limited and very cryptic. Thanks for the suggestion...