A core feature of SQL Server is its ability to ensure that
transactions are atomic—that
is, all or nothing. In addition, transactions must be durable, which means that
if a transaction has been committed, it must be recoverable by SQL Server no
matter what
even if a total system failure occurs one millisecond after the
commit was acknowledged. There are actually four properties that transactions
must adhere to: atomicity, consistency, isolation, and durability, called the ACID
properties.
In SQL Server, if
work is in progress and a system failure occurs before the transaction is
committed, all the work is rolled back to the state that existed before the
transaction began. Write-ahead logging makes it possible to always roll back
work in progress or roll forward committed work that has not yet been applied
to the data pages. Write-ahead logging ensures that the record of each
transaction’s changes is captured on disk in the transaction log before a transaction is
acknowledged as committed, and that the log records are always written to disk
before the data pages where the changes were actually made are written. Writes
to the transaction log are always synchronous—that is, SQL Server must wait for
them to complete. Writes to the data pages can be asynchronous because all the
effects can be reconstructed from the
log if necessary. The transaction management component coordinates logging,
recovery, and buffer management.
The transaction management component delineates the
boundaries of statements that must be grouped together to form an operation. It
handles transactions that cross databases within the same SQL Server instance,
and it allows nested transaction sequences. (However, nested transactions
simply execute in the context of the fi rst-level transaction; no special action
occurs when they are committed. And a rollback specified in a lower level of a
nested transaction undoes the entire transaction.) For a distributed
transaction to another SQL Server instance (or to any other resource manager),
the transaction management component coordinates with the Microsoft Distributed
Transaction Coordinator (MS DTC) service using operating system remote
procedure calls. The transaction management component marks save points—points
you designate within a transaction at which work can be partially rolled back
or undone.
The transaction management component
also coordinates with the locking code regarding when locks can be released,
based on the isolation level in effect. It also coordinates with the versioning
code to determine when old versions are no longer needed and can be removed
from the version store. The isolation level in which your transaction runs
determines how sensitive your application is to changes made by others and
consequently how long your transaction must hold locks or maintain versioned
data to protect against those changes.
SQL Server 2008 supports two concurrency models for
guaranteeing the ACID properties of transactions: optimistic concurrency and
pessimistic concurrency.
Pessimistic concurrency guarantees correctness and
consistency by locking data so that it cannot be changed; this is the
concurrency model that every version of SQL Server prior to SQL Server 2005
used exclusively, and it is the default in both SQL Server 2005 and SQL Server
2008.
Optimistic concurrency, which provides
consistent data by keeping older versions of rows with committed values in an
area of tempdb
called the version store. With optimistic concurrency, readers do not block writers
and writers do not block readers, but writers still block writers. The cost of
these nonblocking reads and writes must be considered. To support optimistic
concurrency, SQL Server needs to spend more time managing the version store. In
addition, administrators have to pay close attention to the tempdb database and plan
for the extra maintenance it requires.
Five
isolation-level semantics are available in SQL Server 2008. Three of them
support only pessimistic concurrency: Read Uncommitted, Repeatable Read, and
Serializable.
Snapshot isolation level supports optimistic concurrency. The
default isolation level, Read Committed, can support either optimistic or
pessimistic concurrency, depending on a database setting.
The behavior of
your transactions depends on the isolation level and the concurrency model you
are working with. A complete understanding of isolation levels also requires an
understanding of locking because the topics are so closely related.
Please follow another article for more detail
