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

[Tim Riley]

"Micah J. Cowan" wrote:

> 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).

Mysql 3.22 has never failed me. (Now running out of disk space would
present a problem, of course, but a frequent grepping of df should
forestall this. But even if it's not prevented, there's no solution anyway.)
But it doesn't take a crash for this event to occur--the customer could
abandon the transaction in the middle. Having commit/rollback doesn't
affect this.

>
>
> 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

How can the credit card number be selected from the customer's order if
it's not 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

A credit card rejection is a very natural event in the business world.
The application should be able to handle this with an update statement,
and committing/rollbacking would not affect this.

> , and
> then be unable to delete the order because, right at that very moment,
> the web server crashed

A web server is a stateless machine--no connection is maintained between
mysql and the browser like say ftp. If apache crashed in the middle of an order,

the customer's order will be incomplete with or without commit/rollback.
Moreover, this is the same situation if the customer abandoned the order
in the middle--a very natural business situation.

>

> , 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.

I think the meaning here is that because apache crashed, there's
no paper trail to collect payment.

> 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.

A credit card authorization rejection should be an update to the
customer or order table as such. The record of the attempted order should
remain in the database to provide customer service to the unfulfilled customer.

>
>
> > 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?

Did you see NBC last night exposing psychic frauds? Anyway, the issues
you raised were either resource failures or natural application events.
If disk resources are monitored and expanded as needed, mysql will
succeed to execute every valid SQL statement. However, if an application
needs to respond to naturally occuring but abnormal business events,
commit/rollback will not help.

>
>
> > > 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.

Would you like to talk about locking, tablespaces, or shared memory?

>
>
> > > 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... ;)
> _______________________________________________
> vox-tech mailing list
> vox-tech@lists.lugod.org
> http://lists.lugod.org/mailman/listinfo/vox-tech