Microsoft® SQL Server® 2012 Bible

1049

Chapter 47: Managing Transactions, Locking, and Blocking

47

Consider the following example. Assume a foreign key constraint exists between a table

named Orders and a table named OrderDetails based on a fi eld named OrderID.

This constraint ensures that before an OrderID exists in the OrderDetails table, the

OrderID must fi rst exist in the Orders table. If a transaction were to fail to write a record

to the Orders table but successfully writes the record to the OrderDetails table, the

database would no longer be in a consistent state.

Consistency allows for the database to be in an inconsistent state during the transaction.

The key is that the database is consistent at the completion of the transaction. Like atom-

icity, the database must commit the whole transaction or roll back the whole transaction if

modifi cations resulted in the database being inconsistent.

Isolation

Each transaction must be isolated, or separated, from the effects of other transactions.

There are different levels of isolation, each level defi ning the rules for how transactions

interact with each other. Some isolation levels, such as READ UNCOMMITTED enable trans-

actions to read data that other transactions are modifying. More stringent isolation levels,

such as SERIALIZABLE, are so strict that they lock ranges of rows (if those records aren’t

modifi ed) to ensure that another transaction cannot read or modify the data involved in

the transaction. SQL Server enforces the isolation levels between transactions through the

use of locks and row versioning.

Durability

The Durability of a transaction ensures that after a transaction has been committed, it

stays committed. This is accomplished by ensuring the transaction is written to stable

media, which is typically thought of as writing to a disk drive, but it can also refer to

a battery-backed memory cache. All SQL Server transactions must fi rst be committed to

the transaction log before they are written to the data fi le. This is known as write-ahead

logging and will be explained in further detail in the section on the Transaction-Log

Architecture later in this chapter.

Programming Transactions

A transaction is a sequence of tasks that together constitute a logical unit of work. All the

tasks must complete or fail as a single unit, and the transaction must adhere to the ACID

properties just outlined. For example, for an inventory movement transaction that reduces

the inventory count from one bin and increases the inventory count in another bin, both

updates must be committed together. If this did not occur, the reliability and usefulness of

database management systems would be negligible.

In SQL Server, every DML operation (Select, Insert, Update, Delete, and Merge) is

a transaction, whether or not it has been executed within a begin transaction. For

c47.indd 1049c47.indd 1049 7/31/2012 10:23:27 AM7/31/2012 10:23:27 AM

http://www.it-ebooks.info