SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 81

Transactions
A transaction is one or more actions that are defined as a single unit of work. In the Relational Database
Management System (RDBMS) world they also comply with ACID properties:

Atomicity: An atomic transaction means either all the actions happen or none of them. If an
action fails half-way through a transaction, then all previous actions in the transaction must be
rolled back as if they never happened.

❑

Consistency: A transaction cannot break the integrity rules of the database; it must leave the
database in a consistent state. For example, you might specify that stock levels cannot be a
negative value, a spare part cannot exist without a parent object, or the data in a sex field must be
male or female.

❑

Isolation: SQL Server is designed to service many concurrent users, but from the viewpoint
of each user, the data set must look like the user is the only one on the system. Each transac-
tion must be entirely self-contained, and changes it makes must not be readable by any other
transaction. SQL Server allows flexibility in the degree of isolation you can specify for your
transaction so that you can find a balance between the performance and business requirements.

❑

❑ Durability: When a transaction is committed, it must persist even if there is a system failure

immediately afterwards. When you commit a transaction in SQL Server, the information needed
to replay it is physically written to the transaction log before the commit is returned to the user as
successful.

Atomicity, consistency, isolation, and durability are inherent properties of SQL Server transactions.
Isolation has a degree of flexibility and is a choice which requires a more detailed understanding, so
we will explore it further in the following section.

Isolation Levels
There are five transaction isolation levels available in SQL Server 2005 that provide increasing levels of
isolation.

Read uncommitted: This is the least isolated and best performing level, but it does allow dirty
reads, non-repeatable reads, and phantoms. It can be used when you don't care about dirty reads
and you want to read the data with the lightest touch possible. It doesn't hold any locks on the
data when reading.

❑

Read committed: This is the default isolation level for SQL Server and usually provides the
best balance between performance and business requirements. It does not allow dirty reads,
but non-repeatable reads and phantoms are still possible. Any locks held are released when the
statement that caused the read operation is complete, even within a transaction. SQL Server
2005 also has a new flavor of read-committed based on row versioning called read-committed
snapshot which is covered later in this chapter.

❑

❑ Repeatable read: A repeatable read is possible by holding read locks for the duration of a

transaction to prevent other transactions from modifying the data so you can have a repeat-
able read. It prevents dirty reads and non-repeatable reads but phantoms can still occur.