SQL Server LockMatchID Command

by PseudoToad   Last Updated September 11, 2019 16:06 PM - source

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?

Answers 2

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


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

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



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


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

Aaron Bertrand
Aaron Bertrand
September 21, 2015 17:22 PM

I got this error when trying to delete a Local Publication in SQL 2012, from SSMS.

Razvan Zoitanu
Razvan Zoitanu
September 11, 2019 15:27 PM

Related Questions

How to load data to table from SQL Audit File

Updated July 20, 2017 09:06 AM

MS SQL 2008 R2, security and auditing

Updated February 20, 2019 11:06 AM

Too many events while auditing SQL Server

Updated January 22, 2019 14:06 PM

Audit Database Log On/Off for SQL 2008

Updated October 25, 2017 15:06 PM