Microsoft® SQL Server® 2012 Bible

1078

Part VIII: Performance Tuning and Optimization

Best Practice

Repeatable read has a signifi cant concurrency overhead, but it’s perfect for situations in which a

transaction must read the data multiple times, perhaps performing calculations, and guarantees that

no other transaction updates the data during these calculations.

Level 4 — Serializable

This most restrictive isolation level prevents all transaction isolation side effects; dirty

reads, nonrepeatable reads, and phantom rows. Serializable prevents the previously

described side-effects by ensuring that data can be modifi ed in such a way so that it

would be present in a range of rows previously read in a transaction. To ensure this hap-

pens, SQL Server locks a range of rows being read rather than only the rows that have

been read.

This isolation level is useful for databases for which absolute transactional integrity is

more important than performance. Banking, accounting, and high-contention sales data-

bases, such as the stock market, typically use serialized isolation.

Phantom Rows

A phantom row occurs when a transaction’s insert, update, or delete causes different rows

to be returned in another transaction, as shown in Figure 47-9.

FIGURE 47-9

When the rows returned by a select are altered by another transaction, the phenomenon is

called a phantom row.

Isolation

t1 t2

Insert

Commit

Select

New additional rows are returned Select

4 Rows

6 Rows

Beginning with a clean copy of AdventureWorks2012, transaction 2 selects all the rows in a

specifi c range (Name BETWEEN 'A' AND 'G'):

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

http://www.it-ebooks.info