Could someone give me some guidance on when I should use WITH (NOLOCK) as opposed to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
What are the pros/cons of each? Are there any unintended consequences you've run into using one as opposed to the other?
This question is related to
sql
sql-server
sql-server-2005
They are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the connection, so if you only want a nolock on one or two tables use that; otherwise use the other.
Both will give you dirty reads. If you are okay with that, then use them. If you can't have dirty reads, then consider snapshot or serializable hints instead.
As you have to use WITH (NOLOCK) for each table it might be annoying to write it in every FROM or JOIN clause. However it has a reason why it is called a "dirty" read. So you really should know when you do one, and not set it as default for the session scope. Why?
Forgetting a WITH (NOLOCK) might not affect your program in a very dramatic way, however doing a dirty read where you do not want one can make the difference in certain circumstances.
So use WITH (NOLOCK) if the current data selected is allowed to be incorrect, as it might be rolled back later. This is mostly used when you want to increase performance, and the requirements on your application context allow it to take the risk that inconsistent data is being displayed. However you or someone in charge has to weigh up pros and cons of the decision of using WITH (NOLOCK).
You cannot use Set Transaction Isolation Level Read Uncommitted in a View (you can only have one script in there in fact), so you would have to use (nolock) if dirty rows should be included.
To my knowledge the only difference is the scope of the effects as Strommy said. NOLOCK hint on a table and the READ UNCOMMITTED on the session.
As to problems that can occur, it's all about consistency. If you care then be aware that you could get what is called dirty reads which could influence other data being manipulated on incorrect information.
I personally don't think I have seen any problems from this but that may be more due to how I use nolock. You need to be aware that there are scenarios where it will be OK to use. Scenarios where you are mostly adding new data to a table but have another process that comes in behind to check for a data scenario. That will probably be OK since the major flow doesn't include going back and updating rows during a read.
Also I believe that these days you should look into Multi-version Concurrency Control. I believe they added it in 2005 and it helps stop the writers from blocking readers by giving readers a snapshot of the database to use. I'll include a link and leave further research to the reader:
As for guidelines... a random search from StackOverflow and the electric interweb...
WITH (NOLOCK) is a hint on a table level. Setting the transaction isolation level to READ_UNCOMMITTED with affect the connection. The difference is in terms of scope. See READUNCOMMITTED and NOLOCK in the SQL Server documentation here:
http://technet.microsoft.com/en-us/library/ms187373.aspx
For TRANSACTION ISOLATION LEVEL: http://technet.microsoft.com/en-us/library/ms173763.aspx
Source: Stackoverflow.com