Transaction Processing

Transaction processing guarantees the consistency and recoverability of SQL Server databases. A transaction is the basic unit of work in 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.

Transaction processing in SQL Server ensures 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 guaranteeing that explicit multistatement transactions (such as those provided in the following DEBIT_CREDIT example) maintain the ACID properties, SQL Server guarantees that a single command that affects multiple rows also maintains the ACID properties.

Here's an example in pseudocode of an ACID transaction. (Note that error handling would be required to achieve the behavior described earlier.)

 BEGIN TRANSACTION DEBIT_CREDIT Debit savings account $1000 Credit checking account $1000 COMMIT TRANSACTION DEBIT_CREDIT 

Now let's take a closer look at each of the ACID properties.

Atomicity

SQL Server guarantees the atomicity of its transactions. Atomicity means that each transaction is treated as all-or-nothingit 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 subtracted from the savings account but never added to the checking account. If the reverse occurs (if the checking account is credited and the savings account is not debited), the customer's checking account will 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 else neither event is completed.

Consistency

The consistency property ensures that a transaction won't allow the system to arrive at an incorrect logical state—the data must always be logically correct. Constraints and rules are honored even in the event of a system failure. In 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 in most situations is redundant with, 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 reads the balance of the savings account after the debit occurs, and then the DEBIT_CREDIT transaction is aborted, the other transaction will 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 can distort the results and make them different than would be expected if users serialized their requests (that is, if requests were queued and serviced 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 to hold locks. 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 14 provides much more detail about locking.)

Durability

After a transaction commits, SQL Server's durability property ensures that the effects of the transaction persist even if a system failure occurs. Conversely, if a system failure occurs while a transaction is in progress, the transaction is 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 is 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 ensure durability.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net