top of page
Search
Writer's pictureLeonard Anghel

How To Batch Deletes Via SQL ON DELETE CASCADE

Updated: May 10, 2020

Motivation:

You search for a fast solution to delete the parents and cascade deletion to the associated children. The SQL, ON DELETE CASCADE perform cascading of deletion at database-level, and methods such deleteAllInBatch() and deleteInBatch() can take advantage of it. There is no need to load the data in the Persistence Context to take advantage of application-level, CascadeType.REMOVE and/or orphanRemoval=true. Nevertheless, you can use ON DELETE CASCADE with deleteAll() and delete(), but mixing batching with database automatic actions (ON DELETE CASCADE) will result in a partially synchronized Persistence Context.


150+ PERSISTENCE PERFORMANCE ITEMS

THAT WILL ROCK YOUR APPS

Description:

This application is an example of batching deletes (in MySQL, but any other database can be used) via ON DELETE CASCADE. Auto-generated database schema will contain the ON DELETE CASCADE directive:

Note: Spring deleteAllInBatch() and deleteInBatch() don't use delete batching and don't take advantage of cascading removal, orphanRemoval and automatic optimistic locking mechanism to prevent lost updates (e.g., @Version is ignored), but both of them take advantage on ON DELETE CASCADE and are very efficient. They trigger bulk operations via Query.executeUpdate(), therefore, the Persistence Context is not synchronized accordingly (it's up to you to flush (before delete) and close/clear (after delete) the Persistence Context accordingly to avoid issues created by unflushed (if any) or outdated (if any) entities). The first one simply triggers a delete from entity_name statement, while the second one triggers a delete from entity_name where id=? or id=? or id=? ... statement. For delete in batches rely on deleteAll(), deleteAll(Iterable<? extends T> entities) or delete() method. Behind the scene, the two flavors of deleteAll() relies on delete(). Mixing batching with database automatic actions (ON DELETE CASCADE) will result in a partially synchronized Persistent Context.


Key points:

  • In this application, we have a Author entity and each author can have several Book (one-to-many)

  • First, we remove orphanRemoval or set it to false

  • Second, we use only CascadeType.PERSIST and CascadeType.MERGE

  • Third, we set @OnDelete(action = OnDeleteAction.CASCADE) next to @OneToMany

  • Fourth, we set spring.jpa.properties.hibernate.dialect to org.hibernate.dialect.MySQL5InnoDBDialect (or, MySQL8Dialect)

  • Fifth, we run through each deleteFoo() method

Tam Ta Da Dam! :) The complete application is available on GitHub.


If you need a deep dive into the performance recipes exposed in this repository then I am sure that you will love my book "Spring Boot Persistence Best Practices".



1,832 views0 comments

Recent Posts

See All

How To Bulk Updates

Motivation: This article is useful if you need a fast way to update a significant amount of data in the database. Bulk operations...

Comentarios


bottom of page