CODEWITHSUNDEEP
javaspringspring-data-jpaspring-jdbc
Felix
Felix

Reputation: 2396

Spring @Transactional across JPA and JDBC

Setup:

Description:

I have a method that uses JdbcOperations (JdbcTemplate) to insert a value into a table. This table has a foreign key constraint to another table which is managed by a JPA CrudRepository.

The method in question looks like this:

    private static final String SAVE_SQL = "INSERT INTO table (entity_id, value) VALUES (?, ?) ON CONFLICT (entity_id, value) DO UPDATE SET value = EXCLUDED.value";

    @Transactional
    public void save(long id, String value) {
        this.otherService.getOrCreateEntity(id);
        
        this.jdbcOperations.update(SAVE_SQL, (ps) -> {
            ps.setLong(id);// this is a foreign key to the JPA managed table
            ps.setString(value);
        });
    }

The method of otherService looks like this:

    @Transactional
    public Entity getOrCreateEntity(long id) {
        final Optional<Entity> optionalEntity = this.crudRepository.findById(id);
        Entity entity;
        
        if (optionalEntity.isEmpty()) {
            entity = new Entity();
            entity.setId(id);
            entity = this.crudRepository.save(entity);
        } else {
            entity = optionalEntity.get();
        }
        
        return entity;
    }

I added the following logging settings to see which transactions are being used:

logging:
  level:
    org.springframework.orm.jpa: DEBUG
    org.springframework.transaction: DEBUG

When the save(long, String) method is called, I can see that a new transaction is created. The otherService.getOrCreateEntity(long)-Method inserts a new Entity and returns it to the method in question.

When the method in question tries to insert its own value using JdbcOperations, I get a DataIntegrityViolationException : violates foreign key constraint.

This is the transaction log:

2021-09-09 21:42:29.704 DEBUG 16077 --- [nio-9000-exec-7] o.s.orm.jpa.JpaTransactionManager        : Creating new transaction with name [TheJdbcOperationsUsingService.save]: PROPAGATION_REQUIRED,ISOLATION_READ_UNCOMMITTED
2021-09-09 21:42:29.704 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Opened new EntityManager [SessionImpl(2113410426<open>)] for JPA transaction
2021-09-09 21:42:29.704 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@1e809ae2]

-- Call to otherService.getOrCreateEntity(long) start
2021-09-09 21:43:17.710 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Found thread-bound EntityManager [SessionImpl(2113410426<open>)] for JPA transaction
2021-09-09 21:43:17.711 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Participating in existing transaction
2021-09-09 21:43:17.712 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Found thread-bound EntityManager [SessionImpl(2113410426<open>)] for JPA transaction
2021-09-09 21:43:17.713 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Participating in existing transaction
2021-09-09 21:43:17.719 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Found thread-bound EntityManager [SessionImpl(2113410426<open>)] for JPA transaction
2021-09-09 21:43:17.720 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Participating in existing transaction
-- Call to otherService.getOrCreateEntity(long) finished

2021-09-09 21:43:51.374 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction rollback
2021-09-09 21:43:51.374 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Rolling back JPA transaction on EntityManager [SessionImpl(2113410426<open>)]
2021-09-09 21:43:51.376 DEBUG 16327 --- [nio-9000-exec-6] o.s.orm.jpa.JpaTransactionManager        : Closing JPA EntityManager [SessionImpl(2113410426<open>)] after transaction

I tried with different Propagation and Isolation Settings on both sides, but with no luck.

Upvotes: 1

Views: 1590

Answers (1)

chrylis -cautiouslyoptimistic-
chrylis -cautiouslyoptimistic-

Reputation: 77234

The problem you're running into is that the EntityManager is keeping a cache of unsaved changes in memory until the transaction closes, and so when you try to insert your data via JDBC directly the database still hasn't seen it.

To fix this, you can use the more specific interface JpaRespository, which has a saveAndFlush method that instructs the EntityManager to immediately write out the insert and not to batch the writes later.

Upvotes: 3

Related Questions