一、死鎖簡介

SQL Server數(shù)據(jù)庫死鎖是指在多個事務(wù)同時訪問數(shù)據(jù)庫資源時，發(fā)生了互相等待對方所持有資源的情況，導(dǎo)致所有事務(wù)無法繼續(xù)執(zhí)行的現(xiàn)象。當(dāng)發(fā)生死鎖時，SQL Server會自動選擇一個事務(wù)進(jìn)行回滾，以解除死鎖并允許其他事務(wù)繼續(xù)執(zhí)行。這篇文章為個人學(xué)習(xí)筆記，可能會有錯誤。

二、死鎖發(fā)生原因

1、死鎖通常發(fā)生在以下情況下：

• 并發(fā)訪問數(shù)據(jù)庫資源：多個事務(wù)同時訪問相同數(shù)據(jù)庫資源，如表、行、頁等。
• 鎖的粒度不同：事務(wù)對數(shù)據(jù)庫資源的鎖定級別不同，如某個事務(wù)對某個表進(jìn)行了排他鎖，同時另一個事務(wù)也對該表進(jìn)行了共享鎖。
• 事務(wù)的執(zhí)行順序不同：多個事務(wù)按不同的順序訪問數(shù)據(jù)庫資源，導(dǎo)致發(fā)生循環(huán)依賴的死鎖。

2、為了避免死鎖的發(fā)生，可以采取以下措施：

• 優(yōu)化事務(wù)的設(shè)計：盡量減少事務(wù)的長度和對資源的占用時間，減少死鎖的發(fā)生概率。
• 使用合適的鎖：選擇合適的鎖級別，避免對資源造成過多的限制。
• 控制事務(wù)的并發(fā)度：限制同一時間內(nèi)可以同時執(zhí)行的事務(wù)數(shù)目，減少死鎖的概率。
• 監(jiān)控和調(diào)整數(shù)據(jù)庫性能：定期監(jiān)控數(shù)據(jù)庫的性能，及時發(fā)現(xiàn)并解決潛在的死鎖問題。

總之，SQL Server數(shù)據(jù)庫死鎖是一種常見的并發(fā)訪問問題，可以通過優(yōu)化事務(wù)設(shè)計、使用合適的鎖、控制事務(wù)并發(fā)度和監(jiān)控數(shù)據(jù)庫性能等方式來減少死鎖的發(fā)生。

三、發(fā)生死鎖后的處理方法

1.了解死鎖

• 死鎖是一種狀態(tài)，發(fā)生在兩個或多個事務(wù)相互等待對方釋放資源時，導(dǎo)致它們都無法繼續(xù)執(zhí)行。
• 它經(jīng)常與正常阻塞混淆，但死鎖幾乎可以立即解決，而阻塞理論上可以無限期地持續(xù)存在。

2.檢測死鎖：

（1）、使用系統(tǒng)視圖檢測死鎖

SQL Server提供了一些系統(tǒng)視圖來幫助檢測死鎖，其中最重要的是sys.dm_tran_locks和sys.dm_exec_requests。

• sys.dm_tran_locks視圖顯示了當(dāng)前數(shù)據(jù)庫中所有的鎖信息。
• sys.dm_exec_requests視圖顯示了當(dāng)前正在執(zhí)行的所有請求信息。

• SELECT * FROM sys.dm_tran_locks
  SELECT * FROM sys.dm_exec_requests

你可以通過觀察這兩個視圖的內(nèi)容來判斷是否存在死鎖情況。例如，你可以查詢sys.dm_tran_locks來查看當(dāng)前的鎖狀態(tài)，并結(jié)合sys.dm_exec_requests來查看請求的狀態(tài)和等待的資源。 

（2）、使用擴(kuò)展事件檢測死鎖

從SQL Server 2012開始，建議使用擴(kuò)展事件（xEvent）來檢測死鎖，而不是使用SQL跟蹤或SQL Profiler。你可以使用xml_deadlock_report擴(kuò)展事件來捕獲死鎖信息。

• 方法：

  確保system_health會話已經(jīng)啟用（這是默認(rèn)設(shè)置）。

  你可以直接查詢system_health會話的環(huán)形緩沖區(qū)來獲取死鎖事件的日志。

使用擴(kuò)展事件（Extended Events）來檢測死鎖在SQL Server中是一種非常有效的方法。以下是如何使用擴(kuò)展事件來捕獲死鎖圖（Deadlock Graph）的具體步驟和示例代碼：

1. 創(chuàng)建會話（Session）

首先，你需要創(chuàng)建一個擴(kuò)展事件會話來捕獲死鎖圖。你可以使用SQL Server Management Studio (SSMS)的圖形界面來創(chuàng)建，但在這里我將給出T-SQL代碼示例。

CREATE EVENT SESSION [Deadlock_Graph_Capture]  
ON SERVER   
ADD EVENT sqlserver.xml_deadlock_report  
ADD TARGET package0.event_file(SET filename=N'D:\DeadlockGraphs\DeadlockGraph_$(ESCAPE_SQUOTE(FILE_NAME_FOR_DATE(GETDATE(), 'yyyyMMdd_HHmm'))).xel',max_file_size=(5),max_rollover_files=(4))  
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

注意：

• 上述代碼創(chuàng)建了一個名為Deadlock_Graph_Capture的擴(kuò)展事件會話。
• 它捕獲sqlserver.xml_deadlock_report事件，這是當(dāng)死鎖發(fā)生時SQL Server生成的事件。
• 目標(biāo)（TARGET）被設(shè)置為一個事件文件，該文件的路徑和命名約定是你可以自定義的。在這個例子中，我使用了日期時間戳來命名文件，并設(shè)置了最大文件大小和滾動文件數(shù)。

2.啟動會話 

創(chuàng)建會話后，你需要啟動它以開始捕獲事件。

ALTER EVENT SESSION [Deadlock_Graph_Capture] ON SERVER STATE = START;

3. 停止和刪除會話（如果需要）

如果你不再需要捕獲死鎖圖，或者想要重置會話設(shè)置，你可以停止并刪除會話。 

ALTER EVENT SESSION [Deadlock_Graph_Capture] ON SERVER STATE = STOP;  
DROP EVENT SESSION [Deadlock_Graph_Capture] ON SERVER;

4. 查看死鎖圖

死鎖圖將被捕獲到你在步驟1中指定的文件路徑中。你可以使用SQL Server Management Studio (SSMS)或其他能夠打開.xel文件的工具來查看死鎖圖。在SSMS中，你可以通過“管理”>“擴(kuò)展事件”>“會話”來查看和管理你的擴(kuò)展事件會話，并可以直接從SSMS中打開捕獲的事件文件來查看死鎖圖。

5. 注意事項

• 確保你有足夠的磁盤空間來存儲捕獲的死鎖圖文件。
• 根據(jù)你的需求調(diào)整事件文件的最大大小和滾動文件數(shù)。
• 定期檢查并管理你的擴(kuò)展事件會話和文件，以避免不必要的磁盤空間占用。

（3）、使用DBCC TRACEON檢測死鎖

你可以使用DBCC TRACEON命令來開啟特定的跟蹤標(biāo)志（trace flag），如1204或1222，以捕獲死鎖信息。這些跟蹤標(biāo)志會將死鎖信息輸出到SQL Server的錯誤日志中。

• 開啟跟蹤：DBCC TRACEON (1204,-1) 或 DBCC TRACEON (1222,-1)
• 關(guān)閉跟蹤：DBCC TRACEOFF (1204,-1) 或 DBCC TRACEOFF (1222,-1)

（4）、使用SQL Server Profiler檢測死鎖 

 雖然SQL Server Profiler在2016年已被棄用，但在早期版本中，你可以使用它來檢測死鎖。在Profiler中，你可以配置特定的事件（如Deadlock graph）來捕獲死鎖信息，這里就不多介紹了。

（5）、查詢性能計數(shù)器 

你還可以查詢SQL Server的性能計數(shù)器來獲取死鎖的數(shù)量。例如，你可以使用以下查詢來獲取自上次重啟以來發(fā)生的死鎖次數(shù)

SELECT cntr_value AS NumOfDeadLocks   
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Locks'   
AND counter_name = 'Number of Deadlocks/sec'   
AND instance_name = '_Total';

但請注意，這個查詢返回的是每秒的死鎖數(shù)量，而不是總數(shù)量。

（6）、 注意事項

• 在檢測死鎖時，請確保你有足夠的權(quán)限來訪問上述的系統(tǒng)視圖和擴(kuò)展事件。
• 死鎖是一個復(fù)雜的問題，可能需要你深入分析數(shù)據(jù)庫的操作和事務(wù)邏輯來找到根本原因。
• 在解決死鎖問題時，務(wù)必考慮到數(shù)據(jù)的完整性和一致性，避免為了解決死鎖而犧牲這些重要的數(shù)據(jù)庫特性。

3.分析死鎖 

• 確定導(dǎo)致死鎖的具體事務(wù)和資源。
• 分析這些事務(wù)的邏輯，找出為何它們會相互等待對方釋放資源。

4.解決死鎖： 

• 優(yōu)化鎖的粒度：使用較低級別的鎖（如行鎖ROWLOCK）代替頁面鎖或表鎖，以減少鎖定范圍，提高并發(fā)性。
• 使用READ COMMITTED SNAPSHOT或SNAPSHOT事務(wù)隔離級別：這可以將讀取操作與其他事務(wù)隔離，減少鎖定沖突。
• 保持事務(wù)簡短并減少鎖定時間：縮短事務(wù)持續(xù)時間，減少鎖定資源的時間，有助于減少死鎖風(fēng)險。
• 按照相同的順序訪問資源：這樣可以避免死鎖，因為任何給定時刻，事務(wù)只需要等待另一個事務(wù)釋放資源。
• 使用TRY...CATCH語句監(jiān)視死鎖錯誤：如果死鎖發(fā)生，事務(wù)將被捕獲并顯示錯誤信息，可以選擇重試事務(wù)。
• 使用NOLOCK選項（注意風(fēng)險）：對于只讀查詢，使用NOLOCK可以避免發(fā)生死鎖，但可能會導(dǎo)致臟讀。
• 當(dāng)死鎖發(fā)生時直接殺進(jìn)程：

首先查詢死鎖的表和進(jìn)程

select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT'

 然后殺死想要殺死的進(jìn)程

kill spid--spid為進(jìn)程號

5.避免未來死鎖：

• 避免事務(wù)中的用戶交互：減少事務(wù)中等待用戶輸入的時間，以降低死鎖的風(fēng)險。
• 使用較低的隔離級別：如READ COMMITTED，這可以減少鎖定的時間。
• 使用基于行版本控制的隔離級別：如SNAPSHOT，這可以消除許多鎖定沖突。
• 使用綁定連接：確保相關(guān)的會話以相同的順序訪問資源。

6.監(jiān)控和調(diào)優(yōu)： 

• 定期監(jiān)控數(shù)據(jù)庫的性能和死鎖情況。
• 根據(jù)監(jiān)控結(jié)果，對數(shù)據(jù)庫和應(yīng)用程序進(jìn)行調(diào)優(yōu)，以減少死鎖的發(fā)生。

7.注意

• 在處理死鎖時，務(wù)必考慮到數(shù)據(jù)的完整性和一致性，避免為了解決死鎖而犧牲這些重要的數(shù)據(jù)庫特性。
• 不同的應(yīng)用場景可能需要采用不同的死鎖處理策略，因此需要根據(jù)實際情況進(jìn)行選擇。

總結(jié) 

到此這篇關(guān)于SQL Server數(shù)據(jù)庫死鎖的原因及處理的文章就介紹到這了,更多相關(guān)SQLServer數(shù)據(jù)庫死鎖處理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論