title: Hibernate delete/insert database blocking lock
body: <p>We are using <code>Hibernate 3.6.1.FINAL</code> against <code>Oracle 11g</code>.  </p>
  
  <p>We are experiencing blocking locks in our database that I believe is caused by an unexpected side-effect of Hibernate. </p>
  
  <p>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.</p>
  
  <p><strong>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.</strong></p>
  
  <p>I'm looking for ideas/suggestions/insight about possible causes, not necessarily an exact answer.</p>
  
  <p>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:</p>
  
  <p><strong>Parent.hbm.xml</strong></p>
  
  <pre><code>// Parent has a unique ID generated with a Sequence,
  // an index column called ORD, and a Version column called UPDATE_COUNTER.
  &lt;!-- bi-directional one-to-many association to Child --&gt;
  &lt;bag
      name="children"
      lazy="false"
      inverse="true"
      cascade="all-delete-orphan"
      order-by="ord asc"&gt;
      &lt;key column="PARENT_ID" /&gt;
      &lt;one-to-many class="Child" /&gt;
  &lt;/bag&gt;
  </code></pre>
  
  <p><strong>Child.hbm.xml</strong></p>
  
  <pre><code>// Child has a unique ID generated with a Sequence, 
  // and a Version column called UPDATE_COUNTER.
  &lt;!-- bi-directional many-to-one association to Parent --&gt;
  &lt;many-to-one
      name="parent"
      class="Parent"
      not-null="true"
  &gt;
      &lt;column name="PARENT_ID" /&gt;
  &lt;/many-to-one&gt;
  </code></pre>
  
  <p>Our DBAs have reported that they see the following:</p>
  
  <p>The Blocked SQL: </p>
  
  <pre><code>insert into CHILD (UPDATE_COUNTER, ORD, PARENT_ID, ID) values (:1 , :2 , :3 , :4)
  </code></pre>
  
  <p>The Blocking SQL: </p>
  
  <pre><code>delete from CHILD where ID=:1  and UPDATE_COUNTER=:2
  </code></pre>
  
  <p>Here's what I've tried thus far:</p>
  
  <ol>
  <li><p>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.</p></li>
  <li><p>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.  </p>
  
  <p>I found the following articles saying that Hibernate could do delete all then re-insert for bags and lists:</p>
  
  <p><a href="http://assarconsulting.blogspot.com/2009/08/why-hibernate-does-delete-all-then-re.html" rel="nofollow">Why Hibernate does "Delete all then re-insert"</a></p>
  
  <p><a href="http://dannylesnik.blogspot.com/2010/10/hibernate-set-map-bag-list-differences.html" rel="nofollow">Hibernate - set, map, bag &amp; list differences</a></p>
  
  <p>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.  </p>
  
  <p>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.  </p></li>
  </ol>
  
  <p>As I mentioned, I'm not looking for an exact answer.  I would just be tremendously grateful if anyone who has a lot of experience with Hibernate could lend any insight or suggestions as to additional paths or avenues I could run down. </p>
  
  <p>Thank you very much in advance. </p>