SQL Server事務遭遇網路異常時的處理機制淺析

SQL Server資料庫中，如果應用程式正在執行一個事務的時候突然遭遇了網路異常，例如網路掉包，網路中斷等，那麼這個事務會怎麼樣？ SQL Server資料庫是通過什麼機制來判斷處理呢？ 估計很多人跟我一樣都有不少疑問， 我們下麵構造一個測試實驗來測試驗證一下。如下所示：

步驟1：在客戶端連使用SSMS工具連接到測試資料庫，執行下麵腳本，顯性事務既不提交也不回滾。模擬事務正在執行當中。

USE AdventureWorks2012;

GO

SELECT@@SPID;

BEGINTRAN

    DELETEFROM [dbo].[Products] WHERE ProductID=1;

    --ROLLBACK;

輸出的會話ID為59

步驟2：在測試伺服器上開啟Profiler跟蹤一下具體信息。具體步驟略過。

步驟3：通過VMware vSphere Client的控制台連接到測試伺服器，禁用網卡，然後啟用網卡，模擬網路異常。（註意：玩過Vmware的應該都知道，這裡不詳細介紹！）

如下截圖所示，在跟蹤過程中，我們可以看到當我構造網路異常時，會話ID=59的事務立即回滾了。

當然你也可以使用下麵函數查看日誌裡面的相關記錄信息。如下所示：

SELECT*

FROMfn_dblog(NULL,NULL)

WHERE Operation ='LOP_ABORT_XACT';

你可以看到 LOP_BEGIN_XACT （事務開始）->   LOP_DELETE_ROWS （刪除記錄） ->  LOP_INSERT_ROWS （插入記錄）  ->LOP_ABORT_XACT （事務回滾）

通過上面實驗測試，我們知道當應用程式遭遇網路異常時，資料庫會回滾未提交的事務。那麼接下來的問題有下麵幾個：

1： SQL Server需要多長時間才能檢測到會話的網路異常？

如上所示，我斷開的是伺服器的網路，會話立即就回滾了。但是如果我斷開的是客戶端（執行SSMS客戶端的網路），那麼會話回滾的時間是30秒。如下截圖所示

事務開始時間為： 2017-07-27 13:48:01:820

事務回滾時間為： 2017-07-27 13:48:32.043

這個是伺服器上Keep Alive參數控制的，具體位置 “SQL Server Configuration Manager”->  “SQL Server Network Configuration” ->  "Protocol for MSSQLSERVER" ->  "TCP/IP " 右鍵單擊屬性，如下截圖所示：

30000 的單位是毫秒， 等價於30秒， 如果你將這個設置為60000 ，那麼測試結果就會是60秒或超過60秒。

當然這個時間差是你斷開網路的時間和事務結束的時間差，而不是事務開始時間與結束時間差，如下測試所示，截圖1，由於需找到禁用網路的位置，然後又切換視窗，導致延誤了幾秒，這個事務開始、結束時間差為70秒。 當然這個值不可能完全等於Keep Alive的值，因為還涉及參數Keep Alive Interval的值，所以這個值玩玩是大於等於Keep Alive的值。具體後面會講述！

2： SQL Server通過什麼機制來判斷當前會話遭遇了網路異常？

在這篇“ORACLE的Dead Connection Detection淺析”文章裡面， 我介紹了Linux系統下TCP KeepAlive概念，顧名思義，TCP keepalive它是用來保持TCP連接的，註意它只適用於TCP連接。系統會替你維護一個timer，時間到了，就會向remote peer發送一個probe package，當然裡面是沒有數據的，對方就會返回一個應答，這時你就知道這個通道保持正常。與TCP keepalive有關的三個參數tcp_keepalive_time、tcp_keepalive_intvl、tcp_keepalive_probes

/proc/sys/net/ipv4/tcp_keepalive_time        當keepalive起用的時候，TCP發送keepalive消息的頻度。預設是2小時。

/proc/sys/net/ipv4/tcp_keepalive_intvl      當探測沒有確認時，keepalive探測包的發送間隔。預設是75秒。

/proc/sys/net/ipv4/tcp_keepalive_probes     如果對方不予應答，keepalive探測包的發送次數。預設值是9。

其實在Windows系統中也有類似的參數，分別是KeepAliveTime、KeepAliveInterval、TcpMaxDataRetransmissions

KeepAliveTime               預設是7,200,000 milliseconds = 2 hours