7

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.

Bat Fastard
  • 73
  • 1
  • 3

2 Answers2

2

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.

bwawok
  • 14,898
  • 7
  • 32
  • 43
  • Good point. The database is only accessed and updated by the Java code in our application. This situation is unlikely to change. So removing the ON DELETE CASCADEs would NOT affect any other apps/scripts/etc. – Bat Fastard Jun 21 '10 at 21:46
  • Then go ahead and do it through hibernate – bwawok Jun 21 '10 at 22:36
  • Why not leave the database `ON DELETE CASCADE` in combination with hibernate delete cascade? database deletes it, hibernate tries to delete cascaded child's but nothing to delete. So nothing should happen or am I wrong? – djmj Jul 31 '13 at 16:49
  • I think that could be a race condition. Hibernate sends the delete for Parent and Children A, B,C. Database gets delete of parent A, then tries to delete children A, B,C. Hibernate delete of these 3 fail. What does Hibernate do? Error? Silent exception? Not sure... seems like you shouldn't put 2 guys in charge of deleting the same thing – bwawok Jul 31 '13 at 19:14
  • Hmm I think there was no problem, must evaluate this again. Is it possible to enforce the 2nd Level cache to recheck entities for update or deletion, maybe within an Interceptor?. I am currently struggling with this problem myself and I do not want to remove my database cascading effects. Implementing `CASCADE` or `SET NULL` could maybe be realized with an interceptor or EntityListener. Will check this. – djmj Aug 20 '13 at 13:35
1

If you are using ON DELETE CASCADE in your database you need to tell hibernate, like this:

@OnDelete(action = OnDeleteAction.CASCADE)

this is different from 

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)

The latter is telling hibenrate something about the in-memory relationship. the first one optimizes delete SQL Statements on the database level. Hibernate needs to know that the DB is taking care of delete childs.

Take a look at this site for a good explanation of this mechanism:

http://eddii.wordpress.com/2006/11/16/hibernate-on-deletecascade-performance/

and the comment from the developer of this feature:

http://www.mail-archive.com/hibernate-devel@lists.sourceforge.net/msg03801.html

Janning Vygen
  • 8,877
  • 9
  • 71
  • 102
  • Does second level cache correctly work with @OnDelete ? All, what I manage to find: no - http://stackoverflow.com/a/21155878/548473. – Grigory Kislin Jan 14 '17 at 14:41
  • 1
    It works! It works with cache, no problem at all. But as always: You need to understand what it is doing and what hibernate expects – Janning Vygen Jan 14 '17 at 18:32