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

--查询当前会话的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