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.