Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 179

the same drawbacks of read committed regarding non-repeatable reads and the like, but it doesn't
have the same blocking problems as read committed. When another transaction requests locked
data using this isolation level, row versioning can provide a copy of it. However, the older versions
of these rows are released when the transaction is over, thereby allowing more side-effects than are
possible in the snapshot isolation level.
This last isolation level cannot be set using the SET TRANSACTION ISOLATION LEVEL command; it
can only be set using the following:
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON;
SUMMARY
In this chapter you looked at how locking protects your transactions and why it is important. You
have seen what can happen if concurrency is ignored completely, and the various ways that these
negative consequences can be prevented. You've also seen how you can determine what is going on
with the locks in your system, and you should now understand the types of things that can cause the
various types of locks to be acquired.
With a good understanding of locks, a database administrator should be able to i nd strategies
for minimizing their impact. As you use the information in this chapter to investigate the locking
patterns in your database, you should be able to get a good picture of the locking that is happening
within your system, and then devise strategies using isolation levels and even perhaps hints (which
have been deliberately avoided in this chapter, as they should only be a last resort) to control what's
going on.
Finally, to return to the anecdote from the beginning of the chapter, Tommy Cooper's magic should
be celebrated and enjoyed. It shouldn't cause you nightmares as you think of the problems you
have with your database environment. Your “handkerchiefs” should only change color if you are
expecting them to do that.