Hibernate second level cache and ON DELETE CASCADE in database schema

Question

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).

We specify FOREIGN KEY constraints in our database schema. Most of our FOREIGN KEY constraints also specify ON DELETE CASCADE.

We've recently started enabling Hibernate 2nd level caching (for popular entities and collections) to mitigate some performance problems.

Performance has improved since we enabled the 2nd level cache. However we've also started encountering ObjectNotFoundExceptions.

It seems the ObjectNotFoundExceptions are occuring because the database is deleting table rows underneath Hibernate. For example, when we delete a Parent with Hibernate, the database schema will ON DELETE CASCADE to any Child entities. This obviously happens without Hibernates knowledge, so it doesn't get a chance to update the 2nd level cache (and remove any deleted Child entities).

We believe the solution to this problem is to remove ON DELETE CASCADE from our database schema (but keep the FOREIGN KEYs). Instead, we need to configure Hibernate to delete Child 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.

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 ON DELETE CASCADE in a database schema with Hibernate?

Thanks.

Answer 1

If you are always going to delete through your program, you want to take the constraint off the database and tell the hibernate object to ON DELETE CASCADE to take care of the related guys.

On the other hand, if you are going to delete objects sometimes in your java app, and sometimes at your database level, you will end up with weird hanging data. In this case you may need to look into a more complicated approach.. You weren't clear if this was the case, so not going to go into more detail here.