I'm using IBM WebSphere Application Server Advanced Edition 4.0.1 and IBM UDB 7.0. Offlate i'm getting a continuous deadlock error in most of the system transactions. Pls provide with some inputs as its affecting many production users.

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

Its not happening on a specific application, but on sparingly on all application for last 1 month. Is there a patch or fix from DB2 or WebSphere which solves this? I'm sure its not a generic error accross all users of this combination (WAS + IBM DB2 UDB). Any inputs and feedback is appreciated.

There are many reasons for a deadlock. Most likely your applications are not commiting often enough. You should make sure you have explicit commit statements for each logical unit of work or use autocommit.

Check you db2diag.log file and see if there are lock escalations. If so, increase your LOCKLIST memory to 4096 pages (all applications must disconnect for the change to take effect).

Thanks Marcus for your update.

The locksize is configured already by our DB admin to this size. If I'm right the application which is throwing this exception is not the issue here. When the application 'B' is trying to update some data in DB, since the table is locked by the other application 'A' this application 'B' throws the deadlock error and roll back the transaction, which is perfectly fine. Now is there a way to find out what's causing this previous lock to DB. I mean is there a way to find which is the application 'A'? There are around 11 WAS applications (EAR's) in our system. Is there a way to find from DB which is this application or the statement which is making this lock so that we can ratify the problem.

Will the connection pool configuration of WAS datasource make any difference?

Any feedback or inputs is appreciated.

You can create an event monitor for deadlocks. See the Command Reference manual. But the information provided in a V7 event monitor for locks is not as good as that provided in V8.2.