交易一多起來,慢慢會碰上交易封鎖(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)
[SQL Server][Lock]透過DMV找鎖定的物件、類型以及被誰Blocking