DB2 v9.5 ESE on AIX v6.1
we are getting deadlocks. here is output of deadlock monitor i set up:

111) Deadlock statement history ...
Deadlock ID : 0
Participant No : 0
Application id : 172.16.1.122.44765.100902195013
Stmt history ID : 108
Type : Dynamic
Section No : 3
Package cache id : 4308926464
Package creator : NULLID
Package name : SQLUFG13
Package version :
Lock timeout value : 120
Nesting level of stmt : 0
Invocation ID : 0
Query ID : 0
Source ID : 0
UOW Sequence number : 0001
Isolation level : Read Stability
Stmt first use time : 09/02/2010 15:50:14.528693
Stmt last use time : 09/02/2010 15:50:14.528693
Statement text : INSERT INTO bb_table ("KEY_","LANGUAGE_","TEXT_") VALUES (CAST (? AS INT)
,CAST (? AS CHAR(1)) ,CAST (? AS VARCHAR(254))

it is not one time thing.
what do we do to fix the problem?
will changing isolation level of the package/statement fix the problem?

thanks in advance

Deadlocks are programming errors. You get a deadlock with this scenario.

Application user A locks resource X
Application user B locks resource Y
Application user A locks resource Y
Application user B tries to lock resource X <-- this causes deadlock detection.

Tell the developers to access the resources in the same order--all of the time.

Andy

A deadlock has two participants; you will need to determine what is on the other side of this deadlock and take an appropriate action. Like Andy said, deadlocks almost always indicate logical errors in the application(s) and it is not possible to resolve such errors on the database side.

yes, i understand how deadlocks happen. this is gotta be at least 2 statements, right? but we can see only one in the report. how do I find those 2 statement which cause deadlocks? our applications are huge and I need to point out what particular statements/packages causing the problem.

does it mean that db2 monitoring tools will not show me 2nd statement in the deadlock and we need to search in the code for it?

will snapshot for locks catch it?

thanks

The deadlock event monitor shows both. Don't forget to flush the event monitor before formatting its data.

do you mean to set its state to 0?

set event monitor my_deadlocks_mon state 0

FLUSH EVENT MONITOR - IBM DB2 9.7 for Linux, UNIX, and Windows

db2 v9.5 does not have this statement.

Yes it does:

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Andy

In addition to what Andy and Nick mentioned, check for lock escalations in the db2diag.log - they can contribute to deadlocks. Deadlocks don't occur without lock waits, reducing locktimeout may reduce deadlocks but increase lock timouts. You can review access plans to make sure no table scans where indexes should be used. Reducing isolation level may help, if appropriate.