3

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.

Improve this question
2
  • It's a little off topic from the question that was asked, but I'd be curious why you're doing explicit table-level locks, rather than letting SQL Server manage locks implicitly. You can't use locks to lock out NOLOCK statements, so the added background might help get to the best solution (which might not be the answer to your question).
    – AMtwo
    Dec 7, 2016 at 12:05
  • Unfortunately this is due to the nature of our application. We have a bunch of threads and processes accessing the same row and column at the same time, wanting to do non-conflicting updates to part of an XML value. As such, when we're doing a read-then-write, we need to make sure that someone else doing a read occurs after the write happens. We're looking at refactoring to split the complex XML data into separate tables, but unfortunately that's a lot of additional work (therefore expensive), which is why we didn't do it in the first place.
    – Nate Diamond
    Dec 7, 2016 at 18:58

1 Answer 1

Reset to default
1

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/

Improve this answer
2
  • Great, so as long as we control that people are not explicitly specifying (NOLOCK), then it should work as intended, correct?
    – Nate Diamond
    Dec 6, 2016 at 17:09
  • @NateDiamond seems sound, but I have never tried something like that.
    – mendosi
    Dec 6, 2016 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.