sql - SQL Server 中的负 SPID？

Question

我正在尝试查找并终止已在其中一个表上设置锁定并阻止许多其他查询执行的 session 。为此，我使用以下脚本:

SELECT REQUEST_MODE, REQUEST_TYPE, REQUEST_SESSION_ID
FROM sys.dm_tran_locks
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_ID('System'))

我得到的结果集如下:

运行 EXEC sp_who2 不会返回任何带有负 SPID 的行。如何找到锁定我的表的 session ？

Answer 1

sys.dm_tran_locks , REQUEST_SESSION_ID:

A value of -2 indicates that the request belongs to an orphaned distributed transaction.

因此，此处不再存在实际的 session ID。阅读评论应该会带您到 KILL :

Use KILL UOW to terminate orphaned distributed transactions. These transactions are not associated with any real session ID, but instead are associated artificially with session ID = '-2'. This session ID makes it easier to identify orphaned transactions by querying the session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.