[SQL Server][Lock]透過擴充事件找鎖定的物件以及被誰Blocking

交易一多起來,慢慢會碰上交易封鎖(Block),人若剛好在案發現場能下SQL Query鑑識,透過DMV、sp_trace、profiler、sqldiag等都能快速釐清交易間不單純的案情;但如果想長期追蹤,可以試試SQL Server 2008推出的擴充事件(Extended events),利用blocked_process_report事件幫我們紀錄過長時間的封鎖(block)。

*blocked_process_report是SQL工作遭到封鎖的時間超過”已封鎖處理序臨界值”時的報表。

紐約曼哈頓第五大道的封鎖

 


blocked process threshold

已封鎖處理序臨界值定義了“多長”的時間視為過長的封鎖,我們可以在伺服器組態設定0到86400秒(1天)的數值,預設是0(不啟動),設定>=5以上才有效。

這邊我們先設定封鎖超過5秒就和長官報告,打開管理工具連接要設定的SQL Instance

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 5 ;  
GO  
RECONFIGURE ;  
GO  

 

設定完成後,查詢系統檢視目錄

 


準備環境

USE tempdb

CREATE TABLE T1
(ID INT IDENTITY,NAME NVARCHAR(20), PRIMARY KEY(ID))
INSERT INTO T1 VALUES('JAVA'),('C#'),('PYTHON'),('R'),('COBOL')

SELECT * FROM T1

 

好的,現在我們有5個很厲害的語言了。

 


建立擴充事件監控過長時間的封鎖(Block)

 (1)建立擴充事件(Event Session)

這邊我們先選擇用指令碼來建,選擇target file到指定目錄下的檔案(這邊可以依需要選擇目的地)

CREATE EVENT SESSION [TransactionBlock] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.database_name)) 
ADD TARGET package0.event_file(SET filename=N'C:\temp\db\TransactionBlock.xel')
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=ON,STARTUP_STATE=OFF)
GO

(2)啟動擴充事件(Event Session)

ALTER EVENT SESSION TransactionBlock
      ON SERVER
STATE = START;   -- STOP;

 

或是,SQL Server 2012開始也能使用工作階段精靈建立擴充事件: 

 


C#真棒!

製作交易封鎖情境: 交易1更新但不commit,交易2查詢

1.Ctrl + N新開一個SQL查詢視窗(建立連線1,鎖定資料,但不Commit)

Use tempdb
BEGIN TRAN
UPDATE T1
    SET NAME = 'C#真棒!'
    WHERE ID = 3

 

2.Ctrl + N新開第二個SQL查詢視窗(建立連線2,執行查詢)。

Use tempdb
SELECT * from T1

 

左邊是交易1(Session id=56)的執行,右邊是交易2(Session = 58)的執行。

因為資料被交易1(56)鎖定(X: exclusive lock),因此交易2(58)想要查詢(S: shared lock)被封鎖(block),需要等待。

 


檢視擴充事件收集到的事件:透過管理工具介面

打開SSMS管理工具

1.管理 > 2.擴充事件 > 3.打開TransactionBlock- Package0.event_file > 4.選取事件 > 5.滑鼠點blocked-process兩下

每5秒就紀錄1次

點選後跳出

 

<blocked-process>:被封鎖的交易(被害人)

<blocking-process>:封鎖其他人的交易(嫌犯)

 


查詢擴充事件結果:透過T-SQL

除了透過管理介面查詢Event結果,也能使用T-SQL查,我們將event data轉換成xml type再下去用xml.query查

SELECT 
 convert(xml,event_data).query('/event').value('(/event/@timestamp)[1]', 'Datetime' ) blocked_time
,convert(xml,event_data).query('/event') as blocked_process_report
FROM sys.fn_xe_file_target_read_file('C:\temp\db\TransactionBlock*.xel', null , null, null);

查詢結果,滑鼠點擊xml超連結,也能查看blocked process report。

 


修改blocked process threshold

注意到幾乎每5秒產生一次事件報表:

 

按照msdn範例改成20秒

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 20 ;  
GO  
RECONFIGURE ;  
GO  

 

重新測試一次剛剛c#好棒的情境,讓他們持續block幾分鐘,每20秒抓到一次

 


小結:

  • 先記載下來過長的封鎖。
  • 單純的查詢封鎖問題,可以試試啟用資料庫的Read Committed Snapshot(SQL Server 2005推出,單版本的隔離)。
  • READ_COMMITTED_SNAPSHOT (單版本) ALLOW_SNAPSHOT_ISOLATION(多版本)
  • “SET” LOCK_TIMEOUT

 

 


參考:

[SQL Server][DeakLock]觀察死結的工具(四)擴充事件(Extended events)

Rico大大的[SQL SERVER]找出封鎖的處理序

[SQL Server][Lock]透過DMV找鎖定的物件、類型以及被誰Blocking

已封鎖的處理序臨界值伺服器組態選項

Blocked Process Report 事件類別