Database Software

Hello,

I have two tables: T1 and T2. T2 has a foreign key reference to T1. The Foreign key reference has "on delete cascade".

The application has two threads. Both threads are concurrently executing transactions that include inserts into tables T1 and T2 as well as deletes to table T1.

TimesTen is intermittently reporting deadlocks (TT6002) between the inserts in table T2 and the deletes in table T1. The deadlocks, I suspect, are side-effects of the "delete cascade".

There is no overlap between the transactions i.e. the deletes performed by a transaction are to records inserted by the same transaction. A transaction typically inserts 15 rows in T1, inserts 15 rows in T2 and then deletes 15 rows from T1. The 15 rows inserted in T2 make references to the 15 rows inserted in T1; the 15 rows deleted from T1 are the rows from the same transaction.

Unique keys are not reused between transactions i.e. each transaction contains never-before used data.

You can guess that the production system would be doing something more useful than just deleting the data that's being inserted in the same transaction; the code has been simplified to try to figure out what's happening...

Any idea why there are deadlocks?

Here are the details of error 6002 when the delete on T1 fails because an insert to T2 has the lock:

Details: Tran 111.10 (pid 8072) wants X lock on rowid 0x002f6124, table T2. But tran 108.5 (pid 3640) has it in Xn (request was Xn). Holder SQL (INSERT INTO T2(PKCol,Col2,FKCol,Col4,Col5,Col6,Col7...) -- file "table.c", lineno 16481, procedure "sbTblGetLockOnOneIndex"

Here are the details of error 6002 when the insert in T2 fails because a delete has the lock:

Details: Tran 108.33 (pid 3304) wants Xni lock on rowid 0x002f6134, table T2. But tran 110.34 (pid 5592) has it in X (request was X). Holder SQL (DELETE FROM T1 WHERE PKCol = 195424;) -- file "table.c", lineno 14856, procedure "sbTblStorLTupSlotAlloc"


Timesten release 7.0.6.2 is being used on Windows XP. The database is configure with:
- disk logging enabled
- row-level locking selected
- durable commits disabled
- read-committed isolation
- lock wait of 10 seconds
- replication is not in use

Thanks for your help,

Michel