The following Hibernate mapping shows that I have an Item class that contains a one-to-many set of Field classes, which contains a list of string values. The Item objects are held in the items table, the Field objects in the item_fields and the string values in the item_field_values table.

<class name="Field" entity-name="Field" table="item_fields" lazy="true">
<composite-id>
<key-property name="field_id" column="field_id"/>
<key-property name="item_id" column="item_id"/>
</composite-id>

<list name="values" lazy="true" table="item_field_values">
<key>
<column name="field_id"/>
<column name="item_id"/>
</key>

<list-index column="position"/>
<element type="string" column="field_value" not-null="false"/>
</list>

<property name="name" column="field_name" type="string" not-null="true"/>
</class>

<class name="Item" table="items" lazy="true">
<id name="item_id" type="long">
<column name="item_id" />
<generator class="assigned" />
</id>

<set name="itemData" inverse="true" cascade="persist,merge,save-update,evict,replicate,lock,refresh,delete-orphan" lazy="true">
<key column="item_id" on-delete="cascade"/>
<one-to-many entity-name="Field"/>
</set>

</class>

The associated DB tables have foreign keys with 'ON DELETE CASCADE' specified so that a delete on the items table will cascade onto all other tables.

When I create and populate the various objects and persist them they are all written correctly to the DB tables.

The issue is that when I delete an Item object the SQL being run includes a DELETE operation on the item_field_values table, which is not required because of the foreign key constraint with the 'ON DELETE CASCADE'.

There is no SQL DELETE run for the item_fields table since this has 'on-delete="cascade"' defined on the sets key element.

How can I stop the DELETE operation on the item_field_values table being run?

This causes a problem since the cascaded delete run by the DB will change the isolation level being used to serializable due to the existing lock on the item_field_values table.

With other threads this can result in a deadlock.

These are the SQL operations output from Hibernate when deleting a Item object:

Hibernate: select item_.item_id from items item_ where item_.item_id=?
Hibernate: select field_.field_id, field_.item_id, field_.field_name as field3_0_ from item_fields field_ where field_.field_id=? and field_.item_id=?
Hibernate: select field_.field_id, field_.item_id, field_.field_name as field3_0_ from item_fields field_ where field_.field_id=? and field_.item_id=?
Hibernate: select field_.field_id, field_.item_id, field_.field_name as field3_0_ from item_fields field_ where field_.field_id=? and field_.item_id=?
Hibernate: delete from item_field_values where field_id=? and item_id=?
Hibernate: delete from item_field_values where field_id=? and item_id=?
Hibernate: delete from item_field_values where field_id=? and item_id=?
Hibernate: delete from items where item_id=?

I tried adding 'inverse="true" cascade="persist,merge,save-update,evict,replicate,lock,refresh,delete-orphan"' to the item_field_values 'list' definition in the mapping file but this results in none of the values being persisted to the DB.

Found the solution. I was so stuck on getting a setting right for the list element in the Field class definition in the Hibernate mapping file, that I didn't check the set definition in the Item class.

All I need to do was remove the 'delete-orphan' setting from the itemData set definition in the Hibernate mapping file. It then becomes:

<set name="itemData" inverse="true" cascade="persist,merge,save-update,evict,replicate,lock,refresh" lazy="true">
<key column="item_id" on-delete="cascade"/>
<one-to-many entity-name="Field"/>
</set>