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

2017-05-13

4065
0
Lock
2017-05-14

除了打開管理工具中的活動監視器(Activity Monitor)外，來試試幾個動態管理檢視(DMV)的組合(dm_tran_locks x dm_os_waiting_tasks x dm_exec_connections)。

建立環境

建立一個簡單的Table T1，然後寫入5筆很厲害的語言資料。

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個很厲害的語言了。

C#真棒!

接著進入活動最精彩的喊口號時間: C#真棒! .NET好威呀!

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

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

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

SELECT * from T1

執行如下，左邊是Session id=52的執行，右邊是Session = 53的執行。

session id=53，持續執行中，資料表當中的資料被鎖定，暫時被Block

3.透過動態管理物件(Dmv)組合來找當下資料庫內相互之間的鎖定及阻塞關係:

首先取得目前系統中被鎖定的交易(dm_tran_locks)以及被鎖定交易的session id，接著從鎖定擁有者的位址關聯到等待資源的情形(dm_os_waiting_tasks)，從中找出封鎖者的session id(blocking_session_id)後，最後再從系統目前執行的交易連接中(dm_exec_connections)找到最後執行的T-SQL指令。

找出被鎖定物件以及語法:


use tempdb
--找鎖定的物件、類型以及被誰Blocking
SELECT
        tl.request_session_id AS [我的SID]
        ,tl.resource_type AS [資源類型]
        ,DB_NAME(tl.resource_database_id) AS [資料庫名稱]
        ,(CASE resource_type
                WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
                ELSE (SELECT
                                        OBJECT_NAME(object_id)
                                FROM sys.partitions
                                WHERE hobt_id = resource_associated_entity_id)
        END) AS [物件名稱]
        ,tl.resource_description AS [資源說明]
        ,tl.request_mode AS [鎖定類型]
        ,tl.request_status AS [狀態]
        ,wt.blocking_session_id AS [被阻塞SID]
        ,c.connect_time AS [連接時間]
        ,txt.text AS [最近執行語法]
	   ,lock_txt.text AS [被阻塞的執行語法]
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_os_waiting_tasks AS wt
        ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.dm_exec_connections AS c
        ON tl.request_session_id = c.session_id
LEFT JOIN sys.dm_exec_connections AS d
        ON wt.blocking_session_id = d.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt 
OUTER APPLY sys.dm_exec_sql_text(d.most_recent_sql_handle) lock_txt 
WHERE resource_type != 'DATABASE'
  AND tl.request_session_id > 50 
ORDER BY tl.request_session_id
GO

可以從查詢結果集中發現sid=53的交易準備對T1作共用鎖(S:share lock)，但被sid=52的交易 block，因為她已經將資料獨佔鎖中(X:Exclusive lock )。

sid=53的語法是SELECT * FROM T1

sid=52的語法是BEGIN TRAN UPDATE T1...