Databases Reference
In-Depth Information
INTRODUCTION
Transactions are a key part of nearly any database application. Transactions
provide a means of helping to ensure that you maintain database integrity and
consistency by requiring that either all dependent changes are made to the
database data or none of the changes are made. In this chapter, you will learn
about transaction processing, including transaction properties and transaction
scope. You will also learn about potential concurrency errors that can occur
and methods different DBMSs use to avoid these. You'll also see how to apply
transactions in a real-world environment, using examples based on SQL Server
2005.
10.1 Understanding Transaction Basics
As you learned in Chapter 2, the term transaction refers to one or more state-
ments that execute as a group. Sound like a batch? The concept is similar, but
there is one key difference when running statements in the context of a trans-
action. All of the statement must either complete as a group, meaning that all
of the updates are made, or must be rolled back, meaning that any changes made
by the statements are reversed as if the statement never ran. The basic purpose
of transactions is to ensure consistency and data integrity during database
updates.
10.1.1 Understanding Transaction Processing
When you think about the database systems that support businesses like airlines,
banking, finance, or retail sales, you can look at them as large transaction pro-
cessing systems. Each business generates transactions in the tens of thousands
that interact in the process of doing business. Even though some of these trans-
actions just read the database and display data, most perform some type of
updating. Such critical database systems must be available at all times and must
provide fast response times for hundreds of concurrent users.
Systems of this type are especially susceptible to two major types of prob-
lems. The first is concurrent transactions attempting to update the same data
item.
Concurrent transactions
are transactions being processed simultaneously
by the database. The transactions can affect one another adversely, producing
incorrect and inconsistent results. The second problem is hardware or software
malfunctions. These could cause database failures with resulting data loss or cor-
ruption. Concurrency control, which is the process of managing concurrent
transactions, and failure recovery are key to maintaining data integrity. Because
interference between transactions can cause errors during both read and write
operations, concurrency control is critical for both.
