Yes, you must use WITH(NOLOCK) on each table of the join. Your queries are not the same though. Try this: Begin a transaction and insert a row into table1 and table2.
Thus, we can say that Nolock reads “Dirty Data” when applied with only Select statement in SQL Server Database. While With (Nolock)do not issue any shared locks and exclusive locks. It is possible with With (Nolock) that, it can read an uncommitted transaction, which can be rolled back at the middle of a read.
The only difference between the two is that the READ UNCOMMITTED isolation level determines the locking mechanism for the entire connection and the NOLOCK table hint determines the locking mechanism for the table that you give the hint to.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With