title: Hibernate second level cache and ON DELETE CASCADE in database schema
body: <p>Our Java application has about 100 classes mapped to a database (SQL Server or MySQL). We are using Hibernate as our ORM (with XML mapping files).</p>
  
  <p>We specify <code>FOREIGN KEY</code> constraints in our database schema. Most of our <code>FOREIGN KEY</code> constraints also specify <code>ON DELETE CASCADE</code>.</p>
  
  <p>We've recently started enabling Hibernate 2nd level caching (for popular entities and collections) to mitigate some performance problems.</p>
  
  <p>Performance has improved since we enabled the 2nd level cache. However we've also started encountering ObjectNotFoundExceptions.</p>
  
  <p>It seems the ObjectNotFoundExceptions are occuring because the database is deleting table rows <strong><em>underneath</em></strong> Hibernate. For example, when we delete a <code>Parent</code> with Hibernate, the database schema will <code>ON DELETE CASCADE</code> to any <code>Child</code> entities. This obviously happens without Hibernates knowledge, so it doesn't get a chance to update the 2nd level cache (and remove any deleted <code>Child</code> entities).</p>
  
  <p>We believe the solution to this problem is to remove <code>ON DELETE CASCADE</code> from our database schema (but keep the <code>FOREIGN KEY</code>s). Instead, we need to configure Hibernate to delete <code>Child</code> dependencies with normal delete SQL which will also make Hibernate update the 2nd level cache. Some limited testing has shown that this approach seems to work.</p>
  
  <p>I wanted to get some community feedback on this. Are there alternative (better?) solutions to our problem? How do others handle this situation? In general, what are the tradeoffs that should be considered when using <code>ON DELETE CASCADE</code> in a database schema with Hibernate?</p>
  
  <p>Thanks.</p>