MySQL 5.7 Spring JPA transaction management not worknig

Question

MySQL version: 5.7.35

MySQL JDBC driver in use: mysql-connector-java-8.0.25.jar:8.0.25

Spring Boot: 2.5.0

I have two database tables:

create table first
(
    id bigint auto_increment
        primary key,
    item_id int not null,
    element varchar(25) not null
    version int not null,
    unique (item_id, element)
) ENGINE=InnoDB;

and

create table second
(
    id bigint auto_increment
        primary key,
    item_id int not null,
    element varchar(25) not null
    version int not null,
    unique(item_id, element)
) ENGINE=InnoDB;

Let's not worry about why I have 2 exactly similarly looking tables. I am simplifying the problem statement here. In reality, the business logic requires 2 separate tables with multiple columns.

In my Spring Boot 2 app, I have to configure 2 datasources: Vertica (for reading data) and MySQL 5.7.35 for persisting.

Relevant MySQLDataSourceConfig.java (I have similar class VerticaDataSourceConfig.java as well that I am not showing here)

@Configuration
@ConfigurationProperties("mysql.datasource")
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "mysqlEntityManagerFactory",
        transactionManagerRef = "mysqlTransactionManager",
        basePackages = { "com.mysql.package.repository" }
)
public class MySQLDataSourceConfig extends HikariConfig {
    public final static String PERSISTENCE_UNIT_NAME = "mysql";
    public final static String PACKAGES_TO_SCAN = "com.mysql.package.entity";

    @Autowired private Environment env;

    @Bean
    public HikariDataSource mysqlDataSource() {
        return new HikariDataSource(this);
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(
            final HikariDataSource mysqlDataSource) {

        return new LocalContainerEntityManagerFactoryBean() {{
            setDataSource(mysqlDataSource);
            setPersistenceProviderClass(HibernatePersistenceProvider.class);
            setPersistenceUnitName(PERSISTENCE_UNIT_NAME);
            setPackagesToScan(PACKAGES_TO_SCAN);

            HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
            adapter.setDatabasePlatform("org.hibernate.dialect.MySQL57Dialect");
            adapter.setDatabase(Database.MYSQL);
            adapter.setShowSql(true);
            setJpaVendorAdapter(adapter);

            Properties jpaProperties = new Properties();
            jpaProperties.put("hibernate.ddl-auto", env.getProperty("mysql.jpa.hibernate.ddl-auto"));
            jpaProperties.put("hibernate.show-sql", env.getProperty("mysql.jpa.hibernate.show-sql"));
            jpaProperties.put("hibernate.format_sql", env.getProperty("mysql.jpa.hibernate.format_sql"));
            //jpaProperties.put("hibernate.dialect", env.getProperty("mysql.jpa.properties.hibernate.dialect"));

            setJpaProperties(jpaProperties);

            afterPropertiesSet();
        }};
    }

    @Bean
    public PlatformTransactionManager mysqlTransactionManager(EntityManagerFactory mysqlEntityManagerFactory) {
        return new JpaTransactionManager(mysqlEntityManagerFactory);
    }
}

application.yaml for MySQL specific properties (similar properties for Vertica are not shown here:

spring:
  autoconfigure:
    exclude: >
      org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration,
      org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration,
      org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration

mysql:
  datasource:
    jdbc-url: jdbc:mysql://${MYSQL_HOST:localhost}:3306/mydb
    username: root
    password: mypassword
    driver-class-name: com.mysql.jdbc.Driver
    hikari:
      connectionTimeout: 30000
      idleTimeout: 30000
      maxLifetime: 2000000
      maximumPoolSize: 20
      minimumIdle: 5
      poolName: mysql-db-pool
      #username: ${DB_USER}
      #password: ${DB_PASSWORD}
  jpa:
    hibernate:
      ddl-auto: none
      format_sql: true
      show-sql: true
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
      naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
    properties:
      hibernate:
        #dialect: org.hibernate.dialect.MySQL57Dialect
        dialect: org.hibernate.dialect.MySQLInnoDBDialect
          #storage_engine: innodb
        #default_schema: hotspot

Here are the repository classes:

MySQLFirstRepository.java

@Repository
public interface MySQLFirstRepository extends CrudRepository<First, BigInteger> {
}

MySQLSecondRepository.java

@Repository
public interface MySQLSecondRepository extends CrudRepository<Second, BigInteger> {
}

You can also assume that I have supplied a JVM property when running the application:

-Dhibernate.dialect.storage_engine=innodb

Here are the entity classes:

First.java

@Entity
@Table(name = "first")
@Getter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class First {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigInteger id;

    @Column(name = "item_id")
    private BigInteger itemId;

    @Column(name = "element")
    private String element;

    @Column(name = "version")
    @Version
    private int version;
}

Second.java

@Entity
@Table(name = "second")
@Getter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Second {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigInteger id;

    @Column(name = "item_id")
    private BigInteger itemId;

    @Column(name = "element")
    private String element;

    @Column(name = "version")
    @Version
    private int version;
}

PROBLEM:

In my service class, I have something like this:

@Transactional(
            transactionManager = "mysqlTransactionManager",
            propagation = Propagation.REQUIRED,
            rollbackFor = {Exception.class},
            isolation = Isolation.DEFAULT)
    public void persist(List<First> firstList, List<Second> secondList) {
        firstRepo.saveAll(firstList);
        secondRepo.saveAll(secondList);
    }

The secondRepo.saveAll(secondList) method throws below exception:

org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement

In spite of this, the first table gets populated and is not rolled back. I read that it may be due to MyISAM vs InnoDB based tables in MySQL, tried a couple of things (as you can see in the commented lines in the code fragments above), tried setting JVM system property mentioned earlier, tried putting ENGINE=InnoDB in my CREATE TABLE statements but nothing seems to have worked.

Please can this be helped with? I would appreciate if the solution is tried first before posting because, as mentioned, I have tried almost everything available on technical forums.

NOTE THAT standard Spring JPA properties cannot be configured since I have to define my own DataSourceConfig (because of multiple data sources), so I have to work with Hibernate properties, as you can see in MySQLDataSourceConfig.java above.

Answer 1

I was eventually able to solve this for myself. Posting details here so someone else can get benefitted.

Some facts first:

1. MySQL 5.7 onwards, the default storage engine is InnoDB (https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html) In MySQL 5.7, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE clause creates an InnoDB table.

2. There is no need to use -Dhibernate.dialect.storage_engine=innodb command line system property.

3. MySQL*InnoDBDialect classes are deprecated

4. Dialect to be used is: org.hibernate.dialect.MySQL57Dialect

Solution:

It appears that Spring @Transactional is not properly intercepted by the default Spring Proxy AOP. The solution is to either use Aspect J (Does Spring @Transactional attribute work on a private method?)

OR

Refactor the code to ensure that the caller of @Transactional method resides in a different class.

Here is an example (just an example to show what I mean, code should be properly refactored and appropriate coding and design principles should be used)

Caller code:

@Service
public class MyService {
    @Autowired private Persister persister;

    public void doSomething() {
        // do something to get a list of entity First and Second
        persister.persist(firstEntityList, secondEntityList);
    }
}

Callee:

public class Persister {
    @Autowired private MySQLFirstRepository firstRepo;
    @Autowired private MySQLSecondRepository secondRepo;

    @Transactional("mysqlTransactionManager") // use other attributes to suit your needs; see some options above in the question
    public void persist(List<First> firstEntityList, List<Second> secondEntityList) {
        firstRepo.saveAll(firstEntityList);
        secondRepo.saveAll(secondEntityList);
    }
}