Transaction Processing

Transaction processing guarantees the consistency and recoverability of SQL Server databases. A transaction is the basic unit of work under SQL Server. Typically, it consists of several SQL commands that read and update the database, but the update is not considered "final" until a COMMIT command is issued. (Note that the example below is pseudocode and that error handling is required to achieve the behavior described.)

Transaction processing in SQL Server assures that all transactions are performed as a single unit of work ”even in the presence of a hardware or general system failure. Such transactions are referred to as having the ACID properties: atomicity, consistency, isolation, and durability. In addition to the explicit multistatement transactions such as those provided in the DEBIT_CREDIT example below, SQL Server guarantees that a single command that affects multiple rows maintains the ACID properties.

Here's an example in pseudocode of an ACID transaction, followed by an explanation of each of the ACID properties.

 BEGIN TRANSACTION DEBIT_CREDIT  Debit savings account 00  Credit checking account 00  COMMIT TRANSACTION DEBIT_CREDIT 

Atomicity

SQL Server guarantees the atomicity of its transactions. With atomicity, each transaction is treated as all-or-nothing ”it either commits or aborts. If a transaction commits, all of its effects remain . If it aborts, all of its effects are undone. In the previous DEBIT_CREDIT example, if the savings account debit is reflected in the database but the checking account credit isn't, funds will essentially disappear from the database; that is, funds will be debited from the savings account but never credited to the checking account. If the reverse occurred (if the checking account were credited and the savings account were not debited), the customer's account would mysteriously increase in value without a corresponding customer cash deposit or account transfer. Because of SQL Server's atomicity feature, both the debit and credit must be completed, or neither event is completed.

Consistency

The consistency property ensures that a transaction won't allow the system to enter an incorrect logical state ”the data must always be logically correct. Constraints and rules are honored, even in the event of a system failure. For the DEBIT_CREDIT example, the logical rule is that money can't be created or destroyed ”a corresponding, counter-balancing entry must be made for each entry. (Consistency is implied by, and for most situations is redundant to, atomicity, isolation, and durability.)

Isolation

Isolation separates concurrent transactions from the updates of other incomplete transactions. In the DEBIT_CREDIT example, another transaction can't see the work-in-progress while the transaction is being carried out. For example, if another transaction read the balance of the savings account after the debit occurred, and then the DEBIT_CREDIT transaction was aborted, the other transaction would be working from a balance that never logically existed.

Isolation among transactions is accomplished automatically by SQL Server. It locks data to allow multiple concurrent users to work with data, but it prevents side-effects that could distort the results and make them different than would be expected if users serialized their requests (that is, if requests were queued and ran one at a time). This serializability feature is one of the isolation levels that SQL Server supports. SQL Server supports multiple degrees of isolation levels that allow you to make the appropriate tradeoff between how much data to lock and how long locks must be held. This tradeoff is known as concurrency vs. consistency . Locking reduces concurrency (because locked data is unavailable to other users), but it provides the benefit of higher consistency. (Chapter 13 provides much more detail about locking.)

Durability

After a transaction commits, SQL Server's durability property ensures that the effects of the transaction will persist even if a system failure occurs. Conversely, if a system failure occurs while a transaction is in progress, the transaction will be completely undone, leaving no partial effects on the data. For example, if a power outage occurs in the midst of a transaction before the transaction is committed, the entire transaction will be automatically rolled back when the system is restarted. If the power fails immediately after the acknowledgment of the commit is sent to the calling application, the transaction is guaranteed to exist in the database. Write-ahead logging and automatic rollback and rollforward of transactions during the recovery phase of SQL Server startup assure durability.