博客

先说下结论，

事务没有rollback或者rollback失败的情况下，如果是相同的connection（数据库连接池会复用连接），可能会产生数据不一致的场景。

示例场景：

begin;

insert into test (id, name) values (2,'ning');


insert into test (id, name) values (1,'ruler');

//commit or rollback，如果上面的第二句sql执行异常，比如dupKey，业务层的事物将会回滚，这里未回滚或者回滚失败，再下一个事务begin或者commint的时候将会把第一句sql也给commit，这种情况就会产生数据不一致的场景。

//begin

insert into test (id, name) values (3,'tian');

commit;

示例代码：

    private static final String URL = "jdbc:mysql://localhost:3306/xxx";
    private static final String USER = "xxx";
    private static final String PASSWORD = "xxx";

    public static void main(String[] args) throws Exception {
        test1();
        //test2();
    }

    private static void test1() throws Exception {
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

        connection.setAutoCommit(false);
        Statement statement1 = null;
        Statement statement2 = null;
        try {
            statement1 = connection.createStatement();

            statement1.execute("insert into test (id, name) values (2,'ning');");

            statement2 = connection.createStatement();
            //test表id是主键，已经录入了1这一条记录，再次insert，will primary key dup exception
            statement2.execute("insert into test (id, name) values (1,'ruler');");

            connection.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            //判断没有执行rollback的场景下，最终INSERT的是几个
            //connection.rollback();
        } finally {
            assert statement1 != null;
            statement1.close();
            assert statement2 != null;
            statement2.close();
        }

        Statement statement3 = connection.createStatement();
        //begin执行完成后，第一句insert将会执行完成
        statement3.execute("begin ;");
        statement3.execute("insert into test (id, name) values (3,'tian');");
        statement3.execute("commit ;");

        statement3.close();

        connection.close();
    }
    
	private static void test2() throws Exception {
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

        connection.setAutoCommit(false);
        Statement statement1 = null;
        Statement statement2 = null;
        try {
            statement1 = connection.createStatement();

            statement1.execute("insert into test (id, name) values (4,'faker');");

            statement2 = connection.createStatement();
            //test表id是主键，已经录入了1这一条记录，再次insert，will primary key dup exception
            statement2.execute("insert into test (id, name) values (1,'ruler');");

            connection.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            //connection.rollback();
        } finally {
            assert statement1 != null;
            statement1.close();
            assert statement2 != null;
            statement2.close();
        }

        Statement statement3 = connection.createStatement();
        statement3.execute("insert into test (id, name) values (5,'shy');");
 		//commit执行完成后，第一句insert将会执行完成
        connection.commit();

        statement3.close();

        connection.close();
    }