Request Sch-M lock on a table

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply