T-SQL筆記61_常用到的查找lock table指令

 

SELECT request_session_id AS spid,

    resource_type AS rt,

    resource_databASe_id AS rdb,

    (CASE resource_type 

    WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id) 

    WHEN 'DATABASE' then 'sombas' 

    ELSE 

    (SELECT object_name(object_id) FROM sys.partitions 

        WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname,

    resource_description AS rd,

    request_mode AS rm,

    request_status AS rs

FROM sys.dm_tran_locks where request_mode IN ('X','IX')

--X 表示 dead lock

--IX 表示 wait lock ，這種是因為前一個Request已經dead lock，而被pending的狀態。

kill {上一個查詢結果集的 spid}

SELECT * FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID() 

AND resource_associated_entity_id = OBJECT_ID(N'dbo.Table名');

select cmd,* from sys.sysprocesses

where blocked > 0    

    SELECT r.session_id,

    r.status AS [指令狀態],

    r.command AS [指令類型],

    r.wait_time/1000.0 AS [等待時間(秒)],

    s.client_interface_name AS [連線資料庫的驅動程式],

    s.host_name AS [電腦名稱],

    s.program_name AS [執行程式名稱],

    t.text AS [執行的SQL語法],

    r.blocking_session_id AS [被鎖定卡住的session_id]

    FROM sys.dm_exec_requests r

    INNER JOIN sys.dm_exec_sessions s

    ON r.session_id = s.session_id

    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

    WHERE s.is_user_process = 1;