Problem with sequence of updates/removes in CMP beans
I'm having a problem where the sequence of updates/removes to the database is not the sequence we have in the code.
Using JBoss 2.4.4 we have a scenario where we need to manually break some foreign key columns before deleting a row. We do this by updating data in one table (setting the FK to null) and then deleting the row in the other table. This MUST happen in one transactional block. The Session bean doing this is marked TRANSACTION_REQUIRED and the session bean talks to CMP entity beans to actually update the data.
Our commit-option is set to 'C' and the XADataSource transaction isolation level is set to "TRANSACTION_READ_COMMITED".
The above works 80% of the time, but under moderate to heavy load we sometimes fail on the integrity constraint we are trying to overcome.
Setting JAWS to trace I see the following
[12:04:34,324,JAWSPersistenceManager] Remove command executing (tx: 2 ) DELETE FROM RAID WHERE RID=?
[12:04:34,324,JAWSPersistenceManager] Set parameter: idx=1, jdbcType=INTEGER, value=18976
[12:04:34,334,JAWSPersistenceManager] Store command executing (tx: 2 ) UPDATE DISK SET DATE_LAST_MODIFIED=?,RAID_RID=? WHERE RID=?
[12:04:34,334,JAWSPersistenceManager] Set parameter: idx=1, jdbcType=TIMESTAMP, value=2002-09-26 12:04:34.304
[12:04:34,334,JAWSPersistenceManager] Set parameter: idx=2, jdbcType=INTEGER, value=NULL
[12:04:34,334,JAWSPersistenceManager] Set parameter: idx=3, jdbcType=INTEGER, value=3352
[12:04:34,344,JAWSPersistenceManager] Rows affected = 1
[12:04:34,344,JAWSPersistenceManager] Store command executing (tx: 2 ) UPDATE DISK SET DATE_LAST_MODIFIED=?,RAID_RID=? WHERE RID=?
[12:04:34,354,JAWSPersistenceManager] Set parameter: idx=1, jdbcType=TIMESTAMP, value=2002-09-26 12:04:34.314
[12:04:34,354,JAWSPersistenceManager] Set parameter: idx=2, jdbcType=INTEGER, value=NULL
[12:04:34,354,JAWSPersistenceManager] Set parameter: idx=3, jdbcType=INTEGER, value=3353
[12:04:34,354,JAWSPersistenceManager] Rows affected = 1
.....
[12:04:34,475,JAWSPersistenceManager] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0532N A parent row cannot be deleted because the relationship "?????????.DSK.DISK_RAID_FK" restricts the deletion. SQLSTATE=23504
[12:04:34,485,Default] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0532N A parent row cannot be deleted because the relationship "?????????.DSK.DISK_RAID_FK" restricts the deletion. SQLSTATE=23504
(ignore the ???????? marks, they are to hide identity)
Normally the updates happen and then the delete, but in this trace we see the delete and then the updates. (Usually when the error happens I don't see the updates, but for some reason the commit (or something) lagged, and I saw the updates before the exception happens)
The other complication is that there are multiple threads running contending for the same resources.
Could the updates be delayed by another thread updating the same table and thus putting the SQL out of sequence?
Does anyone have any other ideas?
John Z.