Transaction Management

You'll recall that in Chapter 9, you learned how to explicitly control transactions. I'll briefly review this topic as it applies to database programming objects. Transaction statements (BEGIN, ROLLBACK, and COMMIT TRANSACTION) are used to queue up a set of statements and control the sequence and dependency of a group of operations. For example, if a stored procedure or user-defined function were to update several sales records and then delete sales records based on some criteria that may have been modified in the UPDATE statement, it would be important to let the update operation finish before deleting any records. In its ever-zealous quest to be efficient, SQL Server may perform operations in parallel, thus working against important business logic. In such cases, it would be important to serialize these dependent operations into separate transactions.

Another important purpose for transactions is to manage the atomicity of a group of operations. If multiple operations are grouped into a single transaction, they are executed as a unit. The outcome of the entire transaction is dependent upon the success of all statements. If they all succeed, the transaction is rolled forward from the transaction log and succeeds. If any operations are unsuccessful, or an error is otherwise raised; the transaction is rolled back, and none of the operations result in committed (inserted, updated, or deleted) records.

Stored procedures are the ideal environment for transactional management. Using the techniques demonstrated in Chapter 9, it's a simple matter to wrap groups of statements into a transactional batch. You can also use error-handling script with transaction management to make your procedures even more bulletproof.

Locking Options

One of the database engine's important jobs is to balance the task of record locking (to protect data as it's modified) and to present consistent result sets of data to queries. This behavior is performed automatically and usually requires no intervention. SQL Server implements locking at various levels based on requested operations and concurrent users sharing the same or adjacent data. On rare occasions, it may be necessary to override the default locking behavior within a transaction. This is done using the SET TRANSACTION ISOLATION LEVEL statement. The locking options described in the following table are supported in SQL Server 2000.

Locking Option	Description
READ UNCOMMITTED	Records are read from the transaction log if they have been modified. This includes “dirty” records that have yet to be rolled forward into table data pages.
READ COMMITTED	This is the default behavior. Records are read only from data pages after newly committed rows have been rolled forward into the database. This option prevents inaccurate dirty reads of data within the context of the current transaction. However, concurrent statements could modify records between operations creating an anomaly known as a phantom read. This typically only happens in cases where multiple users are frequently modifying multiple records.
REPEATABLE READ	This option locks the transaction unconditionally so that no other operations can modify records. This is an extreme measure that can cause increased locking contention, and is recommended only in rare cases.
SERIALIZABLE	This option serializes transactions so that no concurrent operations can be performed that would affect the state of records within the current transaction. This is done by locking records within a range of key values or other search criteria. This has the advantage of simplifying locking contention problems; however, it can impair functionality and performance.

SQL Server 2005 introduces a new isolation level called SNAPSHOT. Snapshot isolation allows users to access the last committed version of data even if that data is undergoing modification. With Snapshot isolation, when data is modified a copy of the data is written to TempDB. Any other transaction that attempts to read the data being modified will be redirected automatically to the copy stored in TempDB.

The behavior of these options may appear to be easily predictable. However, the actual locking behavior of individual rows, tables, and other objects is a result of the locking options specified by the combined transactions as multiple operations are performed on the same data. The following example serializes these other users' operations on the same data. This way, no modifications would be allowed to these records between the two UPDATE statements:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION GO UPDATE Product SET StandardCost = StandardCost * 1.15 WHERE ProductSubCategoryID = 1 GO UPDATE Product SET ListPrice = ListPrice * 1.15 WHERE ProductSubCategoryID = 1 AND StandardCost < 1000 GO COMMIT TRANSACTION