Spring transaction on rollback doesn't restore DB to previous state

Question

I'm trying to insert two records (Person) from within the same transaction (inside savePerson() method from TestService service implementation).

Also I created unique key index on first_name column in PERSON table - so I could get duplicate key error and test transaction rollback when I try to insert two persons with the same first name.

When executing savePerson() method, JTA initiate rollback due duplicate key, but it doesn't initiate rollback in DB too. What I am missing here?

In MySQL general log there is no sql "START TRANSACTION" followed by "ROLLBACK"/"COMMIT". And when starting WildFly server I can see in log sql statement "SET autocommit=1". Is @Transactional annotation supposed to take care of this - handling transaction at DB level too?

I'm using spring-boot, hibernate, and MySQL jdbc.

console.log

15:00:54,060 DEBUG default task-12 jta.JtaTransactionManager:367 - Creating new transaction with name [com.example.test.TestServiceImpl.savePerson]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; '',-java.lang.Exception
15:00:54,066 DEBUG default task-12 support.DefaultListableBeanFactory:251 - Returning cached instance of singleton bean 'transactionManager'
15:00:54,067 DEBUG default task-12 jta.JtaTransactionManager:476 - Participating in existing transaction
15:00:54,083  INFO default task-12 stdout:71 - Hibernate: insert into person (age, first_name, last_name) values (?, ?, ?)
15:00:54,125 DEBUG default task-12 jta.JtaTransactionManager:476 - Participating in existing transaction
15:00:54,126  INFO default task-12 stdout:71 - Hibernate: insert into person (age, first_name, last_name) values (?, ?, ?)
15:00:54,134  WARN default task-12 spi.SqlExceptionHelper:129 - SQL Error: 1062, SQLState: 23000
15:00:54,135 ERROR default task-12 spi.SqlExceptionHelper:131 - Duplicate entry 'John' for key 'idx_first_name_unique'
15:00:54,137 DEBUG default task-12 jta.JtaTransactionManager:858 - Participating transaction failed - marking existing transaction as rollback-only
15:00:54,137 DEBUG default task-12 jta.JtaTransactionManager:1074 - Setting JTA transaction rollback-only
15:00:54,138 DEBUG default task-12 support.DefaultListableBeanFactory:251 - Returning cached instance of singleton bean 'entityManagerFactory'
15:00:54,140 DEBUG default task-12 jta.JtaTransactionManager:851 - Initiating transaction rollback

I'm using MySQL InnoDB tables. Here's the server log (no COMMIT, no SET autocommit=0, no ROLLBACK ???) on the same connection with identifier #22:

           22 Connect   root@172.17.0.1 as anonymous on test
           22 Query /* mysql-connector-java-6.0.6 ( Revision: 3dab84f4d9bede3cdd14d57b99e9e98a02a5b97d ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
           22 Query SELECT @@session.autocommit
           22 Query SET NAMES utf8mb4
           22 Query SET character_set_results = NULL
           22 Query SET collation_connection = utf8_general_ci
           22 Query SET autocommit=1
           22 Query SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
           22 Query SET autocommit=1
170424 15:02:07    22 Query SHOW FULL TABLES FROM `test` LIKE '%'
           22 Query SHOW FULL TABLES FROM `test` LIKE '%'
           22 Query SHOW FULL COLUMNS FROM `person` FROM `test` LIKE '%'
           22 Query SHOW INDEX FROM `person` FROM `test`
170424 15:02:24    22 Query SELECT 1
           22 Query insert into person (age, first_name, last_name) values (40, 'John', 'Smith')
           22 Query insert into person (age, first_name, last_name) values (40, 'John', 'Smith')

Instead should be something like this:

set autocommit = 0
insert into ...
insert into ...
rollback

TestServiceImpl.java

package com.example.test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.test.Person;

@Service
public class TestServiceImpl implements TestService {

    @Autowired
    private TestRepository testRepository;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void savePerson() {
        Person p1 = new Person("John", "Smith", 40);
        Person p2 = new Person("John", "Smith", 40);

        testRepository.save(p1);
        testRepository.save(p2);
    }

}

TestService.java

package com.example.test;

public interface TestService {
    public void savePerson();
}

TestRepository.java:

package com.example.test;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface TestRepository extends JpaRepository<Person, Integer>{

}

Application.java:

package com.example.test;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.support.SpringBootServletInitializer;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableTransactionManagement
public class Application extends SpringBootServletInitializer {

    @Override
    protected SpringApplicationBuilder configure(
            SpringApplicationBuilder application) {
        return application.sources(Application.class);
    }

    public static void main(String[] args) {

        SpringApplication.run(Application.class, args);
    }
}

Person.java

package com.example.test;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Index;

@Entity
@Table(name = "PERSON", indexes = { @Index(name = "idx_first_name_unique", columnList = "first_name", unique = true), })
public class Person {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Integer id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "age")
    private Integer age;

    public Person() {
    }

    public Person(String firstname, String lastname, Integer age) {
        this.firstName = firstname;
        this.lastName = lastname;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Person [firstName=" + firstName + ", lastName=" + lastName
                + ", age=" + age + "]";
    }

}

POM dependencies with versions:

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>1.5.2.RELEASE</version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
            <version>1.5.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.vaadin</groupId>
            <artifactId>vaadin-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <version>1.5.2.RELEASE</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>1.5.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>1.5.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
            <version>1.0.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.2.10.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.2.10.Final</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>
</dependencies>

application.properties:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driverClassName=com.mysql.jdbc.Driver

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect

Answer 1

After digging for awhile, I found it! Finally. :)

1) In order to have standard DB transactions working (BEGIN/COMMIT/ROLLBACK) you should disable JTA. With spring-boot it could be done in application.properties.

application.properties:

spring.jta.enabled = false

2) When using JTA you should enable XA two-phase commit protocol for specific database. Because deployed application is using JTA TransactionManager implementation on Wildfly AS all data sources should be configured there. And in application.properties it's enough just to reference datasource using some JNDI name.

Wildfly standalone-demo.xml:

...
            <datasources>
                 ...
                 <xa-datasource jndi-name="java:jboss/datasources/test" pool-name="TestXADS" enabled="true">
                    <xa-datasource-property name="url">
                        jdbc:mysql://127.0.0.1:3306/test
                    </xa-datasource-property>
                    <driver>mysql</driver>
                    <xa-pool>
                        <min-pool-size>10</min-pool-size>
                        <max-pool-size>20</max-pool-size>
                        <prefill>true</prefill>
                    </xa-pool>
                    <security>
                        <user-name>test</user-name>
                        <password>test</password>
                    </security>
                </xa-datasource>
                <drivers>
                    ...
                    <driver name="mysql" module="com.mysql">
                        <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                    </driver>
                </drivers>
            </datasources>
...

modules/com/mysql/main/module.xml:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="com.mysql">
    <resources>
        <resource-root path="mysql-connector-java-5.1.41-bin.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

application.properties:

spring.datasource.jndi-name=java:jboss/datasources/test