09-sp_lock和sys.dm_tran_locks的用法

一、总结

1.网址

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?redirectedfrom=MSDN&view=sql-server-2017（sp_lock）
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017(sys.dm_tran_locks)

2.查出结果具体含义参考官方文档

二、用到的SQL

1.根据object_id查看对象名称
命令：select OBJECT_NAME(245575913)
           SELECT OBJECT_ID('run.dbo.T1')

　　　

 2.查询所有的锁

命令：exec sp_lock

 3.查询指定进程的锁

命令：exec sp_lock 55

 4.根据系统视图查询指定进程的锁

命令：select * from sys.dm_tran_locks where request_session_id=55

 5.显示某个库的锁信息，参数是dbid

命令：SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = 5

6.显示阻塞信息
命令：SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

 

转载于:https://www.cnblogs.com/jialanyu/p/11578279.html