SQL Server LockMatchID 命令

它是一种内部方法，从名字上看大概是用来锁定一个基于对象的对象，object_id并确保执行用户有权对该对象进行进一步的更改。需要这种锁定来执行添加/删除属性、隐藏列等操作，并确保对象在此期间不能被其他人删除或更改（一般人倾向于通过启动事务和锁定来执行此操作）表通过一些间接的方法，因为没有LOCK TABLE命令——但显然有内部管道来帮助系统操作）。

如果您查看使用它的任何过程（有一个大列表）：

SELECT N'EXEC sp_helptext ''sys.' + QUOTENAME(name) + N''';' 
FROM sys.all_objects 
WHERE [schema_id] = 4
  AND LOWER(OBJECT_DEFINITION([object_id])) LIKE N'%lockmatchid%';

您会看到前面的评论，例如：

-- Lock the table schema and check permissions

-- Share lock default so it cannot be dropped

-- Since binding a default is a schema change, update schema count
--  for the object in the sysobjects table.

-- Ex-lock and check permission

-- Share Lock rule so that it cannot be dropped

-- Check that table and column exist

-- RESOLVE OBJECT NAME

-- Get exclusive object lock upfront so subsequent %%ColumnEx can proceed

-- Acquire sch-M lock up-front on the published object 

-- LOCK PROC & CHECK PERMISSION --

-- LOCK, CHECK PERMISSION, BUMP SCHEMA FOR RECOMPILE  --

-- obtain owner-qual object name

-- Re-acquire schema mod lock to make the code more resilient to changes
-- although this must have been done inside sp_Mrepl_schema

-- LOCK TABLE, CHECK STANDARD TABLE-DDL PERMISSIONS

-- Since binding a default is a schema change,
--  update schema count for the object in the sysobjects table.

-- Update schema count for the object in the sysobjects table.

-- Verify that table exists

（人们说注释代码是浪费时间。）

似乎也有LockExclusiveMatchID方法，尽管Exclusive后来似乎已将参数添加到 中LockMatchID，这使得独占方法已过时。

不确定您需要对此更加具体 - 如果您想追踪这些事件发生率高的地方，您需要查看外部调用，而不是这个特定的声明。例如，您是否有一个应用程序经常重命名对象、sp_autostats直接调用、更改复制或您可以从上面生成的列表中推断出的任何其他内容？您必须弄清楚调用了哪些过程以帮助确定原因。而你将如何处理它（如果你甚至可以确定存在“问题”）将与LockMatchID具体没有任何关系。

我遇到了同样的问题，我看到在同样的 3 分钟内服务器上发生了由相同语句生成的很多锁和死锁。

通过死锁报告，我发现死锁发生在我的进程中，在我的案例中是这个系统进程：Proc [Database Id = 32767 Object Id = -993696157]

select DB_NAME(32767)--NULL
select OBJECT_NAME(-993696157)--sp_MSactivate_auto_sub

根据这篇文章，它与复制系统任务有关。

在我的案例中，这是来自 blocked-process-report\blocking-process\process\executionStack 的典型调用堆栈：

      <executionStack>
        <frame line="30" stmtstart="1570" stmtend="1796" sqlhandle="0x0300ff7f66f82acdcaae2d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="917" stmtstart="67266" stmtend="67522" sqlhandle="0x0300ff7f48dddfc2a8b02d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="52" stmtstart="3496" stmtend="5162" sqlhandle="0x0300ff7f5eea5ef7d25b2d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="92" stmtstart="4236" stmtend="4768" sqlhandle="0x0300ff7f6366c5c416b12d0054a9000001000000000000000000000000000000000000000000000000000000" />
      </executionStack>

如果我用这个查询解码它：

select
cast(
             (select SUBSTRING(txt.text,(ISNULL(T.x.value('./@stmtstart', 'int'), 0) / 2) + 1,
                           ((CASE ISNULL(T.x.value('./@stmtend', 'int'), -1)
                                  WHEN -1 THEN DATALENGTH(txt.text)
                                  ELSE T.x.value('./@stmtend', 'int')
                              END - ISNULL(T.x.value('./@stmtstart', 'int'), 0)) / 2) + 1) + CHAR(13) AS statement_txt
                              ,T.x.value('./@line', 'int') as line
                       from @bpReportXml.nodes('//blocking-process/process/executionStack/frame') AS T(x)
                       cross apply sys.dm_exec_sql_text(T.x.value('xs:hexBinary(substring((./@sqlhandle), 3))', 'varbinary(max)')) AS txt
                       for XML path('')) as xml) AS frame_blocking_process_xml

然后我得到这个结果：

<statement_txt>EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)

</statement_txt>
<line>30</line>
<statement_txt>exec sys.sp_MSsetfilteredstatus @tabid

                    -- clear nonsqlsub status for this article.


</statement_txt>
<line>917</line>
<statement_txt>EXEC @retcode = @cmd
                    @publication,
                    @article,
                    @subscriber,
                    @status,
                    @previous_status,
                    @destination_db,
                    @frequency_type,
                    @frequency_interval,
                    @frequency_relative_interval,
                    @frequency_recurrence_factor,
                    @frequency_subday,
                    @frequency_subday_interval,
                    @active_start_time_of_day,
                    @active_end_time_of_day,
                    @active_start_date,
                    @active_end_date,
                    @optional_command_line,
                    @distribution_jobid OUTPUT,
                    @from_auto_sync,
                    @ignore_distributor,
                    -- Agent offload
                    @offloadagent,
                    @offloadserver,
                    @dts_package_name,
                    @dts_package_password,
                    @dts_package_location,
                    @skipobjectactivation,
                    @distribution_job_name,
                    @publisher,
                    @publisher_type
                    ,@ignore_distributor_failure


</statement_txt>
<line>52</line>
<statement_txt>EXECUTE @retcode =   sys.sp_changesubstatus 
                        @publication = @publication,
                        @article = @article,
                        @status = @status,
                        @from_auto_sync = @from_auto_sync,
                        @skipobjectactivation = @skipobjectactivation,
                        @publisher = @publisher


</statement_txt>
<line>92</line>