3

I'm working with an awful view which internally joins many, many tables together, some of which are the same table.

I'm wondering, when a table is being joined to itself, how is the NOLOCK hint interpreted if it's on one of the joins and not the other? Is the NOLOCK still in effect on the table, or is the table locked altogether if NOLOCK is not included on one of the joins of the same table?

For example (this is pseduo-code, assume that there are valid JOIN ON conditions):

SELECT *
FROM Table1 t1 (NOLOCK)
JOIN Table2 t2 (NOLOCK)
JOIN Table2_Table2 tt (NOLOCK)
JOIN Table2 t22 (NOLOCK)
JOIN Table1 t11

Does Table1 get locked or stay NOLOCKed?

Alex Beggs
  • 1,187
  • 2
  • 11
  • 17
Seth
  • 342
  • 1
  • 4
  • 14
  • The answer is it depends: read & understand http://www.linkedin.com/groups/SQL-Server-NOLOCK-question-66097.S.125721971 . The lock happens on a row level, so if your joins are accessing the same rows with the same filter criteria, you can A. easily optimize the query yourself, why use a language incorrectly? B. The nolock applies to the table row, so when you access it again, it's still nolock. However, say the table is first called without the nolock, it'll lock and nolock won't matter when it's called later. Also, in sql 2008+ there is no reason to use nolock anymore. – RandomUs1r Aug 26 '14 at 22:45

3 Answers3

1

Yes it does get locked by the last Table1 t11 call. Each table locking hint is applied to the specific reference. If you apply it to only one of the table references that is only for that reference and the others will have their own individual locking settings. You can test this using BEGIN TRANSACTION and execute two different queries.

Query 1 (locks the table) Intentionally commenting out the COMMIT TRANSACTION

BEGIN TRANSACTION
SELECT *
FROM Table1 WITH (TABLOCK)
-- COMMIT TRANSACTION

Since COMMIT TRANSACTION was commented out, the transaction is not closed and will still hold the lock. When the second query is run the first lock will still apply on the table from the first query.

Query 2 (this query will hang because of the first lock will block on Table1 t11)

BEGIN TRANSACTION
SELECT *
FROM Table1 t1 (NOLOCK)
JOIN Table2 t2 (NOLOCK)
JOIN Table2_Table2 tt (NOLOCK)
JOIN Table2 t22 (NOLOCK)
JOIN Table1 t11
COMMIT TRANSACTION
Alex Beggs
  • 1,187
  • 2
  • 11
  • 17
  • I like the example you gave, but I think it would be more helpful if you had Query 2 execute first and then Query 1, since of course Query 1 will lock `Table1`. – Seth Aug 27 '14 at 15:57
  • @Seth that would create a race condition. You could have the case that Query 2 finished before Query 1 attempted to obtain the lock on `Table1` and this would not show the lock contention since it would release it with the `COMMIT TRANSACTION` – Alex Beggs Feb 03 '15 at 18:34
0

I would guess that not using nolock is going to result in some type of locking, regardless if it is joined elsewhere in the query with nolock. So it would result in a row lock likely, so put nolock next to the join that is missing it.

mgmedick
  • 686
  • 7
  • 23
0

In very simplified terms, think of it like this: Each of the tables you reference in a query results in a physical execution plan operator accessing that table. Table hints apply to that operator. This means that you can have mixed locking hints for the same table. The locking behavior that you request is applied to those rows that this particular operator happens to read. The respective operator might scan a table, or scan a range of rows, or read a single row. Whatever it is, it is performed under the specified locking options.

Look at the execution plan for your query to find the individual operators.

usr
  • 168,620
  • 35
  • 240
  • 369