SQL Server - Dirty Reads Pros & Cons

Question

Why should I or shouldn't I use dirty reads:

set transaction isolation level read uncommitted

in SQL Server?

Answer 1

Generally when you need to do a sizeable (or frequent) queries to busy tables, where read committed would possibly be blocked by locks from uncommited transactions, but ONLY when you can live with inaccurate data.

As an example, on a gaming web site I worked on recently there was a summary display of some stats about recent games, this was all based on dirty reads, it was more important for us to include then exclude the transactional data not yet committed (we knew anyway that few, if any, transactions would be backed out), we felt that on average the data would be more accurate that way.

Answer 2

use it if you want the data back right away and it is not that important if it is right
do not use if if the data is important to be correct or if you are doing updates with it

Also take a look at snapshot isolation which has been introduced in sql server 2005

Answer 3

From MSDN:

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.

Simply put, when you are using this isolation level, and you are performing multiple queries on an active table as part of one transaction, there is no guarantee that the information returned to you within different parts of the transaction will remain the same. You could query the same data twice within one transaction and get different results (this might happen in the case where a different user was updating the same data in the midst of your transaction). This can obviously have severe ramifications for parts of your application that rely on data integrity.

Answer 4

To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link: http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/

Answer 5

Detailed analysis here: Selects under READ COMMITTED and REPEATABLE READ may return incorrect results

and here: The Case of the Skewed Totals