SpringBoot事务相关备忘（方法添加@Transactional注解，以及SQL语句(SQLServer数据库)添加SET NOCOUNT ON）

项目改用Spring Data JDBC 并手动配置DataSource之后，@Transactional注解一直不起作用。这两天研究了一下，注解不起作用，主要是没有配置 TransactionManager 的事，配置完 TransactionManager 之后，@Transactional注解就起作用了。

但是配置完又发现，用jdbcTemplate.queryForList()方法执行SQL代码时，能够在遇到RAISERROR()的时候回滚，但用jdbcTemplate.update()/execute()方法时，却不能。
搜索了一阵，大概总结一下就是，queryForList()被设计为取得一个ResultSet，如果遇到RAISERROR()，那么方法会抛出异常，然后Spring看到这个unchecked exception(RuntimeException)，就会回滚数据。
而execute()更通用，而且并不期望获得一个 ResultSet，遇到RAISERROR() 的时候，不需要立即抛出异常，特别是在已经成功执行某些代码之后了。Spring没看到有异常，也就不需要回滚数据。

那么，如何在使用execute()方法，同时遇到RAISERROR()时，让数据也能回滚呢？尝试了一下一篇问答里提到的一种方法，就是在SQL语句开头中添加SET NOCOUNT ON; 就可以了，想想原来是跟之前操作返回多数据集一个做法。😊

测试执行结果备忘（方法是否添加@Transactional注解和SQL语句是否添加SET NOCOUNT ON;的各种情况下）

1. 方法上不添加@Transactional注解，同时SQL语句不添加SET NOCOUNT ON; SET NOCOUNT OFF;
   1. 如果SQL语句有语法错误，用queryForList/execute/update方法时，那么整段SQL(sqlWithErrors)都会运行失败。其他queryForList/execute/update调用的SQL(sqlA)可以正常执行。
   2. 如果SQL语句没有语法错误，语句运行到RAISERROR()，用queryForList方法时，能捕获exception，但是RAISERROR前后的代码都会正常执行(sqlWithErrors & sqlA)（RAISERROR似乎没啥用了）。【有问题，但是凑合，毕竟有异常抛出】
   3. 如果SQL语句没有语法错误，语句运行到RAISERROR()，用execute/update方法时，不能捕获exception，RAISERROR前后的代码都会正常执行(sqlWithErrors & sqlA)，因为不会抛出任何异常，系统正常返回。【非常严重的问题】
2. 方法上不添加@Transactional注解，但SQL语句添加了SET NOCOUNT ON; SET NOCOUNT OFF;
   1. 如果SQL语句有语法错误，用queryForList/execute/update方法时，那么整段SQL(sqlWithErrors)都会运行失败。其他queryForList/execute/update调用的SQL(sqlA)可以正常执行。
   2. 如果SQL语句没有语法错误，语句运行到RAISERROR()，用queryForList/update/execute方法时，能捕获exception，但是RAISERROR前后的代码都会正常执行(sqlWithErrors & sqlA)（RAISERROR似乎没啥用了，但好处是还能捕获exception，比前面的强一点点）。
3. 方法上添加了@Transactional注解，同时SQL语句不添加SET NOCOUNT ON; SET NOCOUNT OFF;
   1. 如果SQL语句有语法错误，用queryForList/execute/update方法时，那么整段SQL(sqlWithErrors)都会运行失败。其他queryForList/execute/update调用的SQL(sqlA)即使成功也会回滚。
   2. 如果SQL语句没有语法错误，语句运行到RAISERROR()，用queryForList方法时，能捕获exception，同时整个方法内的所有数据库操作都会回滚。(sqlWithErrors & sqlA)【勉强可用，但是不能返回多数据集】
   3. 如果SQL语句没有语法错误，语句运行到RAISERROR()，用execute/update方法时，不能捕获exception，RAISERROR前后的代码都会正常执行，因为不会抛出任何异常，系统正常返回。(sqlWithErrors & sqlA)【非常严重的问题】
4. 方法上添加了@Transactional注解，同时SQL语句添加SET NOCOUNT ON; SET NOCOUNT OFF;
   1. 如果SQL语句有语法错误，用queryForList/execute/update方法时，那么整段SQL(sqlWithErrors)都会运行失败。其他queryForList/execute/update调用的SQL(sqlA)即使成功也会回滚。
   2. 如果SQL语句没有语法错误，语句运行到RAISERROR()，用queryForList/execute/update方法时，能捕获exception，同时整个方法内的所有数据库操作都会回滚。(sqlWithErrors & sqlA)【可用，期待的结果】

希望相同功能的两个方法，一个启用事务，另一个不启用事务的情况

如果希望有两个相同的方法，一个启用了事务，另外一个不启用事务，那么可以考虑在启用了事务的方法里面，直接调用不启用事务的方法，到时候按需调用就可以了。

参考资料备忘

• When we execute a stored procedure in JDBC we get back a series of zero or more "results". We can then process those "results" sequentially by calling CallableStatement#getMoreResults(). Each "result" can contain
  • zero or more rows of data that we can retrieve with a ResultSet object,
  • an update count for a DML statement (INSERT, UPDATE, DELETE) that we can retrieve with CallableStatement#getUpdateCount(), or
  • an error that throws an SQLServerException.
    For "Issue 1" the problem is often that the stored procedure does not begin with SET NOCOUNT ON; and executes a DML statement before doing a SELECT to produce a result set. The update count for the DML is returned as the first "result", and the data rows are "stuck behind it" until we call getMoreResults.

"Issue 2" is essentially same problem. The stored procedure produces a "result" (usually a SELECT, or possibly an update count) before the error occurs. The error is returned in a subsequent "result" and does not cause an exception until we "retrieve" it using getMoreResults.

In many cases the problem can be avoided by simply adding SET NOCOUNT ON; as the first executable statement in the stored procedure. However, a change to the stored procedure is not always possible and the fact remains that in order to get everything back from the stored procedure we need to keep calling getMoreResults until, as the Javadoc says:

There are no more results when the following is true:

 // stmt is a Statement object
 ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

That sounds simple enough but as usual, "the devil is in the details", as illustrated by the following example. For a SQL Server stored procedure ...

相关代码

   @Transactional(value = "myTransactionManager")
    public void testMethodWithTran() {
        testMethod();
    }

    public void testMethod() {
        var sqlA = """
                INSERT INTO dbo.TestTable (UID, Content, CreateAT, Sec)
                SELECT NEWID(), N'DataA', GETDATE(), N'DataA query section'
                select 1;
                """;

        jdbcTemplate.queryForList(sqlA);

        var sqlWithError = """
                SET NOCOUNT ON;
                
                INSERT INTO dbo.TestTable (UID, Content, CreateAT, Sec)
                SELECT NEWID(), N'DataB', GETDATE(), N'DataB SECTION'
                
                -- Bad SQL Grammar
                --INSERT INTO dbo.TestTable (UID, Content, CreateAT, Sec)
                --SELECT NEWID(), N'DataB2/*'*/, GETDATE(), N'DataB SQL ERROR SECTION'
                
                RAISERROR(N'Raised Error After DataB and before DataC', 16, 1);
                
                INSERT INTO dbo.TestTable (UID, Content, CreateAT, Sec)
                SELECT NEWID(), N'DataC', GETDATE(), N'DataC SECTION'
                
                SET NOCOUNT OFF;
                """;

        jdbcTemplate.execute(sqlWithError);

    }

参考资料

1. No Exception thrown for obvious error
   https://github.com/Microsoft/mssql-jdbc/issues/826
2. How to get everything back from a stored procedure using JDBC
   https://stackoverflow.com/questions/42169951/how-to-get-everything-back-from-a-stored-procedure-using-jdbc/42169952#42169952