1095
Chapter 47: Managing Transactions, Locking, and Blocking
47
SQL Server 2012 Distributed Replay
New in SQL Server 2012 is Distributed Replay. This feature can be used on multiple computers to replay
a SQL Profi ler trace to simulate a mission-critical workload. This is a very useful tool for performance
testing or capacity planning.Summary
A transaction is a logical unit of work. Although the default SQL Server transaction isola-
tion level works well for most applications, there are several means to manipulate and con-
trol the locks. To develop a serious SQL Server application, your understanding of the ACID
database principles, SQL Server’s transaction log, and locking contribute to the quality,
performance, and reliability of the database.Major points from this chapter include the following:■ (^) Transactions must be ACID: atomic (all or nothing), consistent (before and after the
transaction), isolated (not interfering with another transaction), and durable (once
committed always committed).
■ (^) SQL Server transactions are durable because of the write-ahead transaction log.
■ (^) SQL Server transactions are isolated because of locks or snapshot isolation.
■ (^) Using traditional transaction isolation readers block writers, and writers block
readers and other writers.
■ (^) SQL Server offers four traditional transaction isolation levels: read uncommitted,
read committed, repeatable read, and serializable. Read committed, the default
transaction isolation level, works well for most OLTP databases.
■ (^) Do not use read uncommitted (or the nolock hint) unless you are absolutely sure
the side effects will not negatively affect your application data.
■ (^) Snapshot isolation involves reading the before image of the transaction instead of
waiting for the transaction to commit. Using snapshot isolation, readers don’t block
writers; writers don’t block readers; and only writers block other writers.
c47.indd 1095c47.indd 1095 7/31/2012 10:23:34 AM7/31/2012 10:23:34 AM
http://www.it-ebooks.info