TSQL and Table Lock on one row So we have a table that is being used for concurrent access across a number of threads and processes. For certain actions, we want to get an exclusive table lock on the entire table, blocking all access until the action is complete.

Previously, one of our developers did this:

SELECT 1 FROM [TableName] (TABLOCKX);

This worked just fine while our table was small, but as it grew larger became a problem.

After doing some research of this query it seems that it was running once for each row in the table (what SELECT 1 would do, naturally) when really what we wanted was to just select one row, then get our table lock and get out as quickly as possible. I then proposed that we change the query to:

SELECT TOP 1 1 FROM [TableName] (TABLOCKX);

We tested this and it seems to work. The execution plan also recognizes that this will only touch 1 row (whereas the previous one touched all rows).

The question that I have is: Does this work the way that I am thinking? Will this block all access to the table for all operations (including NOLOCK operations). Is there a better way to do this?

Note: The reason that we're doing it this way is that we have a fairly complex .NET library in which we are building some queries on the fly. Because we want them to be modular, we want to make sure that devs don't have to modify or duplicate the queries themselves and can simply note at the beginning of the transaction that they are going to be doing updates to the database. Thereby we can make a single .LockTables(X) which will lock the right tables for the corresponding group.
Any queries that read the table WITH (NOLOCK) or with READ UNCOMMITTED isolation level will proceed despite your WITH (TABLOCKX).

More info about lock modes can be found here:
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

Personally I'd say that if you have queries reading from the table WITH (NOLOCK) then they should fully anticipate that they will get bad results so blocking those reads wouldn't be a priority. If your those queries need to return data that is actually correct then they should not use NOLOCK. https://sqlstudies.com/2015/03/18/why-not-nolock/