JPA transaction not rolling back persist call. Inserts and commits ignoring transactional rules on MySQL only

Question

I have a @Transactional(rollbackFor=Exception.class) annotation on my method using a Spring shared EntityManager. But when I intentially set the username to a duplicate, the transaction does not roll back the persist call. The original unique username still gets committed to the db. The persist call is acting like it has its own rules for commiting. I can select this value in mysql console immediately after this line executes in debug mode. Which probably means it is automatically committed. SQL logging shows insert statement right when the line execute and no delete statement when the rollback happens. The rollback works completely as expected in a H2 in memory database.

• I had spring.jpa.open-in-view=false
• I tried using em.find and modify a value, then throw the exception. The modification is correctly rolled back. So only the persist call is the problem
• I tried propagation REQUIRE REQUIRE_NEW but no use

• the database user used to connect has proper privilege

  @PersistenceContext
  EntityManager em;
  
  @Transactional(rollbackFor = Exception.class)
  public User save(User user) {
      em.persist(user);
      user.setUsername("duplicate"); // this will throw an exception upon flushing the change
      return user;
  }
  

How do I stop this behavior and make the persist call commit with everything else?

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>

Update: Manually with throw new Exception or call em.flush() with integrity violation does not cause the rollback either. The rollback happens but not deleting the inserted record.

2018-10-23 00:26:36.909 DEBUG 5475 --- [nio-8080-exec-1] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction rollback
2018-10-23 00:26:36.913 DEBUG 5475 --- [nio-8080-exec-1] o.s.orm.jpa.JpaTransactionManager        : Rolling back JPA transaction on EntityManager [SessionImpl(PersistenceContext[entityKeys=[EntityKey[com.crawlers.main.models.User#85]],collectionKeys=[]];ActionQueue[insertions=ExecutableList{size=0} updates=ExecutableList{size=0} deletions=ExecutableList{size=0} orphanRemovals=ExecutableList{size=0} collectionCreations=ExecutableList{size=0} collectionRemovals=ExecutableList{size=0} collectionUpdates=ExecutableList{size=0} collectionQueuedOps=ExecutableList{size=0} unresolvedInsertDependencies=null])]

No solution at Annotation @Transactional. How to rollback? work for me. Tried all solutions and comments :(

Answer 1

Resolved. See comment. My tables were backed by MyISAM and it doesn't support rollback. Each query runs individually and the persist call is committed right away.