回滚时的Spring事务不会将DB还原到以前的状态

Question

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

我使用的是MySQL InnoDB表。以下是服务器日志（没有提交，没有设置AutoCommit=0，没有回滚？？？）在标识符为#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')

相反，应该是这样的：

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

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);
    }

}

package com.example.test;

public interface TestService {
    public void savePerson();
}

package com.example.test;

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

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

}

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);
    }
}

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 + "]";
    }

}

<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

挖了一会儿，我找到了！终于。：）

1）为了使标准的DB事务正常工作（开始/提交/回滚），您应该禁用JTA。使用spring-boot可以在application.properties中完成。

Application.Properties：

spring.jta.enabled = false

...
            <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>
...

<?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>

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