Microsoft® SQL Server® 2012 Bible

1085

Chapter 47: Managing Transactions, Locking, and Blocking

47

Result:

LastName

--------

Sánchez

Opening up a third or fourth transaction, they would all still see the original value, The

Bald Knight.

Even after the second transaction committed the change, the fi rst transaction would still

see the original value “Sánchez” This is the same behavior as the serializable isolation, but

there is not the blocking that there was with serializable isolation. Any new transactions

would see update value 'Chapman'.

Using Read Committed Snapshot Isolation

Read committed snapshot isolation is enabled using a similar syntax:

ALTER DATABASE AdventureWorks2012

SET READ_COMMITTED_SNAPSHOT ON

Like snapshot isolation, read committed snapshot isolation uses row versioning to stave off

locking and blocking issues. In the previous example, transaction 1 would see transaction

2’s update after it was committed.

The difference to snapshot isolation is that you don’t specify a new isolation level. This

just changes the behavior of the standard read committed isolation level. This means you

shouldn’t need to change your application to benefi t from it.

Handling Write Conflicts

Transactions that write to the data within a snapshot isolation can be blocked by a previ-

ous uncommitted write transaction. This blocking won’t cause the new transaction to wait;

instead, it generates an error. Be sure to use try...catch to handle these errors, wait a

split second, and try again.

Using Locking Hints

Locking hints enable you to make minute adjustments in the locking strategy. Although the

isolation level affects the entire connection, locking hints are specifi c to one table within

one query (see Table 47-5). The with (locking hint) option is placed after the table in

the from clause of the query. You can specify multiple locking hints by separating them

with commas.

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

http://www.it-ebooks.info