sqlserver查看死锁命令

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName

from sys.dm_tran_locks where resource_type='OBJECT'

下面这个更好用一些

select t1.resource_type [资源锁定类型]

, DB_NAME(resource_database_id) as 数据库名

, t1.resource_associated_entity_id 锁定对象

, t1.request_mode as 等待者请求的锁定模式

, t1.request_session_id 等待者SID

, t2.wait_duration_ms 等待时间

, (select TEXT

from sys.dm_exec_requests r

cross apply

sys.dm_exec_sql_text(r.sql_handle)

where r.session_id = t1.request_session_id) as 等待者要执行的SQL

, t2.blocking_session_id [锁定者SID]

, (select TEXT

from sys.sysprocesses p

cross apply

sys.dm_exec_sql_text(p.sql_handle)

where p.spid = t2.blocking_session_id

) 锁定者执行语句

from sys.dm_tran_locks t1,

sys.dm_os_waiting_tasks t2

where t1.lock_owner_address = t2.resource_address