SQL Server LockMatchID Command

Question

I am seeing calls like those shown below in my SQL Server Audit logs but have no idea what it means. A google search turned up a possible relationship with compiles/recompiles. The line (or similar lines) can also be found inside several SQL Server internal sprocs.

  EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)

Does anyone have an understanding of this process?

Answer 1

It's an internal method, presumably - from the name - used to lock an object based on its object_id and ensure that the executing user has the rights to make further changes to the object. This locking is required to do things like adding/dropping properties, hidden columns, etc., and to ensure that the object can't be dropped or altered by others in the meantime (mere mortals tend to do this by starting a transaction and locking the table through some indirect method, since there is no LOCK TABLE command - but clearly there is internal plumbing to help with system operations).

If you look at any of the procedures where this is used (there is a large list):

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%';

You'll see preceding comments like:

-- 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

(And people say commenting code is a waste of time.)

There also seems to be LockExclusiveMatchID method, though the Exclusive argument seems to have later been added to LockMatchID, which made the exclusive method obsolete.

Not sure how much more specific you need to this to be - if you want to track down where this a high occurrence of these, you need to look at the outer calls, not this specific statement. For example, do you have an application that is renaming objects a lot, or calling sp_autostats directly, changing replication, or anything else you can infer from the list generated above? You'll have to figure out which procedures are getting called to help determine the why. And what you'll do about it (if you can even determine that there is a "problem") won't have anything to do with LockMatchID specifically.

Answer 2

I faced the same issue and I saw a lot of locks generated by same statements and deadlocks happened on server during the same 3 minutes.

Via deadlock reports I figured out that deadlocks happened with my processes and this system process in my case: Proc [Database Id = 32767 Object Id = -993696157]

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

According to this article it is related to replication system task.

This is a typical callstack from blocked-process-report\blocking-process\process\executionStack in my case:

      <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>

If I decode it with this query:

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

Then I get this result:

<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>