views:

148

answers:

3

Background: I have a performance-critical query I'd like to run and I don't care about dirty reads.

My question is; If I'm using joins, do I have to specify the NOLOCK hint on those as well?

For instance; is:

SELECT * FROM table 1 a WITH (NOLOCK)
INNER JOIN table2 b WITH (NOLOCK) ON a.ID = b.ID

Equivilent to:

SELECT * FROM table 1 a WITH (NOLOCK)
INNER JOIN table2 b ON a.ID = b.ID

Or will I need to specify the (NOLOCK) hint on the join to ensure I'm not locking the joined table?

+2  A: 

I was pretty sure that you need to specify the NOLOCK for each join in the query. But my experience was limited to SQL Server 2005.

When i looked up MSDN just to confirm, i couldnt find anything definite.The below statements do seem to make me think, that for 2008, your two statements above are equivalent though for 2005 it is not the case.

[SQL Server 2008 R2] All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

[SQL Server 2005] In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

Additionally, point to note - and this applies to both 2005 and 2008

The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint

InSane
@In Sane: Interesting...thanks for that...I'm assuming that I'm doing no harm by including it on the JOINS, even if it's not entirely necessary? The documentation on NOLOCK is pretty sparse as you've mentioned; I had trouble finding anything conclusive myself.
DanP
+3  A: 

Neither. You set the isolation level to READ UNCOMMITTED which is always better than giving individual lock hints. Or, better still, if you care about details like consistency, use snapshot isolation.

Remus Rusanu
@Remus: I'm not sure that I can use READ UNCOMMITTED in my case because I'm accessing the connection through NHibernate to perform a special raw ADO.NET call; can this be specified inline in the query, or will it obey the transaction level present on the NHibernate transaction?
DanP
Wrap the call in `using (TransactionScope scope=new TransactionScope(..., TransactionOptions) {...}` and set the `IsolationLevel` on the options: http://msdn.microsoft.com/en-us/library/system.transactions.transactionoptions.isolationlevel.aspx
Remus Rusanu
@Remus: Unfortunately, transaction management is taken care of at a much higher level than this, so that's also not an option.
DanP
I see. Then to answer your question: NOLOCK is a *table* hint, and as such it applies to the rowset to which is being added (table, view, TVF etc). If you have multiple rowsets joined in a query, each one would need its own NOLOCK hint.
Remus Rusanu
But have you considered snapshot isolation? `ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON;`. The results are spectacular, as all normal read committed reads turn into snapshot reads, lock free yet consistent. The cost is increased `tempdb` load: http://msdn.microsoft.com/en-us/library/ms175492.aspx
Remus Rusanu
@Reumus: Is that method appropriate for exporting data from a live database?
DanP
@Remus: I think I may be attacking this from the wrong angle; I've opened a new question if you'd care to comment: http://stackoverflow.com/questions/3790430/isolated-ado-net-connection-and-transaction-within-a-transactionscope
DanP
About exporting data: definitely no. Using NOLOCK on exports often creates inconsistent exports. I've seen this several times in my experience, where SSIS export jobs were causing mysterious constraint violation failures on the target, or the verifications were failing (check count of exported for instance). All due to the inherent inconsistency of read uncommitted.
Remus Rusanu
@Remus: I was referring to SNAPSHOT; is that a better option for this sort of thing?
DanP
SNAPSHOT is definitely a good candidate. Try it out, if the extra work in tempdb is not an issue in production, then it would solve all your problems.
Remus Rusanu
+3  A: 

I won't address the READ UNCOMMITTED argument, just your original question.

Yes, you need WITH(NOLOCK) on each table of the join. No, your queries are not the same.

Try this exercise. Begin a transaction and insert a row into table1 and table2. Don't commit or rollback the transaction yet. At this point your first query will return successfully and include the uncommitted rows; your second query won't return because table2 doesn't have the WITH(NOLOCK) hint on it.

whatknott
@whatknott: thanks for the concise answer, just what I was looking for in this case.
DanP