WITH NOLOCK vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Question

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

User · Answer

NOLOCK is local to the table  or views etc  READ UNCOMMITTED is per session connection   As for guidelines    a random search from StackOverflow and the electric interweb      Is the NOLOCK  Sql Server hint  bad practice  When is it appropriate to use NOLOCK  Get rid of those NOLOCK hints

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   MVCC  Database Isolation Levels

User · Answer

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

[sql] WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005