CODEWITHSUNDEEP
sql-servert-sqlnolock
Seth
Seth

Reputation: 426

Using NOLOCK on a table which is being joined to itself

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?

Upvotes: 3

Views: 1162

Answers (3)

Alex Beggs
Alex Beggs

Reputation: 1235

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

Upvotes: 1

usr
usr

Reputation: 171178

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.

Upvotes: 0

mgmedick
mgmedick

Reputation: 700

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.

Upvotes: 0

Related Questions