Chapter 14: Advanced Transaction Control

Overview

In Chapter 3, "Introduction to the Structured Query Language," you saw how you can group SQL statements into transactions. These SQL statements are considered a logical unit of work. One example of this is a transfer of money from one bank account to another using two UPDATE statements, one that takes money out of one account, and another that puts that money into a different account. Both UPDATE statements may be considered to be a single transaction because both statements must be either committed or rolled back together, otherwise money might be lost.

Today's databases can handle many users and programs accessing the database at the same time, each potentially running their own transactions in the database. These are known as concurrent transactions because they are run at the same time. The database software must be able to satisfy the needs of all these concurrent transactions, as well as maintain the integrity of rows stored in the database tables. You can control the level of isolation that exists between your transactions and other transactions that might be running in the database.

In Chapter 8, "Executing Database Commands," you saw how to use a transaction with a Command object. In Chapter 11, "Using DataSet Objects to Modify Data," you saw how to use a transaction with a DataAdapter. In this chapter, you'll delve into advanced transaction control using SQL Server and ADO.NET.

Featured in this chapter:

• The SqlTransaction class

• ACID transaction properties

• Setting a savepoint

• Setting the transaction isolation level

• Understanding SQL Server locks