Microsoft® SQL Server® 2012 Bible

1073

Chapter 47: Managing Transactions, Locking, and Blocking

47

Alternatively, the transaction isolation level for a single DML statement can be set by using

table-lock hints in the from clause. These can be used to override the current connection

transaction isolation level and apply the hint on a per table basis. For example, in the next

code sample, the Department table is actually accessed using a read uncommitted transac-

tion isolation level, not the connection’s read committed transaction isolation level:

SET TRANSACTION ISOLATION LEVEL

REPEATABLE READ;

SELECT Name

FROM HumanResources.Department WITH (NOLOCK)

WHERE DepartmentID = 1;

Level 1 — Read Uncommitted and the Dirty Read

The least stringent level of isolation, Read Uncommitted, does not take shared locks when

reading data. (This applies ONLY when reading data.) As no shared locks are required, data

x currently modifi ed inside a transaction can be read in this isolation level. When this

occurs, the data is said to be “dirty” because it has not yet been committed.

Figure 47-7 shows this type of dirty read.

FIGURE 47-7

A dirty read occurs when transaction 2 can see transaction 1’s uncommitted changes.

t2

Select

Isolation

t1

Update

Commit

To demonstrate the Read Uncommitted Transaction Isolation level and the dirty read, it

allows the following code to use two connections, creating two transactions. The second

transaction sees the fi rst transaction’s update before that update is committed:

--Transaction 1

USE AdventureWorks2012

c47.indd 1073c47.indd 1073 7/31/2012 10:23:31 AM7/31/2012 10:23:31 AM

http://www.it-ebooks.info