I have a script that I wrote to test DB replication. But the problem I have is not related the replication (I believe). I have 4 copies of the script that run concurently. What the script does is generating data and inserting them to the database. The script inserts rows into 4 tables and then commit or rollbacks. My problem is that I get a deadlock exception which is quite unexpected because I expected that a deadlock should not be possible with a transaction that contains only inserts. The log files I have show that lock escalation does not occur.
I set up a event monitor for deadlock exception with details. The output of the event shows a deadlock between 2 or 3 applications. All deadlock connection events look the same (please see at the end of the post) except that they relate to different object ids. So the way I read the log file is that a transaction is doing insert. That leads to row X lock and NW lock. The NW lock is (probably) caused by the fact the table has defined indexes. So actually I guess there should be one row NW lock per index. A problem occurs when a request for NW lock causes wait because that row has already X lock made by another transaction. In that way deadlock can easily be reached.
Could someone tell me if this interpretaion is correct ?
I tried to reproduce this by manually inserting rows (doing db2 +c "insert into ....")
and watching what locks are hold by taking snapshots for locks. The thing is that I cannot see row NW locks , but only row X locks. So I was not able to cause a deadlock in this way That probably means that my interpretation of the problem explain above is wrong
Any ideas how deadlock can happen in concurent transaction of the same type that do only row insert ?
Thanks in advance.
PS: I nearly forgot: I am running this on DB2 8.1.6 on Linux RedHat AS 3
Here is very small part of the event monitor output:
Deadlocked Connection ...
Deadlock ID: 6
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: C0A80137.EDC9.0DB312121849
Appl Seq number: 0003
Appl Id of connection holding the lock: *LOCAL.db2inst1.01FCD2121636
Seq. no. of connection holding the lock: 0005
Lock wait start time: 12-02-2004 13:17:42.410279
Lock Name : 0x03000800FFFFFFCE0000000000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 12-02-2004 13:17:48.204006
Table of lock waited on : SERIES_PRIMARY
Schema of lock waited on : EV
Tablespace of lock waited on : EV_TBSPACE
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: NW - Next Key Weak Exclusive
Node lock occured on: 0
Lock object name: 206
Application Handle: 63
Type : Dynamic
Section : 7
Creator : NULLID
Package : SYSSH200
Cursor : SQL_CURSH200C7
Cursor was blocking: FALSE
Text : INSERT INTO series_primary (vps_id, study_vps_id, study_seq_id, series_instance_uid, date, time, modality) VALUES (?, ?, ?, ?, ?, ?, ?)