[vox-tech] Unnecessary RDBMS features [was Need your help in recovering database.]

[Micah J. Cowan]

On Thu, Apr 24, 2003 at 01:48:04PM -0700, Tim Riley wrote:
> Commit/rollback requires that a copy be made of each datum involved
> in an insert, update, or delete statement. This is very expensive. But
> why make the copy? I know my insert, update, or delete is the correct
> thing to do at the time.

But what about the quite frequent case where what you're doing is the
correct thing to do *only* if the next transaction you're about to do
succeeds?

There are many cases where it would be a bad thing to let the
transaction you just completed remain effective if the next one
fails. If the server shuts down right between the two, you are left
with quirky data.

Example: suppose you need to insert a customer's order for a set of items in
your e-commerce solution. Your solution is to add a row to the Order
table, plus one row in the Ordered_Items table for each item ordered
(so Shipping can determine what to ship). You link the two by ID in
another table, so you can find the Ordered_Items by the Order, and
vice-versa.

If either the web server or database server goes down while you're
adding Order_Items, you'll have an incomplete order, with no way of
knowing that it is incomplete. This is Bad(TM).

To make matters worse, there's the following chicken-or-egg problem:
You want to charge the customer's credit card for the order, but not
unless you're sure that the order will be in the database - customers
get very irate when charged for a service they have not
received. OTOH, it'd be just as bad to put the order in there, then
charge the customer, only to find out the credit card info is bad, and
then be unable to delete the order because, right at that very moment,
the web server crashed, and you have no contextual information to tell
you later that that order shouldn't be in there because you haven't
been paid for it yet. The only way I know of to resolve the situation
is:

  1. Create the order in the DB.
  2. Attempt to charge the card.
  3. If the charge was successful, commit the Order and all the
     Order_Items.
  4. If the charge was unsuccessful, rollback.

> If I'm making a mistake to the database,
> I'll fix it when I catch it.

You must be server-psychic. Can you explain to me how you would catch
the errors above, in a reliable fassion?

> > I
> > agree with you that feature creep causes major bloat, which in turn
> > causes huge opportunities for bloat.
> 
> If you want to expand on this more, there are other areas of Oracle-like
> DBMS features that cause unnecessary bloating and headaches.

Unnecessary is a relative term.

> > But when you have such an
> > important feature for guaranteeing data reliability,
> 
> I'm curious to learn what you consider data reliability to be
> and how commit/rollback guarantees it.

See above example. In my experience, commit/rollback is worth its
weight in gold*

-Micah

*Of course, if you took that statement /literally/, you'd probably
 agree with me, since it doesn't way anything... ;)