SQL Server - Dirty Reads Pros & Cons
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.
Why use a READ UNCOMMITTED isolation level?
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 withread 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 trynolock
.
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.
WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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.
Using NOLOCK for reading single static row. Whats the harm?
After deep searches and asking questions to many experts I found out that using NOLOCK hint causes no problem in this scenario, yet its not advised. nothing wrong with NOLOCK but as I use sql2014 I "should" use ISOLATION LEVEL option. Its a method came instead of NOLOCK. For example for huge table selects that cause deadlocks:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM HugeTable;
COMMIT TRANSACTION;
is very handy.
I had HugeTable and a web form that uses sqlAdapter and Radgrid to show this data. Whenever I run this report, though indexes and paging of radgrid is fine, it caused deadlock, which makes sense. I changed select statement of sqlAdapter to above sentence, its perfect now.
best.
Is the NOLOCK (Sql Server hint) bad practice?
With NOLOCK hint, the transaction isolation level for the SELECT
statement is READ UNCOMMITTED
. This means that the query may see dirty and inconsistent data.
This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.
I suggest reading When Snapshot Isolation Helps and When It Hurts - the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.
Related Topics
Calculating SQL Server Row_Number() Over() for a Derived Table
Can Linq to SQL Query an Xml Field Db-Serverside
Limit Ignored in Query with Group_Concat
Doctrine Query - Ignoring Spaces
Hibernate Create Criteria to Join the Same Table Twice - Tried 2 Approach with 2 Difference Error
Dynamic Column in Select Statement Postgres
Hamming Weight/Population Count in T-Sql
Search If Number Is Contained Within an Expression Like: 1-3,5,10-15,20
Query for Searching the Name Alphabetically
Problem with MySQL Insert Max()+1
Oracle - Literal Does Not Match Format String Error
How to Make a JPA Query with Left Outer Join
How to Parse a Varchar Passed to a Stored Procedure in SQL Server
Mod Negative Numbers in SQL Just Like Excel