Databases Reference
In-Depth Information
a transaction that is updating individual rows in the CUSTOMER table, with a
check made after each update.
This is admittedly an inefficient way of testing for and responding to errors.
However, it is the one method that is supported by nearly all DBMSs. Some
DBMSs, including SQL Server, give you a second option. You can identify a block
of statements within a transaction. You also identify a separate block of error
handling code. If an error is detected while the statement block executes, exe-
cution is redirected to the error handling code. This is shown in Figure 10-3,
with the same operations as in Figure 10-2, but managed in a different fashion:
You place the statements necessary to respond to the error in the error hand-
ing code block. Somewhere within that error handling code will be a ROLL-
BACK TRAN statement. The drawing in Figure 10-3 is not a completely accu-
rate depiction of how this works because it implies that the check is made after
all of the statements execute. What actually happens is that as soon as an error
occurs, processing switches immediately from the statement block to the error
handling code block without attempting to execute any additional statements.
Using Transactions
Exactly when, then, do you use transactions? The short answer is that you should
use transactions any time you could need to roll back your changes in case of
an error. This includes all critical or sensitive database activities, or activities that
need to reference multiple data sources.
Think about what happens when a warehouse sale is posted as a customer
order. When the order is posted, it needs to:
▲
Update the inventory records for each of the order line items.
▲
Update the customer's outstanding balance with the order amount.
▲
Create a customer order record documenting the sale.
The order would then be forwarded to the warehouse for filling and ship-
ment. Now what happens if there is a problem with one of the steps, such as a
problem with creating the customer order record? Both the inventory and cus-
tomer records would be updated, but because the order isn't created, it doesn't
go to the warehouse for fulfillment. The items never get pulled and shipped. The
data in the database is inconsistent. The inventory records show fewer items on
hand than are actually there and the customer is charged for items never
received. A problem like this could be very difficult to isolate and correct.
If the order were run in the context of a transaction, when there was an
error creating the customer order, the changes to the customer records and
inventory records would be rolled back. The customer still doesn't receive the
order, but the inventory and customer records remain consistent. The customer
is never charged for the order.
