Refential constraint violation running multiples updates in the same transaction

Question

I'm getting a very strange referential integrity constraint violation, the following code illustrate what I'm doing (everything is running in the same transaction using the same entityManager):

...
em.persist(newEntity); //id -> @GeneratedValue(strategy = GenerationType.AUTO)
updateReference(oldEntity, newEntity);
...

public void updateReference(Entity1 oldReference, Entity1 newReference) {
    String jpql = "UPDATE entity2 e"
            + " SET e.entity1 = :newReference"
            + " WHERE e.entity1 = :oldReference";

    Query query = entityManager.createQuery(jpql);
    query.setParameter("newReference", newReference);
    query.setParameter("oldReference", oldReference);
    query.executeUpdate();
}

The update statement throws an "JdbcSQLException: Referential integrity constraint violation". The most weird is the if I run the select displayed below before running the update statement, it works with no erros...

entityManager.createQuery("SELECT e FROM entity1 e WHERE e.id = " + newReference.getId()).getResultList();

My guess is that for some reason the entity is cleared from the session before running the update (even though I checked the objects in the persistence context, and the entity is there...), and when I run the select the entity is "fetched" back to the session. But that is just a guess and I have no ideia why this would happen.

*I'm using JPA 2.1, Hibernate 4.3.11.Final and H2

Stacktrace:

2016-04-15 16:07:35.976; [http-bio-8080-exec-19]; ERROR; o.h.e.jdbc.spi.SqlExceptionHelper; Referential integrity constraint violation: "FK_ANAL_RPPA_ATUALIZADA: PUBLIC.RESULTADO_PRE_PROCESSADO_ANALISE FOREIGN KEY(ANAL_CD_IDENTIFICADOR_ATUALIZADA) REFERENCES PUBLIC.ANALISE_ALARME(ANAL_CD_IDENTIFICADOR) (10032)"; SQL statement:
update RESULTADO_PRE_PROCESSADO_ANALISE set ANAL_CD_IDENTIFICADOR_ATUALIZADA=? where ANAL_CD_IDENTIFICADOR_ATUALIZADA=? [23506-175]

...

    Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:87) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.ResultadoPreProcessadoAnaliseJpaDao.atualizarReferenciaResultados(ResultadoPreProcessadoAnaliseJpaDao.java:373) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.negocio.GerenciadorReferenciaAnalise.atualizarReferenciaAnalise(GerenciadorReferenciaAnalise.java:34) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseJpaDao.atualizarAnalise(AnaliseJpaDao.java:114) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeJpaDao.atualizarAnalise(AnaliseAlarmeJpaDao.java:74) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.atualizarAnalise(AnaliseAlarmeHistoricoJpaDao.java:37) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.atualizarAnalise(AnaliseAlarmeHistoricoJpaDao.java:14) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseJpaDao.salvar(AnaliseJpaDao.java:97) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeJpaDao.salvar(AnaliseAlarmeJpaDao.java:60) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.salvar(AnaliseAlarmeHistoricoJpaDao.java:43) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.salvar(AnaliseAlarmeHistoricoJpaDao.java:14) ~[classes/:na]
        at br.com.logique.lsvraptorarq.controlador.CRUDControlador.salvar(CRUDControlador.java:69) ~[VRaptor-arq-1.5.1-SNAPSHOT.jar:na]
        at br.com.logique.bralarmexpert.controlador.AnaliseCRUDController.salvar(AnaliseCRUDController.java:169) ~[classes/:na]
        at br.com.logique.bralarmexpert.controlador.AnaliseAlarmesAnunciadosPorTempoController$Proxy$_$$_WeldClientProxy.salvar(Unknown Source) ~[classes/:na]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_65]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_65]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_65]
        at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_65]
        at net.vidageek.mirror.provider.java.PureJavaMethodReflectionProvider.invoke(PureJavaMethodReflectionProvider.java:38) [mirror-1.6.1.jar:na]
        ... 211 common frames omitted
    Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:109) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:78) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:445) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:379) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1322) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:118) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:371) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:78) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        ... 229 common frames omitted
    Caused by: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK_ANAL_RPPA_ATUALIZADA: PUBLIC.RESULTADO_PRE_PROCESSADO_ANALISE FOREIGN KEY(ANAL_CD_IDENTIFICADOR_ATUALIZADA) REFERENCES PUBLIC.ANALISE_ALARME(ANAL_CD_IDENTIFICADOR) (10032)"; SQL statement:
    update RESULTADO_PRE_PROCESSADO_ANALISE set ANAL_CD_IDENTIFICADOR_ATUALIZADA=? where ANAL_CD_IDENTIFICADOR_ATUALIZADA=? [23506-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.message.DbException.get(DbException.java:172) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.message.DbException.get(DbException.java:149) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:368) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:310) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.table.Table.fireConstraints(Table.java:894) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.table.Table.fireAfterRow(Table.java:911) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.command.dml.Update.update(Update.java:150) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.command.CommandContainer.update(CommandContainer.java:79) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.command.Command.executeUpdate(Command.java:253) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140) ~[h2-1.3.175.jar:1.3.175]
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410) ~[c3p0-0.9.5.1.jar:0.9.5.1]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        ... 237 common frames omitted

Answer 1

when you run the query. the entityManager is probably flushed implicitly. you see, the em.persist(e) alone doesn't ensure that the respective query is really executed. you need to explicitly call flush if you want that.

---

just turn on query logging. or alternatively: to test if the implicit flushing is the reason why the first approach does not work and the second one does. execute the query with the flush-Mode COMMIT.

here I found a blog post that explains hibernates flushing strategies:

https://vladmihalcea.com/a-beginners-guide-to-jpahibernate-flush-strategies/

Answer 2

As your stacktrace clearly shows, it's the database that triggers the error:

org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK_ANAL_RPPA_ATUALIZADA: [...] 
SQL statement: update RESULTADO_PRE_PROCESSADO_ANALISE set [...] where [...]

Default behavior of the EntityManager is to flush "automatically" [JPA Doc]. This means the EntityManager will execute SQL-Statements when it detects it needs to be done. For insert/update/delete most of the time this will be before the transaction ends with commit (or when there is a query for this type of entity, like your working example).

In the failing case it seems the insert-statement caused by em.persist(newEntity) is not flushed to the database. When the update-statement triggered by updateReference(oldEntity, newEntity) is executed, it fails because of the foreign key constraint.

One solution is calling em.flush() [JPA Doc] right after em.persist(newEntity). This will

Synchronize the persistence context to the underlying database

which in your case means the insert-statement being executed on the database.

em.persist(newEntity); //id -> @GeneratedValue(strategy = GenerationType.AUTO)
em.flush(); // trigger insert in db
updateReference(oldEntity, newEntity);

Another Solution is to "defer" the foreign key constraint ("deferred integrity checking"). Then the database validates the constraint at transaction commit. It seems this is currently not possible with H2 but it's on their roadmap [H2 Roadmap].