sql-server add tag
arthur (imported from SE)
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][1] 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.

  [1]: https://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes/3662820#3662820
Top Answer
Paul White (imported from SE)
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][1]. 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.

  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.