Characteristics of Transactions (ACID)

A transaction is a sequence of operations executed as a single logical operation, which must expose the ACID (Atomicity, Consistency, Isolation, and Durability) properties. These are as follows:

• Atomicity—The transaction must be executed as an atomic unit of work, which means that it either completes all of its data modifications or none at all.

• Consistency—The data is consistent before the transaction begins, and the data is consistent after the transaction finishes. To maintain consistency, all integrity checks, constraints, rules, and triggers must be applied to the data during the transaction. A transaction can affect some internal SQL Server data structures, such as allocation maps and indexes, and SQL Server must guarantee that these internal modifications are applied consistently. If the transaction is cancelled, the data should go back to the same consistent state it was in at the beginning of the transaction.

• Isolation—The transaction must be isolated from changes made to the data by other transactions, to prevent using provisional data that is not committed. This implies that the transaction must either see the data in its previous state or the transaction must wait until the changes from other transactions are committed.

• Durability—After the transaction completes, its changes to the data are permanent, regardless of the event of a system failure. In other words, when a client application receives notification that a transaction has completed its work successfully, it is guaranteed that the data is changed permanently.

Every RDBMS uses different ways to enforce these properties. SQL Server 2000 uses Transact-SQL statements to control the boundaries of transactions to guarantee which operations must be considered as an atomic unit of work.

Constraints and other integrity mechanisms are used to enforce logical consistency of every transaction. SQL Server internal engines are designed to provide physical internal consistency to every operation that modifies data, maintaining allocation structures, indexes, and metadata.

The programmer must enforce correct transaction and error management to enforce an appropriate atomicity and consistency. Later in this chapter, in the "Transactions and Runtime Errors" section, you will learn about transaction and error management.

Programmers can select the right level of isolation by specifying Transaction Isolation Level or using locking hints. Later in this chapter, in the "Isolation Levels" section, you will learn how to apply transaction isolation levels. The section "Types of Locks" gives you details on how to use locking hints.

SQL Server guarantees durability by using the Transaction log to track all the changes to the database and uses the recovery process when necessary to enforce data consistency in case of system failure or unexpected shutdown.