Microsoft® SQL Server® 2012 Bible

1071

Chapter 47: Managing Transactions, Locking, and Blocking

47

SQL Server 2005 introduced two additional row versioning ”SQL Server 2005 introduced row

versioning, “ which enables two levels of optimistic transaction isolation: Snapshot, and

Read Committed Snapshot. All six transaction isolation levels are listed in Table 47-3 and

then detailed in this section.

TABLE 47-3 ANSI-92 Isolation Levels

Isolation Level Table Hint Dirty Read NonRepeatable Read Phantom Row

Reader/

Writer

Blocking

The

Tr an s ac tion

isolation level

is set for the

connection.

Table Hints over-

ride the connec-

tion’s transaction

isolation level.

Seeing

another

transac-

tion’s non-

committed

changes.

The same

query read-

ing differ-

ent data

in a

transaction.

Seeing

additional

rows

selected

by where

clause as a

result of

another

transac-

tion.

A write

transac-

tion blocks

a read

transac-

tion.

Read

Uncommitted

(least

restrictive)

NoLock,

ReadUncommitted

Possible Possible Possible Yes

Read

Committed

(SQL Server

default; mod-

erately

restrictive)

ReadCommitted Prevented Possible Possible Yes

Repeatable

Read

RepeatableRead Prevented Prevented Possible Yes

Serializable

(most

restrictive)

Serializable Prevented Prevented Prevented Yes

Snapshot Prevented Prevented Possible No

Read

Committed

Snapshot

Prevented Possible Possible No

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

http://www.it-ebooks.info