Request Sch-M lock on a table

Question

I have access to a database in read uncommitted mode and need to do maintenance on a table in an operative environment. I need to acquire an exclusive lock on a table, do a job, then release the lock.

During this maintenance even read queries should not get uncommitted data back.

This question and answer looks promising. I do:

begin transaction;
SELECT TOP (1) 1 FROM a WITH (TABLOCK);

but then, a SIX lock is acquired on table a. This however does not lock the table from select queries. How can I acquire Sch-M lock on the table? At most I can get the Sch-S lock with:

SELECT TOP (1) * FROM a WITH (TABLOCK);

this still does not block select queries on the table.

Answer 1

There are any number of ways to do literally what you ask, but I would generally advise against using any of them. In most cases, all you need do is prevent any changes to the table while your process completes. This can be done with a TABLOCKX hint inside the transaction:

SELECT TOP (0) NULL FROM Production.Product AS P WITH (TABLOCKX);

You could also temporarily revoke permissions, or use an application lock. Other alternatives exist; the best approach depends on the details.

If you really want to acquire Sch-M (a system lock), one way is to call sp_compile e.g.:

-- AdventureWorks sample database
BEGIN TRANSACTION;
EXECUTE sys.sp_recompile @objname = N'Production.Product';
SELECT * FROM sys.dm_tran_locks AS DTL WHERE DTL.request_session_id = @@SPID;
ROLLBACK TRANSACTION;

If the entire contents of the table are invalidated by whatever maintenance you are doing, you'll naturally get a Sch-M lock when you perform a TRUNCATE TABLE. Again, what is best depends on details that have not been provided.