I have an object (WorkflowProcessStatus) that contains a map with a one-to-many relationship. The child object (ProcessActivitySetStatus) also has a map with a one-to-many relationship.
When running multiple users (i.e. multiple threads), I get database deadlocks on updating the WorkflowProcessStatus. The deadlock occurs on the table containing the WorkflowProcessStatus map to the ProcessActivitySetStatus objects.

On updating of these objects, I have to first update and add the new ProcessActivitySetStatus and get the ids before doing a final update that includes the new children for these objects so my update is a two step process.

Originally, I had all steps wrapped within a single transaction. Now, I have broken them into two separate transactions (which did not help). I have included this latest version. I have also included the Hibernate sql statements for updating one object.

Does anyone have any suggestions or notice what I am doing incorrectly to manage the persistence on these objects?



Hibernate version: 2.1.4

Mapping documents:


<hibernate-mapping>
<class
name="com.xxi.data.WorkflowProcessStatus"
table="EvenueSessionWorkflowProcessStatus"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="EvenueSessionWorkflowProcessStatusId"
type="int"
unsaved-value="0"
>
<generator class="identity">
</generator>
</id>

<property
name="applicantEvenueSessionId"
type="int"
update="true"
insert="true"
column="applicantEvenueSessionId"
not-null="true"
/>

<property
name="workflowProcessDefinitionId"
type="java.lang.Integer"
update="true"
insert="true"
column="workflowProcessDefinitionId"
not-null="true"
/>

<property
name="status"
type="int"
update="true"
insert="true"
column="status"
not-null="true"
/>

<property
name="currentActivitySetIdentifier"
type="int"
update="true"
insert="true"
column="CurrentProcessActivitySetDefinitionId"
not-null="false"
/>

<map
name="activitySetStatus"
table="EvenueSessionActivitySetStatus"
lazy="false"
sort="unsorted"
inverse="false"
cascade="all-delete-orphan"
>

<key
column="EvenueSessionWorkflowProcessStatusId"
/>

<index
column="ProcessActivitySetDefinitionId"
type="int"
/>

<one-to-many
class="com.xxi.data.ProcessActivitySetStatus"
/>
</map>

<list
name="activitySetStatusOrder"
table="EvenueSessionActivitySetStatusOrder"
lazy="false"
inverse="false"
cascade="all"
>

<key
column="EvenueSessionWorkflowProcessStatusId"
/>

<index
column="SequenceNumber"
/>

<element
column="ProcessActivitySetDefinitionId"
type="int"
not-null="false"
unique="false"
/>

</list>

</class>
<class
name="com.xxi.data.ProcessActivitySetStatus"
table="EvenueSessionActivitySetStatus"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="EvenueSessionActivitySetStatusId"
type="int"
unsaved-value="0"
>
<generator class="identity">
</generator>
</id>

<property
name="activitySetIdentifier"
type="int"
update="true"
insert="true"
column="ProcessActivitySetDefinitionId"
not-null="true"
/>

<property
name="status"
type="int"
update="true"
insert="true"
column="status"
not-null="true"
/>

<property
name="currentActivityIdentifier"
type="int"
update="true"
insert="true"
column="CurrentProcessActivityDefinitionId"
not-null="false"
/>

<map
name="activityStatus"
table="EvenueSessionActivityStatus"
lazy="false"
sort="unsorted"
inverse="false"
cascade="all-delete-orphan"
>

<key
column="EvenueSessionActivitySetStatusId"
/>

<index
column="ProcessActivityDefinitionId"
type="int"
/>

<one-to-many
class="com.xxi.data.ProcessActivityStatus"
/>
</map>

<list
name="activityStatusOrder"
table="EvenueSessionActivityStatusOrder"
lazy="false"
inverse="false"
cascade="all-delete-orphan"
>

<key
column="EvenueSessionActivitySetStatusId"
/>

<index
column="SequenceNumber"
/>

<element
column="ProcessActivityDefinitionId"
type="int"
not-null="false"
unique="false"
/>

</list>

</class>


<class
name="com.xxi.data.ProcessActivityStatus"
table="EvenueSessionActivityStatus"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="EvenueSessionActivityStatusId"
type="int"
unsaved-value="0"
>
<generator class="identity">
</generator>
</id>

<property
name="activityIdentifier"
type="int"
update="true"
insert="true"
column="ProcessActivityDefinitionId"
not-null="true"
/>

<property
name="status"
type="int"
update="true"
insert="true"
column="status"
not-null="true"
/>

<property
name="currentPage"
type="int"
update="true"
insert="true"
column="currentPage"
not-null="false"
/>

<property
name="maximumPages"
type="int"
update="true"
insert="true"
column="maximumPages"
not-null="false"
/>

<property
name="pageViewCountOrWorkflowId"
type="int"
update="true"
insert="true"
column="PageViewCount"
not-null="false"
/>

<property
name="startDate"
type="java.util.Date"
update="true"
insert="true"
column="startDate"
not-null="false"
/>

<property
name="externalIntegrationPageProgression"
type="int"
update="true"
insert="true"
column="externalIntegrationPageProgression"
/>

</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

public void saveOrUpdateEvenueSession( int evenueSessionId, Map workflowStatusMap ) throws PersistenceException, HibernateException {
for ( Iterator i = workflowStatusMap.values().iterator(); i.hasNext(); ) {
WorkflowProcessStatus status = (WorkflowProcessStatus) i.next();
status.setApplicantEvenueSessionId( evenueSessionId );
saveOrUpdate( status );
replaceHibernateCollections( status );
}
}

private Serializable save( Serializable object ) throws PersistenceException, HibernateException {
WorkflowProcessStatus status = (WorkflowProcessStatus) object;
Serializable key = null;
WorkflowProcessStatus clone = status.shallowCopy();
getContext().beginTransaction( FlushMode.COMMIT );
try {
if ( getLogger().isDebugEnabled() ) {
getLogger().debug( "Attempting add for object: " + clone + " to: WorkflowProcessStatus" );
}
key = getContext().getSession().save( clone );
if ( getLogger().isDebugEnabled() ) {
getLogger().debug( "Succeeded in add for object: " + clone + " to: WorkflowProcessStatus" );
}
if ( clone.getId() <= 0 ) {
throw new PersistenceException( "WorkflowProcessStatus has no key after the add." );
}
status.setId( clone.getId() );
}
finally {
getContext().endTransaction();
}
saveOrUpdateActivitySets( status, clone );
return key;
}

private void saveOrUpdate( Serializable object ) throws PersistenceException, HibernateException {

WorkflowProcessStatus status = (WorkflowProcessStatus) object;
if ( status.getId() <= 0 ) {
save( object );
}
else {
WorkflowProcessStatus clone = status.shallowCopy();
saveOrUpdateActivitySets( status, clone );
}
}

private void saveWorkflowProcessStatus( WorkflowProcessStatus status, Map activitySetStatusToClone ) throws PersistenceException {

// get rid of the hibernate collections
if ( status.getActivitySetStatus() instanceof net.sf.hibernate.collection.PersistentCollection ) {
status.setActivitySetStatus( new HashMap( status.getActivitySetStatus() ) );
}
if ( status.getActivitySetStatusOrder() instanceof net.sf.hibernate.collection.PersistentCollection ) {
status.setActivitySetStatusOrder( new ArrayList( status.getActivitySetStatusOrder() ) );
}

// apply the activity set clone ids to the real activity sets
for ( Iterator i = status.getActivitySetStatus().values().iterator(); i.hasNext(); ) {
ProcessActivitySetStatus setStatus = (ProcessActivitySetStatus) i.next();
if ( setStatus.getActivityStatus() instanceof net.sf.hibernate.collection.PersistentCollection ) {
setStatus.setActivityStatus( new HashMap( setStatus.getActivityStatus() ) );
}
if ( setStatus.getActivityStatusOrder() instanceof net.sf.hibernate.collection.PersistentCollection ) {
setStatus.setActivityStatusOrder( new ArrayList( setStatus.getActivityStatusOrder() ) );
}
ProcessActivitySetStatus setClone = (ProcessActivitySetStatus) activitySetStatusToClone.get( setStatus );
if ( setClone.getId() <= 0 ) {
throw new PersistenceException( "ProcessActivitySetStatus clone (" + setClone + ") has no key after the add." );
}
else {
setStatus.setId( setClone.getId() );
}
}
}

private Map saveShallowActivitySetStatus( WorkflowProcessStatus status, WorkflowProcessStatus clone, Session session ) throws HibernateException {
// first, get rid of the hibernate collections
Map activitySetStatusToClone = new HashMap( status.getActivitySetStatus().size() );
if ( status.getActivitySetStatus() instanceof net.sf.hibernate.collection.PersistentCollection ) {
status.setActivitySetStatus( new HashMap( status.getActivitySetStatus() ) );
}
if ( status.getActivitySetStatusOrder() instanceof net.sf.hibernate.collection.PersistentCollection ) {
status.setActivitySetStatusOrder( new ArrayList( status.getActivitySetStatusOrder() ) );
}

// change any new activity set statii to be shallow because the new activity statii cannot be added until the set status has an id
for ( Iterator i = status.getActivitySetStatus().values().iterator(); i.hasNext(); ) {
ProcessActivitySetStatus setStatus = (ProcessActivitySetStatus) i.next();
if ( setStatus == null ) {
i.remove();
}
else {
if ( setStatus.getActivityStatus() instanceof net.sf.hibernate.collection.PersistentCollection ) {
setStatus.setActivityStatus( new HashMap( setStatus.getActivityStatus() ) );
}
if ( setStatus.getActivityStatusOrder() instanceof net.sf.hibernate.collection.PersistentCollection ) {
setStatus.setActivityStatusOrder( new ArrayList( setStatus.getActivityStatusOrder() ) );
}
if ( setStatus.getId() <= 0 ) {
ProcessActivitySetStatus setClone = setStatus.shallowCopy();
setClone.setActivityStatusOrder( new ArrayList( setStatus.getActivityStatusOrder() ) );
activitySetStatusToClone.put( setStatus, setClone );
clone.addActivitySetStatusWithoutImpactingOrder( setClone.getActivitySetIdentifier(), setClone );
}
else {
activitySetStatusToClone.put( setStatus, setStatus );
clone.addActivitySetStatusWithoutImpactingOrder( setStatus.getActivitySetIdentifier(), setStatus );
}
}
}
clone.setActivitySetStatusOrder( new ArrayList( status.getActivitySetStatusOrder() ) );

if ( getLogger().isDebugEnabled() ) {
getLogger().debug( "Saving clone as: " + clone );
}

session.evict( clone );
session.update( clone );
return activitySetStatusToClone;
}

private void saveOrUpdateActivitySets( WorkflowProcessStatus status, WorkflowProcessStatus clone ) throws PersistenceException, HibernateException {
Map activitySetStatusToClone = null;
getContext().beginTransaction( FlushMode.COMMIT );
try {
activitySetStatusToClone = saveShallowActivitySetStatus( status, clone, getContext().getSession() );
getContext().commitTransaction();
}
catch ( HibernateException e ) {
getContext().transactionException();
throw e;
}
catch ( PersistenceException e ) {
getContext().transactionException();
throw e;
}
finally {
getContext().endTransaction();
}
saveWorkflowProcessStatus( status, activitySetStatusToClone );
getContext().beginTransaction( FlushMode.COMMIT );
try {
getContext().getSession().update( status );
getContext().commitTransaction();
}
catch ( HibernateException e ) {
getContext().transactionException();
throw e;
}
catch ( PersistenceException e ) {
getContext().transactionException();
throw e;
}
finally {
getContext().endTransaction();
}
}


Full stack trace of any exception that occurs:
Exception committing a transaction...exception: could not delete collection: [com.xxi.data.WorkflowProcessStatus.activitySetStatus#199]
com.xxi.persistence.api.exceptions.PersistenceException: Exception committing a transaction...exception: could not delete collection: [com.xxi.data.WorkflowProcessStatus.activitySetStatus#199]
at com.xxi.persistence.impl.db.hibernate.HibernatePersistenceContext.commitTransaction(HibernatePersistenceContext.java:316)
at com.xxi.persistence.WorkflowProcessStatusPersistenceModuleClean.saveOrUpdateActivitySets(WorkflowProcessStatusPersistenceModuleClean.java:210)
at com.xxi.persistence.WorkflowProcessStatusPersistenceModuleClean.save(WorkflowProcessStatusPersistenceModuleClean.java:97)
at com.xxi.persistence.WorkflowProcessStatusPersistenceModuleClean.saveOrUpdate(WorkflowProcessStatusPersistenceModuleClean.java:105)
at com.xxi.persistence.WorkflowProcessStatusPersistenceModuleClean.saveOrUpdateEvenueSession(WorkflowProcessStatusPersistenceModuleClean.java:71)
at com.xxi.test.ApplicantEvenueSessionTask.executeAndSave(ApplicantEvenueSessionTask.java:89)
at com.xxi.test.ApplicantEvenueSessionTask.run(ApplicantEvenueSessionTask.java:225)
at com.xxi.test.ApplicantEvenueSessionTask$2.run(ApplicantEvenueSessionTask.java:210)
at java.util.TimerThread.mainLoop(Timer.java:432)
at java.util.TimerThread.run(Timer.java:382)
Caused by: net.sf.hibernate.JDBCException: could not delete collection: [com.xxi.data.WorkflowProcessStatus.activitySetStatus#199]
at net.sf.hibernate.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:496)
at net.sf.hibernate.impl.ScheduledCollectionRemove.execute(ScheduledCollectionRemove.java:22)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2414)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2369)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2236)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at com.xxi.persistence.impl.db.hibernate.HibernatePersistenceContext$TransactionLocal.commitTransaction(HibernatePersistenceContext.java:173)
at com.xxi.persistence.impl.db.hibernate.HibernatePersistenceContext.commitTransaction(HibernatePersistenceContext.java:305)
... 9 more
Caused by: com.inet.tds.SQLException: Msg 1205, Level 13, State 50, Line 1, Sqlstate 40001
[BRUTESQUAD]Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.inet.tds.a.a(Unknown Source)
at com.inet.tds.a.a(Unknown Source)
at com.inet.tds.c.int(Unknown Source)
at com.inet.tds.c.executeUpdate(Unknown Source)
at com.mchange.v2.sql.filter.FilterPreparedStatement.executeUpdate(FilterPreparedStatement.java:71)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at net.sf.hibernate.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:486)
... 16 more


Name and version of the database you are using: MS SQLServer 2000 with SP3a

The generated SQL (show_sql=true):
Open session (net.sf.hibernate.impl.SessionImpl@1f30035) on Thread[Thread-6,5,main]
Begin transaction (net.sf.hibernate.transaction.JDBCTransaction@92af24) for com.xxi.persistence.impl.db.hibernate.HibernatePersistenceContext on thread: Thread[Thread-6,5,main]
Hibernate: insert into EvenueSessionActivityStatus (ProcessActivityDefinitionId, status, currentPage, maximumPages, PageViewCount, startDate, externalIntegrationPageProgression) values (?, ?, ?, ?, ?, ?, ?) select SCOPE_IDENTITY()
Hibernate: insert into EvenueSessionActivityStatus (ProcessActivityDefinitionId, status, currentPage, maximumPages, PageViewCount, startDate, externalIntegrationPageProgression) values (?, ?, ?, ?, ?, ?, ?) select SCOPE_IDENTITY()
Hibernate: insert into EvenueSessionActivityStatus (ProcessActivityDefinitionId, status, currentPage, maximumPages, PageViewCount, startDate, externalIntegrationPageProgression) values (?, ?, ?, ?, ?, ?, ?) select SCOPE_IDENTITY()
Hibernate: update EvenueSessionWorkflowProcessStatus set applicantEvenueSessionId=?, workflowProcessDefinitionId=?, status=?, CurrentProcessActivitySetDefinitionId=? where EvenueSessionWorkflowProcessStatusId=?
Hibernate: update EvenueSessionActivitySetStatus set ProcessActivitySetDefinitionId=?, status=?, CurrentProcessActivityDefinitionId=? where EvenueSessionActivitySetStatusId=?
Hibernate: update EvenueSessionActivitySetStatus set EvenueSessionWorkflowProcessStatusId=null, ProcessActivitySetDefinitionId=null where EvenueSessionWorkflowProcessStatusId=?
Hibernate: delete from EvenueSessionActivitySetStatusOrder where EvenueSessionWorkflowProcessStatusId=?
Hibernate: update EvenueSessionActivityStatus set EvenueSessionActivitySetStatusId=null, ProcessActivityDefinitionId=null where EvenueSessionActivitySetStatusId=?
Hibernate: delete from EvenueSessionActivityStatusOrder where EvenueSessionActivitySetStatusId=?
Hibernate: update EvenueSessionActivitySetStatus set EvenueSessionWorkflowProcessStatusId=?, ProcessActivitySetDefinitionId=? where EvenueSessionActivitySetStatusId=?
Hibernate: insert into EvenueSessionActivitySetStatusOrder (EvenueSessionWorkflowProcessStatusId, SequenceNumber, ProcessActivitySetDefinitionId) values (?, ?, ?)
Hibernate: update EvenueSessionActivityStatus set EvenueSessionActivitySetStatusId=?, ProcessActivityDefinitionId=? where EvenueSessionActivityStatusId=?
Hibernate: insert into EvenueSessionActivityStatusOrder (EvenueSessionActivitySetStatusId, SequenceNumber, ProcessActivityDefinitionId) values (?, ?, ?)
Committing transaction: net.sf.hibernate.transaction.JDBCTransaction@92af24 on thread: Thread[Thread-6,5,main]
End transaction for com.xxi.persistence.impl.db.hibernate.HibernatePersistenceContext on thread: Thread[Thread-6,5,main]

Debug level Hibernate log excerpt:

I did not necessarily resolve the described issue but, after running several test variations, I did make changes to eliminate the problem so I could move on.

First, as expected, I did not find that this had anything at all to do with Hibernate or how I had it configured. Tests using straight JDBC resulted in the same deadlock situation.

Second, it appears to be specifically a MS SQLServer issue. Running the same process using MySQL as the database had no problems/deadlocks.

Finally, experimenting with turning locks off and using locking hints did not help at all. Perhaps an expert SQLServer person could understand the issue but, after researching and reading, I could not find any solution.

What did I do:
1. Redesigned the object to reduce the tables hit from 5 to 3.
2. Made individual transactions/commit for each object...leaving me with no true error recovery. Luckily, this can be ok for these objects.

In the end, while performance is reduced and code increased, I have no deadlocks.

I am still open to any input but am moving on to other problems:)

deadlock on current update is not a RDMS specific issue, it must happen in some cases and it is trivial to reproduce it on any transactional database.
There are a few workarounds to avoid deadlock:
1. redeign schema to eleminate conflicts.
2. custom locking to serialize transactions manualy.
3. commit per update.
4. resubmit
There is no the best workaround to solve this problem, it depends on use case.