Grails cascade delete on parent is deleting child records one by one

Question

Using Grails 2.4.4, Hibernate 4.3.6.1 plugin...

We have a domain object with a parent/child one-to-many relationship using a hasMany/belongsTo. When deleting the parent record, Grails cascades the deletes to the child records. However, when looking at the SQL, I see that Grails is fetching the child records and deleting them one-by-one using delete ... where id = ? and version = ?

This is a horribly inefficient way to delete the child records and I'm fairly sure Grails didn't do this when I initially wrote this code (I can't find anything that specifically causes this behavior).

I would expect Grails/Hibernate to run a query more like:

delete from Child where parent_id = ?

So that all child records are deleting in one query, instead of thousands of queries.

Is there something in the mappings/config that would cause Grails to behave in this manner?

I'm pretty sure that since you are using optimistic locking (e.g. you haven't disabled version) on your child instances it must check each instance by version, and thus delete them one at a time. This is one of the many gotchas when using GORM. — Joshua Moore
Interesting, I can certainly disable optimistic locking and see if it changes the behavior. Generally speaking, I think optimistic locking is a false sense of security anyway. — Aaron
So removing the optimistic locking has no affect other than to remove the version = ? part from the delete statement. — Aaron

Burt Beckwith Burt Beckwith · Accepted Answer · 2015-03-25T03:26:59

It's easy enough to explicitly delete the child objects and the parent object in a transactional service method. Remove the hasMany collection, and convert the belongsTo to a regular property, e.g.

class Author {
   String name
}

and

class Book {
   String title
   Author author
}

and then you can efficiently delete the books and the author in two queries:

void deleteAuthor(Author a) {
   Book.where { author == a }.deleteAll()
   a.delete()
}

Watch this presentation which shows additional reasons why you should avoid using collections.

Grails cascade delete on parent is deleting child records one by one

1 Answers