183

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 table1 a WITH (NOLOCK)
INNER JOIN table2 b WITH (NOLOCK) ON a.ID = b.ID

Equivalent to:

SELECT * FROM table1 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?

ChrisF
  • 134,786
  • 31
  • 255
  • 325
DanP
  • 6,310
  • 4
  • 40
  • 68

3 Answers3

201

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.

codeConcussion
  • 12,739
  • 8
  • 49
  • 62
22

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 couldn't 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.

Community
  • 1
  • 1
Jagmag
  • 10,283
  • 1
  • 34
  • 58
  • @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 Sep 24 '10 at 11:01
  • 2
    @InSane: Where did you get this info from? It seems to go against the accepted answer. – Jay Sullivan Dec 23 '13 at 15:53
  • 1
    @notfed - refer technet link http://technet.microsoft.com/en-us/library/ms187373(v=sql.105).aspx - you can change database version at top to compare the same article for different versions of the db – Jagmag Jan 15 '14 at 06:16
  • 3
    The 2005 text talks about VIEWS. So if you do "from myview with (nolock)" then it says the nolock is propagated to all tables and views involved in myview (you could have 10 joins in there). Not sure what the 2008 text exactly mean as it adds "accessed by the query plan" in addition to the views. – Thierry_S Mar 04 '16 at 15:29
11

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
  • 288,378
  • 40
  • 442
  • 569
  • @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 Sep 24 '10 at 11:00
  • 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 Sep 24 '10 at 14:40
  • @Remus: Unfortunately, transaction management is taken care of at a much higher level than this, so that's also not an option. – DanP Sep 24 '10 at 16:02
  • 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 Sep 24 '10 at 17:08
  • 3
    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 Sep 24 '10 at 17:10
  • @Reumus: Is that method appropriate for exporting data from a live database? – DanP Sep 24 '10 at 19:11
  • @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 Sep 24 '10 at 19:45
  • 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 Sep 24 '10 at 19:59
  • @Remus: I was referring to SNAPSHOT; is that a better option for this sort of thing? – DanP Sep 24 '10 at 22:23
  • 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 Sep 24 '10 at 23:12