Database Reference
In-Depth Information
1.
Check whether the customer is already in the database (present in the
customers
table). If not, add him or her.
2.
Retrieve the customer's ID.
3.
Add a row to the
orders
table associating it with the customer ID.
4.
Retrieve the new order ID assigned in the
orders
table.
5.
Add one row to the
orderitems
table for each item ordered, asso-
ciating it with the
orders
table by the retrieved ID (and with the
products
table by product ID).
Now imagine that some database failure (for example, out of disk space, secu-
rity restrictions, table locks) prevents this entire sequence from completing.
What would happen to your data?
Well, if the failure occurred after the customer was added and before the
orders
table was added, there is no real problem. It is perfectly valid to have
customers without orders. When you run the sequence again, the inserted cus-
tomer record will be retrieved and used. You can effectively pick up where
you left off.
But what if the failure occurred after the
orders
row was added, but before
the
orderitems
rows were added? Now you'd have an empty order sitting in
your database.
Worse, what if the system failed during adding the
orderitems
rows? Now
you'd end up with a partial order in your database, but you wouldn't know it.
How do you solve this problem? That's where
transaction processing
comes in.
Transaction processing is a mechanism used to manage sets of SQL operations
that must be executed in batches to ensure that databases never contain the
results of partial operations. With transaction processing, you can ensure that
sets of operations are not aborted mid-processing—they either execute in their
entirety or not at all (unless explicitly instructed otherwise). If no error occurs,
the entire set of statements is committed (written) to the database tables. If
an error does occur, a rollback (undo) can occur to restore the database to a
known and safe state.
So, looking at the same example, this is how the process would work:
1.
Check whether the customer is already in the database; if not, add him
or her.
2.
Commit the customer information.