Out of order cascaded delete

[frensjan]

I have a tree like data structure expressed with a @ManyToOne parent field and a @OneToMany children field. The tree roots are referenced from a container with @OneToMany. The cascade setting for the @OneToMany fields is CascadeType.ALL. When deleting a particular structure of a tree, the deletes are reordered by ebean causing a Referential integrity constraint violation error.

Steps to reproduce

Set up a data structure like below:

@Entity
public class TreeBean {

    @Id
    @GeneratedValue
    public long id;

    @ManyToOne
    private TreeBean parent;

    @ManyToOne
    private TreeContainerBean treeContainerBean;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "parent")
    private List<TreeBean> children;

    ...
}

@Entity
public class TreeContainerBean {

    @Id
    @GeneratedValue
    public long id;

    @OneToMany(cascade = CascadeType.ALL)
    private List<TreeBean> trees;

    ...
}

Instantiate the tree data structure like:

root
+ child 1
+ child 1a
  - child 1a1
+ child 2
  - child 2a
  - child 2b

referenced from a 'container' bean.

The following tests fails at the database.delete(...) statement.

TreeBean root = new TreeBean();

TreeBean child1 = new TreeBean();
TreeBean child1a = new TreeBean();
TreeBean child1a1 = new TreeBean();
root.getChildren().add(child1);
child1.getChildren().add(child1a);
child1a.getChildren().add(child1a1);

TreeBean child2 = new TreeBean();
TreeBean child2a = new TreeBean();
TreeBean child2b = new TreeBean();
root.getChildren().add(child2);
child2.getChildren().add(child2a);
child2.getChildren().add(child2b);

TreeContainerBean container = new TreeContainerBean();
container.getTrees().add(root);

database.save(container);
database.delete(container);

With the following logging output:

2019-11-04 13:43:42,460 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] insert into tree_container_bean default values; -- bind()
2019-11-04 13:43:42,472 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] insert into tree_bean (parent_id, tree_container_bean_id) values (?,?)
2019-11-04 13:43:42,472 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(null,1)
2019-11-04 13:43:42,475 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] insert into tree_bean (parent_id, tree_container_bean_id) values (?,?)
2019-11-04 13:43:42,475 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(1,null)
2019-11-04 13:43:42,475 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(1,null)
2019-11-04 13:43:42,476 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] insert into tree_bean (parent_id, tree_container_bean_id) values (?,?)
2019-11-04 13:43:42,477 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(2,null)
2019-11-04 13:43:42,477 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(3,null)
2019-11-04 13:43:42,477 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(3,null)
2019-11-04 13:43:42,478 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] insert into tree_bean (parent_id, tree_container_bean_id) values (?,?)
2019-11-04 13:43:42,478 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(4,null)
2019-11-04 13:43:42,513 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] select t0.id from tree_bean t0 where tree_container_bean_id=? ; --bind(1)
2019-11-04 13:43:42,517 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] select t0.id from tree_bean t0 where (parent_id) in (?); --bind(Array[1]={1})
2019-11-04 13:43:42,519 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] select t0.id from tree_bean t0 where (parent_id) in (?,?); --bind(Array[2]={2,3})
2019-11-04 13:43:42,519 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] select t0.id from tree_bean t0 where (parent_id) in (?,?,?); --bind(Array[3]={4,5,6})
2019-11-04 13:43:42,520 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] select t0.id from tree_bean t0 where (parent_id) in (?); --bind(Array[1]={7})
2019-11-04 13:43:42,525 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] delete from tree_bean where id  in (?)
2019-11-04 13:43:42,526 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(Array[1]={7})
2019-11-04 13:43:42,526 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] delete from tree_bean where id  in (?,?,?)
2019-11-04 13:43:42,526 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(Array[3]={4,5,6})
2019-11-04 13:43:42,526 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[] delete from tree_bean where id  in (?,?)
2019-11-04 13:43:42,527 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(Array[2]={2,3})
2019-11-04 13:43:42,527 DEBUG [main] io.ebeaninternal.server.logger.DSpiLogger:26 - txn[]  -- bind(Array[1]={1})

io.ebean.DataIntegrityException: Error when batch flush on sql: delete from tree_bean where id  in (?)
	at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:49)
	at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:231)
	at io.ebeaninternal.server.transaction.TransactionManager.translate(TransactionManager.java:243)
	at io.ebeaninternal.server.transaction.JdbcTransaction.translate(JdbcTransaction.java:685)
	at io.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:828)
	at io.ebeaninternal.server.persist.DefaultPersister.delete(DefaultPersister.java:884)
	at io.ebeaninternal.server.persist.DefaultPersister.deleteRequest(DefaultPersister.java:600)
	at io.ebeaninternal.server.persist.DefaultPersister.deleteRequest(DefaultPersister.java:580)
	at io.ebeaninternal.server.persist.DefaultPersister.delete(DefaultPersister.java:572)
	at io.ebeaninternal.server.core.DefaultServer.delete(DefaultServer.java:2012)
	at io.ebeaninternal.server.core.DefaultServer.delete(DefaultServer.java:2003)
	at ....DeleteTreeTest.fails(DeleteTreeTest.java:37)
	...
Caused by: org.h2.jdbc.JdbcBatchUpdateException: Referential integrity constraint violation: "FK_TREE_BEAN_PARENT_ID: PUBLIC.TREE_BEAN FOREIGN KEY(PARENT_ID) REFERENCES PUBLIC.TREE_BEAN(ID) (1)"; SQL statement:
delete from tree_bean where id  in (?) [23503-193]
	at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1208)
	at io.ebean.datasource.delegate.PreparedStatementDelegator.executeBatch(PreparedStatementDelegator.java:357)
	at io.ebeaninternal.server.persist.BatchedPstmt.executeAndCheckRowCounts(BatchedPstmt.java:130)
	at io.ebeaninternal.server.persist.BatchedPstmt.executeBatch(BatchedPstmt.java:97)
	at io.ebeaninternal.server.persist.BatchedPstmtHolder.flush(BatchedPstmtHolder.java:123)
	at io.ebeaninternal.server.persist.BatchControl.flushPstmtHolder(BatchControl.java:223)
	at io.ebeaninternal.server.persist.BatchControl.flushInternal(BatchControl.java:285)
	at io.ebeaninternal.server.persist.BatchControl.flushBuffer(BatchControl.java:265)
	at io.ebeaninternal.server.persist.BatchControl.flush(BatchControl.java:244)
	at io.ebeaninternal.server.persist.BatchControl.executeOrQueue(BatchControl.java:172)
	at io.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:818)
	... 30 more

'Root'cause

The 'trick' seems to be in the cardinality of the TreeBean#children and the delete statements that are generated. In the test, deleting child1a1 yields the same SQL statement as deleting root: delete from tree_bean where id in (?).

The order in which the deletes are generated is correct, but in io.ebeaninternal.server.persist.BatchedPstmtHolder#getBatchedPstmt the delete of the root is 'smuggled' into the delete of the child1a1 leaf. However, this is then thus executed before the deletes of the other leafs and interior nodes of the tree so references to root still exist in the database.

So

delete from tree_bean where id  in (?)
 -- bind(Array[1]={7})
delete from tree_bean where id  in (?,?,?)
 -- bind(Array[3]={4,5,6})
delete from tree_bean where id  in (?,?)
 -- bind(Array[2]={2,3})
 -- bind(Array[1]={1})

is not what is actually executed! It is - in this case - more something like:

delete from tree_bean where id  in (?)
 -- bind(Array[1]={7})
 -- bind(Array[1]={1})                     <-- Reordered by BatchedPstmtHolder#getBatchedPstmt
delete from tree_bean where id  in (?,?,?)
 -- bind(Array[3]={4,5,6})
delete from tree_bean where id  in (?,?)
 -- bind(Array[2]={2,3})

Workaround

A workaround is to perform such deletes with batching cascades disabled (e.g. through transaction.setBatchOnCascade(false)).