SQL Server - Dirty Reads Pros & Cons

Question

Why should I or should not use dirty reads:

set transaction isolation level read uncommitted 

in SQL Server?

Answer 1

From MSDN :

When this parameter is set, uncommitted or dirty data can be read; values ​​in the data can be changed, and rows can appear or disappear in the data set until the end of the transaction.

Simply put, when you use this isolation level and execute several queries in the active table as part of a single transaction, there is no guarantee that the information returned to you in different parts of the transaction will remain unchanged, you can request the same data twice within the same transactions and get different results (this can happen when another user updates the same data in the middle of the transaction). Obviously, this can have serious consequences for parts of your application that rely on data integrity.

Answer 2

As a rule, when you need to make significant (or frequently used) queries to busy tables, where a read message can be blocked by locks from idle transactions, but ONLY when you can live with inaccurate data.

As an example, on the gaming website I’ve been working on lately, a short display of some statistics about the latest games appeared, all of this was based on dirty reads, it was more important for us to include and then exclude transactional data, and not (we still knew that in case of any transactions it would be refused), we felt that on average the data would be more accurate.

Answer 3

use it if you want the data to be right away, and it’s not so important if it’s right. should not be used if data is important for proper operation or if you are updating it

Also note the snapshot isolation that was introduced in SQL Server 2005

Answer 4

The thing is, when you want to read data before committing, we can do this with a set transaction isolation level, which will be considered uncommitted, data may or may not change.

We can read the data using the query:

 Select * from table_name with(nolock) 

This applies only to reading an uncommitted isolation level.