1048
Part VIII: Performance Tuning and Optimization
What’s New with Transactions?
SQL Server has always had transactional integrity, but Microsoft has improved it over the versions.
SQL Server 7 introduced row locking, which eliminated the need to always create locks at the page
level, which dramatically improved scalability. SQL Server 2000 improved how deadlocks are detected
and rolled back. SQL Server 2005 introduced an entirely rewritten lock manager, which simplifi ed lock
escalation and improved performance. Beyond the ANSI standard isolation levels, SQL Server 2005
added snapshot isolation, which makes a copy of the data being updated in its own physical space,
completely isolated from any other transactions, which enables readers to not block writers. Try-catch
error handling, introduced in SQL Server 2005, can catch a 1205 deadlock error.SQL Server 2008 continued the performance advances with the new ability to restrict lock escalation
on a table, which forces row locks and can improve scalability.New to SQL Server 2012 are the AlwaysOn read-only secondaries, which allow users to view data on
a read-only copy of data. Read-only secondaries manage this through the use of the read-committed
snapshot isolation level.Transactions and locking in SQL Server can be complicated. So, this chapter explains
the foundation of ACID transactions and SQL Server’s default behavior fi rst, followed by the
potential problems and variations.For the majority of SQL Server installations, the default Read Committed transaction isola-
tion level works quite well for OLTP style environments. The section on transaction
isolation level explains the exceptions.The ACID Properties
Transactions must adhere to a set of requirements, known as the ACID properties. ACID
is an acronym for four interdependent properties: Atomicity, Consistency, Isolation, and
Durability. Much of the architecture of any modern relational database is founded on these
properties. Understanding the ACID properties of a transaction is a prerequisite for under-
standing many facets of SQL Server.Atomicity
A transaction must be Atomic, meaning all changes made by the transaction are completed
as a single unit, or none of the changes are made. If a partial transaction were committed,
the atomic property is violated, and the database is left in an inconsistent state. The abil-
ity to commit or roll back transactions is required for Atomicity.Consistency
The transaction must preserve database Consistency, which means that the database must
begin the transaction in a state of consistency and return to a state of consistency when
the transaction is complete.c47.indd 1048c47.indd 1048 7/31/2012 10:23:26 AM7/31/2012 10:23:26 AM
http://www.it-ebooks.info