Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server NOLOCK and joins

People also ask

Do you need Nolock on joins?

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.

What is difference between Nolock and with Nolock in SQL Server?

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.

What is the difference between Nolock and read uncommitted?

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.