[SQL Server]Talking about XACT_ABORT

SQL Server提供Xact_abort來處理run-time發生錯誤的TSQL,

可幫助將現有交易或相依交易自動rolls back,而Xact_abort也是我開發交易必要習慣,

建議有使用SQL Server的朋友可啟用,Xact_abort會幫我們築起最後一道安全交易防線。

昨天我遇見一個情況,有時我發現一些sql session會holding locks,

導致blocking其他session一段時間,但holding locks的session似乎是idle,

會發生這情況有很多可能原因造成,但這裡我是模擬ADO.net的connection交由GC處理且timeout情況,

以及如何使用Xact_abort來解決。

 

我簡單用一個console application來執行一隻SP模擬timeout,但我在ap這端不處理交易roll back(如果發生錯誤時)

CREATE proc [dbo].[usp_addmynumbers]
(
@c1 int,@c2 varchar(10)
)
as
set nocount on;
begin tran
  insert dbo.mynumbers values(@c1,@c2)
  waitfor delay '00:00:40'
commit
GO

C#

const string sql = "dbo.usp_addmynumbers";
            SqlTransaction ts = null;
            try
            {
                if (sqlConnection.State == ConnectionState.Closed)
                    sqlConnection.Open();
                ts = sqlConnection.BeginTransaction("SampleTransaction");
                using (SqlCommand command = new SqlCommand(sql, sqlConnection))
                {
                    command.Transaction = ts;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(
                        new SqlParameter
                        {
                            ParameterName = "c1",
                            SqlDbType = SqlDbType.Int,
                            Value = c1
                        });
                    command.Parameters.Add(
                        new SqlParameter
                        {
                            ParameterName = "c2",
                            SqlDbType = SqlDbType.VarChar,
                            Value = c2
                        });
                    var rowsAffected = command.ExecuteNonQuery();
                    ts.Commit();
                    Console.WriteLine($"Both records are written to database: {rowsAffected}");
                    return 1;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  Message: {0}", ex.Message);
               
                return -1;
            }

 

執行中,可以看到相關資源都被Lock

交易也顯示活動中

等待類型和經過時間我們也可以查詢的到

發生timeout錯誤後,ap這裡也沒有實作rollback,

而你在SQL SERVER也查不到任何等待類型(查不到這句話讓我很熟悉..XD)。

假設這時剛好有另一條session要查詢被lock的資源,

這時就會發生blocking情況,因為之前交易並未被關閉。

到現在,我還沒有設定Xact_abort ON,但如果AP這裡的connection有確保close,

其實也能避免上訴情況(我得再次強調,我這是console application),現在我使用using包起我的connection

using (sqlConnection)
			 {
			   ...
			 }

再次依照上面動作重複執行一次

Timeout 錯誤發生後,因為AP這裡一定會釋放connection,

就算我沒有處理roll back,之前相關被lock的資源,也都一定會釋放,

同時也不影響其他session存取相關資源。

 

沒有Lock、沒有活動交易

更沒有blocking

真實世界,每個系統都有不同設計考量,web base系統的db connection如是交由GC來處理,

那麼最好啟用Xact_abort來防止上述情況。

啟用xact_abort

alter proc usp_addmynumbers
(
@c1 int,@c2 varchar(10)
)
as
set nocount on;
set xact_abort on;
begin tran
  insert dbo.mynumbers values(@c1,@c2)
  waitfor delay '00:01:00'
commit

AP端的connection交由GC自行處理,然後再重複執行一次

相關資源被Lock

發生timeout錯誤

因為啟用Xact_abort,所以該交易自動被SQL Server給roll back,

相關LOCK資源也都一起release,當然更不可能有blocking其他session情況。

上面我用console來示範,所以情況比較單存,如果是web application則存在很多我們可能沒有想到的情況,

如同我一開始所說,造成這種情況的主因很多,所以我個人在web-base系統上還是會兩邊都處理,

AP這邊處理完成後立即close connection,也在catch block判斷交易roll back,

SP那裏當然也會啟用xact_abort多一道防線。

 using (sqlConnection)
            {
                const string sql = "dbo.usp_addmynumbers";
                SqlTransaction ts = null;
                try
                {
                    if (sqlConnection.State == ConnectionState.Closed)
                        sqlConnection.Open();
                    ts = sqlConnection.BeginTransaction("SampleTransaction");
                    using (SqlCommand command = new SqlCommand(sql, sqlConnection))
                    {
                        command.Transaction = ts;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(
                            new SqlParameter
                            {
                                ParameterName = "c1",
                                SqlDbType = SqlDbType.Int,
                                Value = c1
                            });
                        command.Parameters.Add(
                            new SqlParameter
                            {
                                ParameterName = "c2",
                                SqlDbType = SqlDbType.VarChar,
                                Value = c2
                            });
                        var rowsAffected = command.ExecuteNonQuery();
                        ts.Commit();
                        Console.WriteLine($"Both records are written to database: {rowsAffected}");
                        return 1;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("  Message: {0}", ex.Message);
                    //Attempt to roll back the transaction.
                    try
                    {
                        ts.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        // This catch block will handle any errors that may have occurred
                        // on the server that would cause the rollback to fail, such as
                        // a closed connection.
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                    }
                    return -1;
                }
            }

 

同事詢問,xact_abort是否可以全域設定?全域設定是否有什麼副作用呢?是否會和.net roll back衝突呢?

a.可以全域設定。至於副作用我個人到覺得沒有,如果真要說的話,

那就是有些系的作業統特性不允許整批roll back交易,因為啟用xact_abort會自動roll back交易和相依交易,

這樣的特性可能會破壞系統這些job特性,例如job可能夜間跑批次更新作業,

就算遇到某一筆新增失敗,但就先跳過該筆資料,繼續處理其他資料,後續再來補跑這些失敗的資料。

另外,我和國外一些專家討論,他們提供了不錯的文章給我,如下

ROLLBACK damn you!

Baby baby baby, where did our love/ data go?

全域設定是否適合現有系統,還請自行考量

EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE
IF (@@OPTIONS & 16384)>0
print 'xact_abort is on'
else
print 'xact_abort is off'
DBCC USEROPTIONS

b.不會和.net roll back衝突。

 

參考

Configure the user options Server Configuration Option

[C#]遵守TSQL王道的TinyORM

Orphaned Locks – Idle Session – and XACT_ABORT

使用XACT_STATE確認失敗交易

SET XACT_ABORT (Transact-SQL)

XACT_ABORT AND QUERY TIMEOUT IN SQL SERVER