一、死鎖簡(jiǎn)介

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

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

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

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

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

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

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

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

1.了解死鎖

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

2.檢測(cè)死鎖：

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

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

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

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

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

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

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

• 方法：

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

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

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

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

首先，你需要?jiǎng)?chuàng)建一個(gè)擴(kuò)展事件會(huì)話來(lái)捕獲死鎖圖。你可以使用SQL Server Management Studio (SSMS)的圖形界面來(lái)創(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)建了一個(gè)名為Deadlock_Graph_Capture的擴(kuò)展事件會(huì)話。
• 它捕獲sqlserver.xml_deadlock_report事件，這是當(dāng)死鎖發(fā)生時(shí)SQL Server生成的事件。
• 目標(biāo)（TARGET）被設(shè)置為一個(gè)事件文件，該文件的路徑和命名約定是你可以自定義的。在這個(gè)例子中，我使用了日期時(shí)間戳來(lái)命名文件，并設(shè)置了最大文件大小和滾動(dòng)文件數(shù)。

2.啟動(dòng)會(huì)話 

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

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

3. 停止和刪除會(huì)話（如果需要）

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

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文件的工具來(lái)查看死鎖圖。在SSMS中，你可以通過(guò)“管理”>“擴(kuò)展事件”>“會(huì)話”來(lái)查看和管理你的擴(kuò)展事件會(huì)話，并可以直接從SSMS中打開捕獲的事件文件來(lái)查看死鎖圖。

5. 注意事項(xiàng)

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

（3）、使用DBCC TRACEON檢測(cè)死鎖

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

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

（4）、使用SQL Server Profiler檢測(cè)死鎖 

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

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

你還可以查詢SQL Server的性能計(jì)數(shù)器來(lái)獲取死鎖的數(shù)量。例如，你可以使用以下查詢來(lái)獲取自上次重啟以來(lái)發(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';

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

（6）、 注意事項(xiàng)

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

3.分析死鎖 

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

4.解決死鎖： 

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

 然后殺死想要?dú)⑺赖倪M(jìn)程

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

5.避免未來(lái)死鎖：

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

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

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

7.注意

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

總結(jié) 

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

最新評(píng)論