SQL Server資料庫中,如果應用程式正在執行一個事務的時候突然遭遇了網路異常,例如網路掉包,網路中斷等,那麼這個事務會怎麼樣? SQL Server資料庫是通過什麼機制來判斷處理呢? 估計很多人跟我一樣都有不少疑問, 我們下麵構造一個測試實驗來測試驗證一下。如下所示: 步驟1:在客戶端連使用S ...
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