SpringBoot事務相關備忘（方法新增@Transactional註解，以及SQL語句(SQLServer資料庫)新增SET NOCOUNT ON）

asashadow發表於2024-10-18
Spring BootSQLServer資料庫

專案改用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

相關文章