Microsoft® SQL Server® 2012 Bible

1074

Part VIII: Performance Tuning and Optimization

BEGIN TRANSACTION

UPDATE HumanResources.Department

SET Name = 'Transaction Fault'

WHERE DepartmentID = 1

In a separate Management Studio window, as shown in Figure 47-1, execute another trans-

action in its own connection window. This transaction sets its transaction isolation level

to permit dirty reads. Only the second transaction needs to be set to read uncommitted for

transaction 2 to experience a dirty read:

-- Transaction 2

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT Name

FROM HumanResources.Department

WHERE DepartmentID = 1;

Result:

Name

----------------------

Transaction Fault

Transaction 1 hasn’t yet committed the transaction, but transaction 2 read “Transaction

Fault.” That’s a dirty read side effect of the READ UNCOMMITTED isolation level..

To fi nish the task, the fi rst transaction rolls back that transaction:

-- Transaction 1

ROLLBACK TRANSACTION

There are other issues about reading uncommitted data due to the way the SQL engine opti-

mizes such a read that can result in your query reading the same data more than once.

Level 2 — Read Committed

SQL Server’s default transaction isolation level, read committed, ensures that one transac-

tion cannot read data that another transaction has altered but not yet committed. This is

accomplished through shared locks, which are taken when data is read and released after-

wards. Shared locks cannot be taken on data that is currently being modifi ed. This pre-

vents dirty reads, but it is susceptible to other types of isolation level side effects. These

side effects (described in detail later in this chapter), although undesirable, require more

stringent isolation levels to protect against them, which can drastically inhibit

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

http://www.it-ebooks.info