Database Reference
In-Depth Information
The
ROLLBACK
statement rolls back all the actions performed in the transaction until that point. For a detailed
description of how to implement error handling in SQL Server-based applications, please refer to the MSDN Library
article titled “Using TRY . . . CATCH in Transact SQL” (
http://bit.ly/PNlAHF
)
or to the introductory article titled
Since the atomicity property requires that either all the actions of a logical unit of work are completed or no
effects are retained, SQL Server
isolates
the work of a transaction from that of others by granting it exclusive rights
on the affected resources. This means the transaction can safely roll back the effect of all its actions, if required. The
exclusive rights granted to a transaction on the affected resources block all other transactions (or database requests)
trying to access those resources during that time period. Therefore, although atomicity is required to maintain the
integrity of data, it introduces the undesirable side effect of blocking.
Consistency
A logical unit of work should cause the state of the database to travel from one
consistent
state to another. At the end
of a transaction, the state of the database should be fully consistent. SQL Server always ensures that the internal state
of the databases is correct and valid by automatically applying all the constraints of the affected database resources
as part of the transaction. SQL Server ensures that the state of internal structures, such as data and index layout, are
correct after the transaction. For instance, when the data of a table is modified, SQL Server automatically identifies all
the indexes, constraints, and other dependent objects on the table and applies the necessary modifications to all the
dependent database objects as part of the transaction.
The logical consistency of the data required by the business rules should be ensured by a database developer.
A business rule may require changes to be applied on multiple tables. The database developer should accordingly
define a logical unit of work to ensure that all the criteria of the business rules are taken care of. SQL Server provides
different transaction management features that the database developer can use to ensure the logical consistency
of the data.
As just explained, maintaining a consistent logical state requires the use of transactions to define the logical
unit of work per the business rules. Also, to maintain a consistent physical state, SQL Server identifies and works on
the dependent database objects as part of the logical unit of work. The atomicity characteristic of the logical unit of
work blocks all other transactions (or database requests) trying to access the affected objects during that time period.
Therefore, even though consistency is required to maintain a valid logical and physical state of the database, it also
introduces the undesirable side effect of blocking.
Isolation
In a multiuser environment, more than one transaction can be executed simultaneously. These concurrent
transactions should be isolated from one another so that the intermediate changes made by one transaction don't
affect the data consistency of other transactions. The degree of
isolation
required by a transaction can vary. SQL
Server provides different transaction isolation features to implement the degree of isolation required by a transaction.
■
Note
transaction isolation levels are explained later in the chapter in the “isolation levels” section.
The isolation requirements of a transaction operating on a database resource can block other transactions
trying to access the resource. In a multiuser database environment, multiple transactions are usually executed
simultaneously. It is imperative that the data modifications made by an ongoing transaction be protected from the
modifications made by other transactions. For instance, suppose a transaction is in the middle of modifying a few
rows in a table. During that period, to maintain database consistency, you must ensure that other transactions do
not modify or delete the same rows. SQL Server logically isolates the activities of a transaction from that of others by
blocking them appropriately, which allows multiple transactions to execute simultaneously without corrupting one
another's work.
