Databases Reference
In-Depth Information
Understanding Key Points
A transaction is a single unit of work for the database. A well-designed transac-
tion should accomplish one thing, and all of the statements in that transaction
should be related to that one thing only. For example, suppose a bank customer
wants to transfer money from a savings account to a checking account. This
requires two actions: extracting the money from savings and adding the money
to checking. Both actions should take place in the context of a single transaction.
With nearly all DBMSs, these changes are initially made to copies of the table
data stored in memory. The changes are written to memory. The transaction
processor also writes “before and after” versions of the data to the transaction
log. If the transaction completes without error, updating both tables, the data-
base is instructed to
commit
the transaction. This means that the changes can
be written permanently to the hard disk, and the transaction is identified as
committed in the transaction log. If there is an error while the transaction is run-
ning, the DBMS is instructed to roll back the transaction. A
rollback
is the rever-
sal of the changes made by the transaction. It's as if the statements in the trans-
action never ran.
Who or what instructs the database to commit or roll back the transac-
tion? The batch, application, or connection that initiated the transaction is
responsible for either committing or rolling back the completed transaction.
Committing or rolling back the transaction are your only options for com-
pleting a transaction. Statements executed in the context of a transaction must
either commit as a group or roll back as a group.
Partial updates,
when some
of the tables involved in an operation are updated, but not all, are normally
not allowed.
As with most standards, there are exceptions. Some DBMSs give you the abil-
ity to identify groups of statements within the transactions and manage the com-
mit and rollback process by group. This is a more advanced feature of transac-
tion management and beyond the scope of this chapter. Another way to manage
partial transactions is nesting transactions, having a transaction executing inside
another transaction, which is covered later in this chapter.
Understanding the Need for Transactions
Let's consider a specific example. Your database supports an application that sup-
ports automatic purchase order processing. After the order is filled, final pro-
cessing involves five tables, shown in Figure 10-1.
Inventory quantity on hand is reduced by the items sold and shipped. The
Sales table, which tracks sales history for order point calculations, is increased
by the same amounts. Also, an order is created, which adds rows to the Order-
Head and OrderTail tables. Finally, the CustomerBalance table is increased by
the amount of the order. If any of these updates are not made, not only is the
data inaccurate, it is inconsistent. If either Inventory or Sales don't get updated,
