question

Raj More avatar image
Raj More asked

Transaction Isolation Level vs. NoLock

Is there a difference between the following SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Select * From MyTable Where Date = '10/1/2011' and Select * From MyTable With (NoLock) Where Date = '10/1/2011'
nolock
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
Both commands will work the same, except the isolation level command will apply to all tables in a select, NOLOCK is a hint that runs for only one table. e.g. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM MyTable1 A INNER JOIN MyTable2 B ON A.Col1 = B.Col1 will effectively do the same as SELECT * FROM MyTable1 A WITH (NOLOCK) INNER JOIN MyTable2 B WITH (NOLOCK) ON A.Col1 = B.Col1 You may want to only use the NOLOCK hint on one table, however unlikely that may be. EDIT: The main thing to consider here, is what would you win by using this form of data access? NOLOCK / READ UNCOMMITED does have its dangers (phantom reads being the main issue IMO).
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You still need to worry about bad reads. Page splits can cause data to rearrange. You can get extra rows or miss rows entirely. NOLOCK is absolutely not the magic "run faster" button on SQL Server.
4 Likes 4 ·
As I mentioned, the risk of using NOLOCK/READ UNCOMMITED on "live" data is there. If the data you are reading is not being manipulated you don't need to worry about phantom reads, but then I don't see the need for using those hints/isolation levels.
3 Likes 3 ·
@Grant Fritchey -- top job on beating out Delaney, Randall and Tripp for favorite SQL Server blogger!
2 Likes 2 ·
And Grant Fritchey just happened to discuss Nolock at some length in his recent article at http://www.simple-talk.com/sql/database-administration/grant-fritcheys-sql-server-howlers/ The bottom line is nolock can be great, but you need to use it very cautiously and you can still wind up with faulty data even if the particular rows you are looking for is not not being updated at the moment. Perhaps you could consider a replicated reporting database on another server if you are having locking issues but need fast access to old data?
1 Like 1 ·
I'm reading data for a prior data - all transaction are happening on the current date.
0 Likes 0 ·
Show more comments
firmbyte avatar image
firmbyte answered
In reference to "NOLOCK is a hint that runs for only one table", MSDN says "All lock hints are propagated to all the tables and views that are accessed by the query plan".
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

firmbyte avatar image
firmbyte answered
In reference to "NOLOCK is a hint that runs for only one table", Technet says "All lock hints are propagated to all the tables and views that are accessed by the query plan". http://technet.microsoft.com/en-us/library/ms187373.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.