如何查找被锁定的表名(特定于任何事务)

问题描述：

如果我们希望立即解锁这些表，是否有任何方法可以列出这些被锁定的表并杀死这些事务.

Is there any way to list out the locked tables and to kill the transactions if we want them to be unlocked immediately.

或者对于我正在寻找的上述操作，我们还需要遵循其他术语吗?

Or is there any other terminology do we need to follow for above operation i am seeking for.

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

Any Help or guidance will be appreciated.

答

这将显示使用

This will show all databases with exclusive locks being held (which may include transient ones held at the time this is run), using the sys.dm_tran_locks DMV:

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 =意图共享)请参见锁定模式.

(X = exclusive, S = Shared, IS = Intent Shared) See Lock Modes.

但是最好的方法可能是打开跟踪标志1204和1222:

But probably the best way is to turn on Trace Flags 1204 and 1222:

跟踪标志1204和跟踪标志1222 发生死锁时，跟踪标志1204 和跟踪标志1222返回信息在SQL Server中捕获的 2005错误日志.跟踪标志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.

参考:检测并结束死锁

此外，运行sp_who2并在BlkBy(阻止者)列中查找条目；遵循这些步骤，直到到达死锁链的头.那是负责的进程标识符(或PID).

Also, run sp_who2 and look for entries in the BlkBy (Blocked By) column; follow these until you get to the head of the deadlock chain. That is the process identifier (or PID) responsible.

要获取特定进程背后正在运行的sql，可以运行:

To get what sql is running behind a specific process you can run:

dbcc inputbuffer (@pid)

并使用该PID杀死进程(谨慎行事，风险自负):

and use that PID to kill the Process (with prudence and at your own risk):

kill @pid

查看谁是活跃的? v10.00:轻松进行DMV监视

也请阅读阻止不是死锁(以区分这两种情况)

Also read Blocking is not Deadlocking (to distinguish the two scenarios)

如何查找被锁定的表名(特定于任何事务)

相关推荐