Hi All,

DB2 UDB v8.2 Fixpack 7, OS- AIX

I am getting this error :

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001

Also I took a snaphot of the databases which does show 2 deadlocks.

What is best way to get rid of the deadlock? Can I force those applications which are holding the locks from the database by using "force application (application handle)"

Thanks

Anks

Deadlocks are automatically resolved by DB2 when it cancels one of the participants in the deadlock in order to let the other one proceed. By default, DB2 checks for deadlocks every 10 seconds.

A locktimeout error (-911 reason code 68) can occur when the locktimeout period has occurred for an application waiting on a lock (but there is no deadly embrace). One thing you should do is to change the default locktimeout value from -1 (infinite wait time) to something like 30 seconds.
db2 connect to <db-name>
db2 update db cfg using locktimeout 30

To analyze future deadlocks, you should create a deadlock event monitior. See create event monitor in the Command Reference manual. If you first created the database under version 8.2, then a deadlock event monitor may already be created by default and you can retrieve reports on what happened.

Thanks Marcus,

The locktimeout is already set to 300 seconds, which I feel is little on the higher side.

The Default deadlock does exits and I do see the .evt and .ctl under the folder db2detaildeadlock. Another strange thing is that the db2diag.log does not show any traces of deadlock. How much time does DB2 takes in resolving the deadlock? Because I saw deadlocks bumping from 1 to 3 in just half an hour.

In a deadlock situation, can trying to increasing the MAXLOCKS help in getting rid of the deadlock, if DB2 doesn't get rid of the deadlock on its own.


db2 get db cfg | grep LOCK

Max storage for lock list (4KB) (LOCKLIST) = 8196
Percent. of lock lists per application (MAXLOCKS) = 30
Lock timeout (sec) (LOCKTIMEOUT) = 300

I know MAXLOCKS is on a low side and I do see lot of lock escalations just before the deadlock.

Thanks
Anks

DB2 starts the deadlock detector every 10 seconds by default (configurable in the db cfg) and if a deadlock exists it immediately cancels one of the participants. A deadlock is different than a lock wait because DB2 can determine if a real deadlock exists (in other words, it will never be resolved by itself no matter how much time elapses).

Increasing the LOCKLIST and the MAXLOCKS (the percent of the LOCKLIST that can be used by one application before escalation takes place) will help prevent lock escalation from page to table. When the locklist is full, lock escalation takes place. But your values seem reasonable to me.

The problem can probably be traced back to applications that are not commiting frequently enough, or running certain utilities when others are trying to access the table.

If you produce the report for the deadlocks that occured, you should be able get some information on what happened.

Thanks again.

I do have couple of application, database and lock snapshots taken at the time I got the above mentioned eror and a db2event.000 file. I suppose I can format this file using:

db2evmon -d <database_name> <the event monitor file along with the path>

Hopefully this would give me some information on the deadlock. I have attached a snapshot of the application which was holding the lock.

Thanks
Anks