Delete cascade N+1 problem

[jnehlmeier]

Description

In a production app using JPA an object graph should be deleted using a cascaded delete on the root entity. This has been implemented using Ebean since JPA produces a gazillion of single delete queries. However the object graph causes Ebean to produce way more queries than expected and needed (but still better than JPA).

So for now the expected behavior below needs to be implemented manually in that app to avoid many queries.

Expected behavior

select t0.id from city t0 where country_id=?;
select t0.id, t0.mayor_id, t0.vice_mayor_id from city t0 where t0.id = any(?);
select t0.id from calendar t0 where person_id = any(?);

delete from event where (calendar_id) = any(?);
delete from calendar where id  = any(?);
delete from city where id = any(?);
delete from person where id = any(?);
delete from country where id=? and version=?;

Actual behavior

txn[1002] select t0.id from city t0 where country_id=? ; --bind(1)
txn[1002] select t0.id, t0.mayor_id, t0.vice_mayor_id from city t0 where t0.id = any(?); --bind(Array[3]={1,2,3})
txn[1002] delete from city where id=?; -- bind(1)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(1)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={1,2}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={1,2}) rows(2)
txn[1002] delete from person where id=?; -- bind(1)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(2)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={3,4}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={3,4}) rows(2)
txn[1002] delete from person where id=?; -- bind(2)
txn[1002] delete from city where id=?; -- bind(2)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(3)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={5,6}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={5,6}) rows(2)
txn[1002] delete from person where id=?; -- bind(3)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(4)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={7,8}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={7,8}) rows(2)
txn[1002] delete from person where id=?; -- bind(4)
txn[1002] delete from city where id=?; -- bind(3)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(5)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={9,10}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={9,10}) rows(2)
txn[1002] delete from person where id=?; -- bind(5)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(6)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={11,12}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={11,12}) rows(2)
txn[1002] delete from person where id=?; -- bind(6)
txn[1002] delete from country where id=? and version=?; -- bind(1,1)

Steps to reproduce

Consider the following entities:

@MappedSuperclass
public class BaseModel extends Model {

  @Version
  private long version = 0;
}

@Entity
public class Country extends BaseModel {

  @Id
  private Long id;

  @OneToMany(cascade = CascadeType.ALL)
  private List<City> cities = new ArrayList<>();

  public void addCity(City city) {
    cities.add(city);
  }
}

@Entity
public class City extends BaseModel {

  @Id
  private Long id;

  @OneToOne(cascade = CascadeType.ALL, optional = false)
  private Person mayor;

  @OneToOne(cascade = CascadeType.ALL, optional = false)
  private Person viceMayor;

  public City(Person mayor, Person viceMayor) {
    this.mayor = mayor;
    this.viceMayor = viceMayor;
  }
}

@Entity
public class Person extends BaseModel {

  @Id
  private Long id;

  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  private List<Calendar> calendars = new ArrayList<>();

  public void addCalendar(Calendar calendar) {
    calendars.add(calendar);
  }
}

@Entity
public class Calendar extends BaseModel {

  @Id
  private Long id;

  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  private List<Event> events = new ArrayList<>();

  public void addEvent(Event event) {
    events.add(event);
  }
}

@Entity
public class Event extends BaseModel {

  @Id
  private Long id;
}

And the following simple code to produce the above log

public static void main(String[] args) throws InterruptedException {
  // init Ebean 
  // setupDb(); 

  var country = new Country();
  for (int a = 0; a < 3; a++) {
    var mayor = createPerson();
    var viceMayor = createPerson();
    country.addCity(new City(mayor, viceMayor));
  }
  country.save();

  Thread.sleep(2000);

  country.deletePermanent();
}

private static Person createPerson() {
  var person = new Person();
  for (int a = 0; a < 2; a++) {
    var calendar = new Calendar();
    for (int b = 0; b < 10; b++) {
      calendar.addEvent(new Event());
    }
    person.addCalendar(calendar);
  }
  return person;
}

[jnehlmeier]

Just for information: I just noticed that if you insert the above entity graph using batch insert, the insertion logic behaves similar to the current delete cascade logic.

txn[1001] insert into country (version) values (?); -- bind(1)
txn[1001] insert into person (version) values (?); -- bind(1)
txn[1001] insert into calendar (person_id, version) values (?,?)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001] insert into event (calendar_id, version) values (?,?)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001] insert into person (version) values (?); -- bind(1)
txn[1001] insert into calendar (person_id, version) values (?,?)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001] insert into city (country_id, version, mayor_id, vice_mayor_id) values (?,?,?,?)
txn[1001]  -- bind(1,1,1,2)
txn[1001] insert into event (calendar_id, version) values (?,?)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001] insert into person (version) values (?); -- bind(1)
txn[1001] insert into calendar (person_id, version) values (?,?)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001] insert into event (calendar_id, version) values (?,?)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001] insert into person (version) values (?); -- bind(1)
txn[1001] insert into calendar (person_id, version) values (?,?)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001] insert into city (country_id, version, mayor_id, vice_mayor_id) values (?,?,?,?)
txn[1001]  -- bind(1,1,3,4)
txn[1001] insert into event (calendar_id, version) values (?,?)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(7,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001]  -- bind(8,1)
txn[1001] insert into person (version) values (?); -- bind(1)
txn[1001] insert into calendar (person_id, version) values (?,?)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001] insert into event (calendar_id, version) values (?,?)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(9,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001]  -- bind(10,1)
txn[1001] insert into person (version) values (?); -- bind(1)
txn[1001] insert into calendar (person_id, version) values (?,?)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001] insert into city (country_id, version, mayor_id, vice_mayor_id) values (?,?,?,?)
txn[1001]  -- bind(1,1,5,6)
txn[1001] insert into event (calendar_id, version) values (?,?)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(11,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)
txn[1001]  -- bind(12,1)

[rbygrave]

Note that for inserting the graph ... that has changed with #1835

The underlying issue there was that for "depth < 0" Ebean didn't "escalate to use jdbc batch". For this graph going from City to Person put the "relative depth" back to 0 so ... Person wasn't batched.

Anyway, with #1835 the insert now looks more like what we would expect:

txn[1001] insert into pcf_country (version) values (?); -- bind(1)
txn[1001] insert into pcf_person (name, version) values (?,?)
txn[1001]  -- bind(person_kKeUu-a2hCPK,1)
txn[1001]  -- bind(person_BeJIakGbEjMM,1)
txn[1001]  -- bind(person_X6GL9uRH57XP,1)
txn[1001]  -- bind(person_DyXrKCJ1dApZ,1)
txn[1001]  -- bind(person_gf0_UiBsbrP-,1)
txn[1001]  -- bind(person_wJEVzmELBSCp,1)
txn[1001] insert into pcf_calendar (pcf_person_id, version) values (?,?)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(1,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(2,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(3,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(4,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(5,1)
txn[1001]  -- bind(6,1)
txn[1001]  -- bind(6,1)
txn[1001] insert into pcf_city (pcf_country_id, name, version, mayor_id, vice_mayor_id) values (?,?,?,?,?)
txn[1001]  -- bind(1,city_scJDPDJ0tqBF,1,1,2)
txn[1001]  -- bind(1,city_Xjvbx6wlTWxG,1,3,4)
txn[1001]  -- bind(1,city_qYNttn5KlIp8,1,5,6)
txn[1001] insert into pcf_event (pcf_calendar_id, name, version) values (?,?,?)
txn[1001]  -- bind(1,evt_BbCHlSVLEenT,1)
txn[1001]  -- bind(1,evt_2Hj1zarg6lqD,1)
txn[1001]  -- bind(1,evt_CiJRubbAtGBk,1)
txn[1001]  -- bind(1,evt_jTZVqidDy8VA,1)
txn[1001]  -- bind(1,evt_AcQGz-Qq9vjF,1)
txn[1001]  -- bind(1,evt_gwrvaWxJnOmS,1)
txn[1001]  -- bind(1,evt_gm4ZGbaPals-,1)
txn[1001]  -- bind(1,evt_Qq90ZbIqIohK,1)
txn[1001]  -- bind(1,evt_zQBL8ktrjXw-,1)
txn[1001]  -- bind(1,evt_yudU3AmQOlb8,1)
txn[1001]  -- bind(2,evt_ZggKTsevWsqN,1)
txn[1001]  -- bind(2,evt_WAthl2YdFVJk,1)
txn[1001]  -- bind(2,evt_KE-JAfGUhzQ6,1)
txn[1001]  -- bind(2,evt_oCKbHV4dq-m4,1)
txn[1001]  -- bind(2,evt_XJ-LRrpmBaxU,1)
txn[1001]  -- bind(2,evt__mCmbejQwvFm,1)
txn[1001]  -- bind(2,evt_yziK8PYIJ1Pn,1)
txn[1001]  -- bind(2,evt_lzS5E9a_kx0l,1)
txn[1001]  -- bind(2,evt_V1QZU5WU7QGy,1)
txn[1001]  -- bind(2,evt_ETPJEgx--fx4,1)
txn[1001] insert into pcf_event (pcf_calendar_id, name, version) values (?,?,?)
txn[1001]  -- bind(3,evt_uqHlOMarGbVx,1)
txn[1001]  -- bind(3,evt__Agwprp8SjDt,1)
txn[1001]  -- bind(3,evt_4PIpruK2b7Sq,1)
txn[1001]  -- bind(3,evt_UASb6XOptTfZ,1)
txn[1001]  -- bind(3,evt_0IeS766Tx7Gt,1)
txn[1001]  -- bind(3,evt_y9O7laQnfu1P,1)
txn[1001]  -- bind(3,evt_4MbCt_MDlJNT,1)
txn[1001]  -- bind(3,evt_Zksr299NL9wc,1)
txn[1001]  -- bind(3,evt_Urw8l0C66H2m,1)
txn[1001]  -- bind(3,evt_0i8yyTY5i2Wx,1)
txn[1001]  -- bind(4,evt_MRY6Nw8ZDo9T,1)
txn[1001]  -- bind(4,evt_hCU2NHivLzhw,1)
txn[1001]  -- bind(4,evt_FuWayiLXOkvo,1)
txn[1001]  -- bind(4,evt_Y7eBjstBRUU-,1)
txn[1001]  -- bind(4,evt_ANlczbaO-kQ4,1)
txn[1001]  -- bind(4,evt_KBA35SWSIFtN,1)
txn[1001]  -- bind(4,evt_grdUP6ikRLbl,1)
txn[1001]  -- bind(4,evt_CzhY2u2V0HRX,1)
txn[1001]  -- bind(4,evt_qwNgmOT1Z0XP,1)
txn[1001]  -- bind(4,evt_AgavkimYTCX4,1)
txn[1001] insert into pcf_event (pcf_calendar_id, name, version) values (?,?,?)
txn[1001]  -- bind(5,evt_JDD8CxoF4xkn,1)
txn[1001]  -- bind(5,evt_XqmwcMLVdjj8,1)
txn[1001]  -- bind(5,evt_ghlIpj_jE3aY,1)
txn[1001]  -- bind(5,evt_ZAy4iZdrJKNs,1)
txn[1001]  -- bind(5,evt_ZFzHpGNY6c0h,1)
txn[1001]  -- bind(5,evt_bYP5GTod1IOA,1)
txn[1001]  -- bind(5,evt_pxJCrkENggG8,1)
txn[1001]  -- bind(5,evt_avrTFVsIaBJg,1)
txn[1001]  -- bind(5,evt_meHZJ2pE_BOe,1)
txn[1001]  -- bind(5,evt_zna8iOtRw0yk,1)
txn[1001]  -- bind(6,evt_TrM8qMc91Ocu,1)
txn[1001]  -- bind(6,evt_zFb8eDA1GdpI,1)
txn[1001]  -- bind(6,evt_oQqTSPsFID_e,1)
txn[1001]  -- bind(6,evt_FDihJ7530XjC,1)
txn[1001]  -- bind(6,evt_QACGA97wnF3A,1)
txn[1001]  -- bind(6,evt_IgqVoqmqCZX9,1)
txn[1001]  -- bind(6,evt_rx7-XIVXM937,1)
txn[1001]  -- bind(6,evt_afg9-niH18u8,1)
txn[1001]  -- bind(6,evt_lhJrGyxCCpex,1)
txn[1001]  -- bind(6,evt_-XNh3JNvaR6r,1)
txn[1001] insert into pcf_event (pcf_calendar_id, name, version) values (?,?,?)
txn[1001]  -- bind(7,evt_BA6HinPZ0K1w,1)
txn[1001]  -- bind(7,evt_dy3Cw1a7aZY_,1)
txn[1001]  -- bind(7,evt_6zE3KfAZo-pY,1)
txn[1001]  -- bind(7,evt_olclVIV0GAdZ,1)
txn[1001]  -- bind(7,evt_Wh2-_pn3JhDL,1)
txn[1001]  -- bind(7,evt_9In_HsEv0iw-,1)
txn[1001]  -- bind(7,evt_Qm2hwKQFTGdE,1)
txn[1001]  -- bind(7,evt_F0FA_tQzEUb0,1)
txn[1001]  -- bind(7,evt_zTYWuKghiyOQ,1)
txn[1001]  -- bind(7,evt_ytC9v7KIYzEN,1)
txn[1001]  -- bind(8,evt_N2TJaQYutDg6,1)
txn[1001]  -- bind(8,evt_r9dpEPOhxYTG,1)
txn[1001]  -- bind(8,evt_2K03f5PDKGPl,1)
txn[1001]  -- bind(8,evt_4vPhuGvEB1dk,1)
txn[1001]  -- bind(8,evt_kyC2tqPiKDLY,1)
txn[1001]  -- bind(8,evt_ggqHEAJxOajV,1)
txn[1001]  -- bind(8,evt_5x3QVbqmH1Lg,1)
txn[1001]  -- bind(8,evt_I0cKv5UV0gcV,1)
txn[1001]  -- bind(8,evt_ZSu4_v4_3LCm,1)
txn[1001]  -- bind(8,evt_oqAJQmdh2d9H,1)
txn[1001] insert into pcf_event (pcf_calendar_id, name, version) values (?,?,?)
txn[1001]  -- bind(9,evt_R7HQoMG9EKmd,1)
txn[1001]  -- bind(9,evt_SCevgWBbybo7,1)
txn[1001]  -- bind(9,evt_WC4l7PH_5ram,1)
txn[1001]  -- bind(9,evt_isnTx-BdOA23,1)
txn[1001]  -- bind(9,evt_aI6O57GHWK4R,1)
txn[1001]  -- bind(9,evt_DClX6kpbYp3s,1)
txn[1001]  -- bind(9,evt_HDnQ1WqWDABw,1)
txn[1001]  -- bind(9,evt_aIYVcXVBzFtK,1)
txn[1001]  -- bind(9,evt_SvQ4bZ4TZzdA,1)
txn[1001]  -- bind(9,evt_gciY6yH1LTKM,1)
txn[1001]  -- bind(10,evt_hL8pu-C6c72l,1)
txn[1001]  -- bind(10,evt_PHI6M6qVjNSo,1)
txn[1001]  -- bind(10,evt_9MpexxjVbnA_,1)
txn[1001]  -- bind(10,evt_qfcjUrvs3EEv,1)
txn[1001]  -- bind(10,evt_UXuc3FqQCk9k,1)
txn[1001]  -- bind(10,evt_NTdQZvoDxojA,1)
txn[1001]  -- bind(10,evt_XA8kgartqmDK,1)
txn[1001]  -- bind(10,evt_0-9FGaP_Gjv_,1)
txn[1001]  -- bind(10,evt_2sJ--JDqpoj6,1)
txn[1001]  -- bind(10,evt_cQ4ghv-5eTGF,1)
txn[1001] insert into pcf_event (pcf_calendar_id, name, version) values (?,?,?)
txn[1001]  -- bind(11,evt_qPrLh26VViCd,1)
txn[1001]  -- bind(11,evt_6SQvz_ff7C-n,1)
txn[1001]  -- bind(11,evt_qnR2MgJS5ZMQ,1)
txn[1001]  -- bind(11,evt_nUPElccflhyJ,1)
txn[1001]  -- bind(11,evt_CKskuo6ev0x4,1)
txn[1001]  -- bind(11,evt_9EWBoGZ2A29z,1)
txn[1001]  -- bind(11,evt_kj01NWRmu6jZ,1)
txn[1001]  -- bind(11,evt_GwD-ak9plHsH,1)
txn[1001]  -- bind(11,evt_xgX0fEpIeMWT,1)
txn[1001]  -- bind(11,evt_gGUwGq0rqDPt,1)
txn[1001]  -- bind(12,evt_K6OyRwHYK_hs,1)
txn[1001]  -- bind(12,evt_-nRsMkYE_JFX,1)
txn[1001]  -- bind(12,evt_Flo-Ga9AOHLE,1)
txn[1001]  -- bind(12,evt_bVoi5ycX8mRn,1)
txn[1001]  -- bind(12,evt_whEEeD3Zoafn,1)
txn[1001]  -- bind(12,evt_Ni_KFs31zOmY,1)
txn[1001]  -- bind(12,evt_921WmG48a27q,1)
txn[1001]  -- bind(12,evt_6DsiXZB-ADHR,1)
txn[1001]  -- bind(12,evt_sVEho-D7jDVz,1)
txn[1001]  -- bind(12,evt_ERRaD2oQ7KC4,1)
txn[1001] Commit

Refer to TestPersistCascade.insert()

[jnehlmeier]

That's great! Does this also fix the delete behavior?

[rbygrave]

Does this also fix the delete behavior?

Not it doesn't. Leaving this ticket open for fixing the delete behavior.