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

[Micah J. Cowan]

On Thu, Apr 24, 2003 at 07:23:33PM -0700, Tim Riley wrote:
> 
> 
> "Micah J. Cowan" wrote:
> > 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.)

Translation: you've never been unlucky enough for this to happen to
you. You still don't describe how you'd avoid problems if it did.

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

No: this is after the customer has already made the transaction. This
is the final step, when the database is actually placing the order.

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

A. You have it in session data because the customer just entered it.
B. You retrieve it prior to any of the other database operations.

You're just being a little silly, now.

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

You keep saying "committing/rollbacking would not affect this." Could
you explain why, in context of my examples? Because I just backed up
my statements with concrete examples. You haven't done this
yet. Answer the question: what would you do to avoid having either an
order in the DB marked to be shipped without payment, or payment for
an order which didn't get into your DB? Please be specific, instead of
just saying "that won't matter."

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

As I said: no, this is when the order is actually being *placed*.

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

Not if something kills mysql, or the web server doesn't exist to send
it the SQL query anymore.

> However, if an application
> needs to respond to naturally occuring but abnormal business events,
> commit/rollback will not help.

Prove it.

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

Un-freaking-believable. You think locking is a bad idea, too?

-Micah