史丹利好熱

*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 事件類別