We're updating the issue view to help you get more done. 

DML batch delete re-firing SQL from previous calls

Description

I was using HQL DML to delete entities in batch, but noticed the longer the process ran, the # of entities I was deleting per second was decreasing. When I dumped the stack, was always in MySQL transmission, not in hibernate code. When I enabled SQL dumps, I saw sql from previously deleted entities being re-fired, which explained the exponential slowdown of my application.

I traced it to code that is invoked when a BulkIdStrategy is required. In my case, I'm using InlineIdsInClauseBulkIdStrategy and had a joined inheritance mapping requiring multiple deletes from child tables. It appears the DML query is being cached and within AbstractInlineIdsDeleteHandlerImpl, there is a "deletes" member variable that is being appended to on each execution.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 @Entity @Inheritance(strategy = InheritanceType.JOINED) @Table(name="test_entity") public class TestEntity { @Id int id; } @Entity @Table(name="test_entity_type1") public class TestEntityType1 extends TestEntity { } @Entity @Table(name="test_entity_type2") public class TestEntityType2 extends TestEntity { }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 create table test_entity (id integer not null auto_increment, primary key (id)) engine=InnoDB; create table test_entity_type1 (id integer not null auto_increment, primary key (id)) engine=InnoDB; create table test_entity_type2 (id integer not null auto_increment, primary key (id)) engine=InnoDB; alter table test_entity_type1 add constraint FKnup5qde9q5309bofv03i80kg1 foreign key (id) references test_entity (id); alter table test_entity_type2 add constraint FKnup5qde9q5309bofv03i80kg2 foreign key (id) references test_entity (id); insert into test_entity values(1); insert into test_entity_type1 values(1); insert into test_entity values(2); insert into test_entity_type1 values(2); insert into test_entity values(3); insert into test_entity_type2 values(3); insert into test_entity values(4); insert into test_entity_type2 values(4);
1 2 3 4 5 6 7 for (int i = 1; i <= 4; i++) { Query deleteQuery = em.createQuery("delete TestEntity e where e.id = :id"); deleteQuery.setParameter("id", i); deleteQuery.executeUpdate(); System.out.println("==================================================="); }

Output:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Hibernate: select testentity0_.id as id from test_entity testentity0_ where testentity0_.id=? Hibernate: delete from test_entity_type1 where (id) in ((1)) Hibernate: delete from test_entity_type2 where (id) in ((1)) Hibernate: delete from test_entity where (id) in ((1)) =================================================== Hibernate: select testentity0_.id as id from test_entity testentity0_ where testentity0_.id=? Hibernate: delete from test_entity_type1 where (id) in ((1)) Hibernate: delete from test_entity_type2 where (id) in ((1)) Hibernate: delete from test_entity where (id) in ((1)) Hibernate: delete from test_entity_type1 where (id) in ((2)) Hibernate: delete from test_entity_type2 where (id) in ((2)) Hibernate: delete from test_entity where (id) in ((2)) =================================================== Hibernate: select testentity0_.id as id from test_entity testentity0_ where testentity0_.id=? Hibernate: delete from test_entity_type1 where (id) in ((1)) Hibernate: delete from test_entity_type2 where (id) in ((1)) Hibernate: delete from test_entity where (id) in ((1)) Hibernate: delete from test_entity_type1 where (id) in ((2)) Hibernate: delete from test_entity_type2 where (id) in ((2)) Hibernate: delete from test_entity where (id) in ((2)) Hibernate: delete from test_entity_type1 where (id) in ((3)) Hibernate: delete from test_entity_type2 where (id) in ((3)) Hibernate: delete from test_entity where (id) in ((3)) =================================================== Hibernate: select testentity0_.id as id from test_entity testentity0_ where testentity0_.id=? Hibernate: delete from test_entity_type1 where (id) in ((1)) Hibernate: delete from test_entity_type2 where (id) in ((1)) Hibernate: delete from test_entity where (id) in ((1)) Hibernate: delete from test_entity_type1 where (id) in ((2)) Hibernate: delete from test_entity_type2 where (id) in ((2)) Hibernate: delete from test_entity where (id) in ((2)) Hibernate: delete from test_entity_type1 where (id) in ((3)) Hibernate: delete from test_entity_type2 where (id) in ((3)) Hibernate: delete from test_entity where (id) in ((3)) Hibernate: delete from test_entity_type1 where (id) in ((4)) Hibernate: delete from test_entity_type2 where (id) in ((4)) Hibernate: delete from test_entity where (id) in ((4)) ===================================================

Environment

None

Status

Assignee

Unassigned

Reporter

Scott Werner

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.3.7
5.4.0

Priority

Major