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可能夜間跑批次更新作業，
就算遇到某一筆新增失敗，但就先跳過該筆資料，繼續處理其他資料，後續再來補跑這些失敗的資料。
另外，我和國外一些專家討論，他們提供了不錯的文章給我，如下
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