sql-server – 如何查找锁定的表名(特定于任何事务)

或者我们需要遵循的任何其他术语,我正在寻找上述操作.

任何帮助或指导将不胜感激.

解决方法

select d.*,l.* from sys.dm_tran_locks l
join sys.databases d on l.resource_database_id = d.database_id 
where l.request_mode = 'X'

(X =独占,S =共享,IS =意图共享)见Lock Modes.

但可能最好的方法是打开Trace Flags 1204和1222：

Trace Flag 1204 and Trace Flag 1222
When deadlocks occur,trace flag 1204
and trace flag 1222 return information
that is captured in the SQL Server
2005 error log. Trace flag 1204
reports deadlock information formatted
by each node involved in the deadlock.
Trace flag 1222 formats deadlock
information,first by processes and
then by resources. It is possible to
enable both trace flags to obtain two
representations of the same deadlock
event.

参考：Detecting and Ending Deadlocks

另外,运行sp_who2并查找BlkBy(Blocked By)列中的条目;按照这些,直到你到达死锁链的头部.这是负责的进程标识符(或PID).

要获取在特定进程后面运行的sql,您可以运行：

dbcc inputbuffer (@pid)

并使用该PID来杀死进程(谨慎并且风险自负)：

kill @pid

查看Who is Active? v10.00: DMV Monitoring Made Easy

另请阅读Blocking is not Deadlocking(区分两种情况)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!