SQL SERVER-查看语句申请锁的情况

SQL SERVER-查看语句申请锁的情况

想要观察一个SQL语句运行过程中申请了哪些锁，打开一个查询写入要被观察的SQL

--查询当前会话的sessionID
SELECT @@SPID


SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ

BEGIN TRAN

SELECT [data] FROM [MyTable] WHERE [index_column]>30 AND [index_column]<40  


---执行了上面的SQL，观察完后再回滚

ROLLBACK 

在打开另外一个查询，查询上个语句运行的锁情况

--填入要观察的sesionID,

SELECT 
request_session_id,
resource_type,
request_status,
request_mode,
resource_description,
OBJECT_NAME(p.object_id) as object_name,
i.name,
p.index_id
FROM sys.dm_tran_locks  t
LEFT JOIN sys.partitions p  on  t.resource_associated_entity_id=p.hobt_id
LEFT JOIN  sys.indexes i on  i.object_id=p.object_id and p.index_id=i.index_id
WHERE  request_session_id=88
ORDER BY request_session_id,resource_type

查询该进程等待的锁资源

SELECT 
request_session_id,
resource_type,
request_status,
request_mode,
resource_description,
OBJECT_NAME(p.object_id) as object_name,
i.name,
p.index_id
FROM sys.dm_tran_locks  t
LEFT JOIN sys.partitions p  on  t.resource_associated_entity_id=p.hobt_id
LEFT JOIN  sys.indexes i on  i.object_id=p.object_id and p.index_id=i.index_id
WHERE  request_status='WAIT'  AND  request_session_id=87
ORDER BY request_session_id,resource_type