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.