最近在分析SQL Server的死鎖時，發現一個比較有意思的現象，發現死鎖當中一個會話的隔離級別為序列化（Serializable），這個是讓人比較奇怪的地方，我們知道SQL Server資料庫的預設隔離級別為已提交讀（READ COMMITTED），除非人為設置事務隔離級別(TRANSACTION... ...
最近在分析SQL Server的死鎖時，發現一個比較有意思的現象，發現死鎖當中一個會話的隔離級別為序列化（Serializable），這個是讓人比較奇怪的地方，我們知道SQL Server資料庫的預設隔離級別為已提交讀（READ COMMITTED），除非人為設置事務隔離級別(TRANSACTION ISOLATION LEVEL)，否則事務隔離級別會使用資料庫的預設隔離級別。在分析了死鎖相關的存儲過程後，沒有發現有人為修改事務隔離級別的地方。在分析過後，我們判斷應該是在應用程式代碼裡面有設置隔離級別，下麵我們通過一個小實驗來構造這樣的一個案例。
測試環境資料庫為AdventureWorks2014，如下所示，我簡單寫了一點C#代碼，截取黏貼部分C#代碼在此，在這段代碼中，我們使用TransactionScope，我們先更新Sales.SalesOrderDetail,然後查詢 [Sales].[SalesOrderHeader]的相關數據來綁定Grid控制項
try {using (TransactionScope scope = new TransactionScope())
{using (SqlConnection conn = new SqlConnection(connString))
{string cmdText = "UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1;";
SqlCommand cmd = new SqlCommand(cmdText, conn);conn.Open();
cmd.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connString))
{ DataSet sqldataset = new DataSet(); string cmdText = "SELECT * FROM [Sales].[SalesOrderHeader] WHERE SalesOrderID=43659;";
SqlCommand cmd = new SqlCommand(cmdText, conn); SqlDataAdapter sqladapter = new SqlDataAdapter(cmdText, conn); sqladapter.Fill(sqldataset, "spt_values");gvData.DataSource = sqldataset;
gvData.DataBind();
}
scope.Complete();
}
}
catch (TransactionAbortedException exc) { log.Error("錯誤", exc);}
然後另外一個會話，就直接用SSMS開啟一個事務（懶得構造C#代碼案例，主要是太浪費時間了），主要執行下麵邏輯：
BEGIN TRAN
UPDATE [Sales].[SalesOrderHeader] SET SubTotal = SubTotal + 10
WHERE SalesOrderID=43659;WAITFOR DELAY '00:00:10';
SELECT TOP 10 * FROM Sales.SalesOrderDetail
--ROLLBACK TRAN;
執行上面SQL語句，然後運行最上面C#代碼，立馬就能構造出一個死鎖案例，如下截圖所示，測試環境為SQL Server 2014，我就使用擴展事件system_health捕獲的死鎖（當然，你可以使用任何方式，例如Profile或Trace捕獲死鎖相關信息），使用SQL將死鎖的XML信息查出
如下所示，你會看到使用TransactionScope的會話的隔離級別為isolationlevel="serializable (4)"， 具體可以參考下麵死鎖的XML文件。
<deadlock>
<victim-list>
<victimProcess id="process17676e108" />
</victim-list>
<process-list>
<process id="process17676e108" taskpriority="0" logused="384" waitresource="KEY: 7:72057594048479232 (0ca7b7436f59)" waittime="379" ownerId="46635671" transactionname="user_transaction" lasttranstarted="2019-04-02T23:26:21.150" XDES="0x17f0511f0" lockMode="S" schedulerid="1" kpid="13440" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-04-02T23:26:21.147" lastbatchcompleted="2019-04-02T23:26:09.343" lastattention="1900-01-01T00:00:00.343" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MyNB00021" hostpid="9728" loginname="test" isolationlevel="read committed (2)" xactid="46635671" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="8" stmtstart="282" stmtend="368" sqlhandle="0x020000002a285923f5e38f7347b53337195c56a4a1bc33080000000000000000000000000000000000000000">
unknown </frame></executionStack>
<inputbuf>
BEGIN TRANUPDATE [Sales].[SalesOrderHeader] SET SubTotal = SubTotal + 10
WHERE SalesOrderID=43659; WAITFOR DELAY '00:00:10';SELECT TOP 10 * FROM Sales.SalesOrderDetail </inputbuf>
</process>
<process id="process175603c28" taskpriority="0" logused="436" waitresource="KEY: 7:72057594048544768 (6a8a6db47ef5)" waittime="4420" ownerId="46635065" transactionname="user_transaction" lasttranstarted="2019-04-02T23:25:36.807" XDES="0x1762fa9f0" lockMode="S" schedulerid="1" kpid="51760" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-02T23:26:26.450" lastbatchcompleted="2019-04-02T23:25:36.807" lastattention="1900-01-01T00:00:00.807" clientapp=".Net SqlClient Data Provider" hostname="MyNB00021" hostpid="1700" loginname="kkk" isolationlevel="serializable (4)" xactid="46635065" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"><executionStack>
<frame procname="AdventureWorks2014.Sales.iduSalesOrderDetail" line="18" stmtstart="982" stmtend="2448" sqlhandle="0x0300070076146e6c18e00a016ba3000000000000000000000000000000000000000000000000000000000000">
INSERT INTO [Production].[TransactionHistory]
([ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT inserted.[ProductID]
,inserted.[SalesOrderID]
,inserted.[SalesOrderDetailID]
,'S',GETDATE()
,inserted.[OrderQty]
,inserted.[UnitPrice]
FROM inserted INNER JOIN [Sales].[SalesOrderHeader]
ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID </frame><frame procname="adhoc" line="1" stmtstart="52" stmtend="262" sqlhandle="0x02000000abf4ee0ff24fea415c6f35709c721203030a173b0000000000000000000000000000000000000000">
unknown </frame><frame procname="adhoc" line="1" stmtend="186" sqlhandle="0x02000000b0cd40243d43ed1a51b1baa9cbf70d1628eae7880000000000000000000000000000000000000000">
unknown </frame></executionStack>
<inputbuf>
UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1; </inputbuf>
</process>
</process-list>
