Database Reference
In-Depth Information
Read Uncommitted
Read Uncommitted is the lowest of the four isolation levels, and it allows SELECT statements to read data without
requesting an (S) lock. Since an (S) lock is not requested by a SELECT statement, it neither blocks nor is blocked by the
(X) lock. It allows a SELECT statement to read data while the data is under modification. This kind of data read is called
a dirty read.
Assume you have an application in which the amount of data modification is extremely minimal and that your
application doesn't require much in the way of accuracy from the SELECT statement it issues to read data. In this case,
you can use the Read Uncommitted isolation level to avoid having some other data modification activity block the
SELECT statement.
You can use the following SET statement to configure the isolation level of a database connection to the Read
Uncommitted isolation level:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
You can also achieve this degree of isolation on a query basis using the NOLOCK locking hint.
SELECT *
FROM Production.Product AS p WITH (NOLOCK);
The effect of the locking hint remains applicable for the query and doesn't change the isolation level of the
connection.
The Read Uncommitted isolation level avoids the blocking caused by a SELECT statement, but you should not
use it if the transaction depends on the accuracy of the data read by the SELECT statement or if the transaction cannot
withstand a concurrent change of data by another transaction.
It's important to understand what is meant by a dirty read. Lots of people think this means that, while a field is
being updated from Tusa to Tulsa , a query can still read the previous value or even the updated value, prior to the
commit. Although that is true, much more egregious data problems could occur. Since no locks are placed while
reading the data, indexes may be split. This can result in extra or missing rows of data returned to the query. To be
clear, using Read Uncommitted in any environment where data manipulation as well as data reads are occurring can
result in unanticipated behaviors. The intention of this isolation level is for systems primarily focused on reporting
and business intelligence, not online transaction processing.
Read Committed
The Read Committed isolation level prevents the dirty read caused by the Read Uncommitted isolation level. This
means that (S) locks are requested by the SELECT statements at this isolation level. This is the default isolation level of
SQL Server. If needed, you can change the isolation level of a connection to Read Committed by using the following
SET statement:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
The Read Committed isolation level is good for most cases, but since the (S) lock acquired by the SELECT
statement isn't held until the end of the transaction, it can cause nonrepeatable read or phantom read issues,
as explained in the sections that follow.
The behavior of the Read Committed isolation level can be changed by the READ_COMMITTED_SNAPSHOT database
option. When this is set to ON , row versioning is used by data manipulation transactions. This places an extra load on
tempdb because previous versions of the rows being changed are stored there while the transaction is uncommitted.
This allows other transactions to access data for reads without having to place locks on the data, which can improve
the speed and efficiency of all the queries in the system without resulting in the issues generated by page splits with
NOLOCK or READ UNCOMMITTED .
 
Search WWH ::




Custom Search