Why use a READ UNCOMMITTED isolation level

Question

In plain English  what are the disadvantages and advantages of using  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   in a query for  NET applications and reporting services applications

User · Answer

My favorite use case for read uncommited is to debug something that is happening inside a transaction   Start your software under a debugger  while you are stepping through the lines of code  it opens a transaction and modifies your database  While the code is stopped  you can open a query analyzer  set on the read uncommited isolation level and make queries to see what is going on    You also can use it to see if long running procedures are stuck or correctly updating your database    It is great if your company loves to make overly complex stored procedures

User · Answer

This will give you dirty reads  and show you transactions that s not committed yet  That is the most obvious answer  I don t think its a good idea to use this just to speed up your reads  There is other ways of doing that if you use a good database design   Its also interesting to note whats not happening  READ UNCOMMITTED does not only ignore other table locks  It s also not causing any locks in its own   Consider you are generating a large report  or you are migrating data out of your database using a large and possibly complex SELECT statement  This will cause a shared lock that s may be escalated to a shared table lock for the duration of your transaction  Other transactions may read from the table  but updates are impossible  This may be a bad idea if its a production database since the production may stop completely   If you are using READ UNCOMMITTED you will not set a shared lock on the table  You may get the result from some new transactions or you may not depending where it the table the data were inserted and how long your SELECT transaction have read  You may also get the same data twice if for example a page split occurs  the data will be copied to another location in the data file    So  if its very important for you that data can be inserted while doing your SELECT  READ UNCOMMITTED may make sense  You have to consider that your report may contain some errors  but if its based on millions of rows and only a few of them are updated while selecting the result this may be  good enough   Your transaction may also fail all together since the uniqueness of a row may not be guaranteed   A better way altogether may be to use SNAPSHOT ISOLATION LEVEL but your applications may need some adjustments to use this  One example of this is if your application takes an exclusive lock on a row to prevent others from reading it and go into edit mode in the UI  SNAPSHOT ISOLATION LEVEL does also come with a considerable performance penalty  especially on disk   But you may overcome that by throwing hardware on the problem      You may also consider restoring a backup of the database to use for reporting or loading data into a data warehouse

User · Answer

This can be useful to see the progress of long insert queries  make any rough estimates  like COUNT    or rough SUM      etc   In other words  the results the dirty read queries return are fine as long as you treat them as estimates and don t make any critical decisions based upon them

User · Answer

When is it ok to use READ UNCOMMITTED   Rule of thumb  Good  Big aggregate reports showing constantly changing totals   Risky  Nearly everything else   The good news is that the majority of read-only reports fall in that Good category   More detail     Ok to use it    Nearly all user-facing aggregate reports for current  non-static data e g  Year to date sales  It risks a margin of error  maybe  lt  0 1   which is much lower than other uncertainty factors such as inputting error or just the randomness of when exactly data gets recorded minute to minute    That covers probably the majority of what an Business Intelligence department would do in  say  SSRS  The exception of course  is anything with   signs in front of it  Many people account for money with much more zeal than applied to the related core metrics required to service the customer and generate that money   I blame accountants    When risky   Any report that goes down to the detail level  If that detail is required it usually implies that every row will be relevant to a decision  In fact  if you can t pull a small subset without blocking it might be for the good reason that it s being currently edited  Historical data  It rarely makes a practical difference but whereas users understand constantly changing data can t be perfect  they don t feel the same about static data  Dirty reads won t hurt here but double reads can occasionally be  Seeing as you shouldn t have blocks on static data anyway  why risk it  Nearly anything that feeds an application which also has write capabilities    When even the OK scenario is not OK    Are any applications or update processes making use of big single transactions  Ones which remove then re-insert a lot of records you re reporting on  In that case you really can t use NOLOCK on those tables for anything

User · Answer

The advantage is that it can be faster in some situations  The disadvantage is the result can be wrong  data which hasn t been committed yet could be returned  and there is no guarantee that the result is repeatable   If you care about accuracy  don t use this   More information is on MSDN      Implements dirty read  or isolation level 0 locking  which means that no shared locks are issued and no exclusive locks are honored  When this option is set  it is possible to read uncommitted or dirty data  values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction  This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction  This is the least restrictive of the four isolation levels

User · Answer

This isolation level allows dirty reads  One transaction may see uncommitted changes made by some other transaction   To maintain the highest level of isolation  a DBMS usually acquires locks on data  which may result in a loss of concurrency and a high locking overhead  This isolation level relaxes this property   You may want to check out the Wikipedia article on READ UNCOMMITTED for a few examples and further reading     You may also be interested in checking out Jeff Atwood s blog article on how he and his team tackled a deadlock issue in the early days of Stack Overflow  According to Jeff      But is nolock dangerous  Could you end   up reading invalid data with read uncommitted on  Yes  in theory  You ll   find no shortage of database   architecture astronauts who start   dropping ACID science on you  and all   but pull the building fire alarm when   you tell them you want to try nolock    It s true  the theory is scary  But   here s what I think   In theory there   is no difference between theory and   practice  In practice there is        I would never recommend using nolock   as a general  good for what ails you    snake oil fix for any database   deadlocking problems you may have  You   should try to diagnose the source of   the problem first       But in practice adding nolock to queries that you absolutely know are simple  straightforward read-only affairs never seems to lead to problems    As long as you know what you re doing    One alternative to the READ UNCOMMITTED level that you may want to consider is the READ COMMITTED SNAPSHOT  Quoting Jeff again      Snapshots rely on an entirely new data change tracking method     more than just a slight logical change  it requires the server to handle the data physically differently  Once this new data change tracking method is enabled  it creates a copy  or snapshot of every data change  By reading these snapshots rather than live data at times of contention  Shared Locks are no longer needed on reads  and overall database performance may increase

User · Answer

It can be used for a simple table  for example in an insert-only audit table  where there is no update to existing row  and no fk to other table  The insert is a simple insert  which has no or little chance of rollback

User · Answer

Regarding reporting  we use it on all of our reporting queries to prevent a query from bogging down databases  We can do that because we re pulling historical data  not up-to-the-microsecond data

User · Answer

Use READ UNCOMMITTED in situation where source is highly unlikely to change    When reading historical data  e g some deployment logs that happened two days ago  When reading metadata again  e g  metadata based application    Don t use READ UNCOMMITTED when you know souce may change during fetch operation

User · Answer

I always use READ UNCOMMITTED now  It s fast with the least issues  When using other isolations you will almost always come across some Blocking issues   As long as you use Auto Increment fields and pay a little more attention to inserts then your fine  and you can say goodbye to blocking issues    You can make errors with READ UNCOMMITED but to be honest  it is very easy make sure your inserts are full proof  Inserts Updates which use the results from a select are only thing you need to watch out for   Use READ COMMITTED here  or ensure that dirty reads aren t going to cause a problem   So go the Dirty Reads  Specially for big reports   your software will run smoother

[sql-server] Why use a READ UNCOMMITTED isolation level?

Examples related to sql-server

Examples related to tsql

Examples related to isolation-level