Microsoft® SQL Server® 2012 Bible

1082

Part VIII: Performance Tuning and Optimization

Result:

DeptID Name

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

18 ABC Dept

1 ABC Test

12 Document Control

1 Engineering

16 Executive

14 Facilities and Maintenance

10 Finance

Selecting the range after the transaction 2 is committed, and transaction 1 has made its

updates, reveals the inserted and updated rows added to the range. And the Amazing FX

Dept disappeared.

Concurrency and serialized isolation levels do not play well together because to get the pro-

tection needed for the serialized isolation level requires additional locks. And worse, those

locks have to be on key ranges to prevent someone else inserting rows. If you don’t have the

correct indexes, the only way SQL can prevent phantoms is to lock the table. Locking the

table is obviously not good for concurrency. For this reason, if you need to use serialized

transactions, you must make sure you have the correct indexes to avoid table locks occurring.

Snapshot Isolations

Traditionally, writers block readers, and readers block writers, but version-based isolations

are a completely different twist. When version-based isolations are enabled if a transaction

modifi es (irrespective of the isolation level) data, a version of the data as it was before the

modifi cation is stored. This allows other transactions to read the original version of the

data even while the original transaction is in an uncommitted state.

So snapshot isolation eliminates writer versus reader contention. Nevertheless, contention

isn’t completely gone — you still have writers confl icting with writers. If a second writer

attempts to update a resource that’s already being updated, the second resource is blocked.

There are two version based isolations: snapshot isolation and read committed snapshot

isolation.

■ Snapshot isolation operates like serializable isolation without the locking and

blocking issues. The same SELECT within a transaction can see the same version of

the before image of the data.

■ (^) Read Committed Snapshot Isolation sees any committed data similar to SQL Server’s
default isolation level of read committed. Most important, it doesn’t place any
shared locks on the data read.
c47.indd 1082c47.indd 1082 7/31/2012 10:23:32 AM7/31/2012 10:23:32 AM
http://www.it-ebooks.info