Hi All,
I hope it's okay that I posted my issue to StackOverflow as well: http://stackoverflow.com/questions/13123601/hibernate-delete-insert-database-blocking-lock
I figured that Hibernate Forums and StackOverflow would be the best resources for getting insight and ideas regarding our Hibernate Delete/Insert Database Blocking Lock issue.
We are using:
- Hibernate 3.6.1.FINAL
- Oracle 11g
We are experiencing blocking locks in our database that I believe is caused by an unexpected side-effect of Hibernate.
This is a critical issue we need to resolve, as the database blocking locks quickly snowball and transactions begin timing out, causing a production outage.
I am trying to figure out if there is a certain scenario in which Hibernate can do a behind-the-scenes delete and re-insert of a collection of children, or some way that it could cause blocking locks on a delete/insert.
I'm looking for ideas/suggestions/insight about possible causes, not necessarily an exact answer.
I've isolated the entities involved and their mappings. Unfortunately due to the fact that we have a huge legacy system with tons of code, I had to come up with a simplified example:
Parent.hbm.xmlCode:
// Parent has a unique ID generated with a Sequence,
// an index column called ORD, and a Version column called UPDATE_COUNTER.
<!-- bi-directional one-to-many association to Child -->
<bag
name="children"
lazy="false"
inverse="true"
cascade="all-delete-orphan"
order-by="ord asc">
<key column="PARENT_ID" />
<one-to-many class="Child" />
</bag>
Child.hbm.xmlCode:
// Child has a unique ID generated with a Sequence,
// and a Version column called UPDATE_COUNTER.
<!-- bi-directional many-to-one association to Parent -->
<many-to-one
name="parent"
class="Parent"
not-null="true"
>
<column name="PARENT_ID" />
</many-to-one>
Our DBAs have reported that they see the following:
The Blocked SQL:
Code:
insert into CHILD (UPDATE_COUNTER, ORD, PARENT_ID, ID) values (:1 , :2 , :3 , :4)
The Blocking SQL:
Code:
delete from CHILD where ID=:1 and UPDATE_COUNTER=:2
Here's what I've tried thus far:
- Did a full audit of our codebase, established that there is nowhere where we delete and insert a row with the same primary key. All of our INSERT for Child is done with ID set to null, in which case the Sequence will generate the Unique ID.
- Turned on show_sql. Also, DBAs set up a trigger on Child to capture all DML activity so we could get the bind variables in development.
I found the following articles saying that Hibernate could do delete all then re-insert for bags and lists:
Why Hibernate does "Delete all then re-insert": http://assarconsulting.blogspot.com/2009/08/why-hibernate-does-delete-all-then-re.html
Hibernate - set, map, bag & list differences: http://dannylesnik.blogspot.com/2010/10/hibernate-set-map-bag-list-differences.html
However, I determined that this is not occurring. I wrote test code that removes one of the Child objects in the middle (ORD=5 with a collection of 10 children that had ORD=0 through ORD=9) and then saved Parent.
I saw in the DML activity table that a delete was issued for the removed Child and UPDATE was done for the remaining 9 children that set their ORD to the correct value.
As I mentioned, I'm not looking for an exact answer. I would be tremendously for insight or suggestions as to additional paths or avenues I could run down.
Thank you,
Philip Tenn
Login
FAQ
Search