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

[Jeff Newmiller]

On Thu, 24 Apr 2003, Tim Riley wrote:

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

Anything that interferes can cause problems.... network disconnection, 

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

Transactions should not span "user interactive" time.... they should only
last while a (hopefully short) batch of selects, updates, or network
communication events take place.  That is, after the user enters
everything into temporary storage, the final group of related operations
should all be invoked and results committed as a result of one user input 
event (proceed button).  This is no different than locking.

Nevertheless, elements involved during this sequence of operations may
include multiple computers separated by an undefined number of network
links, and supported by an unspecified range of power sources, and subject
to unspecified product reliability factors, as well as multitasking shared
access to a common data store.

Locking can require sophisticated algorithms to cover all these cases...
producing the net effect of transactions. I prefer to let someone else
figure out how to implement the transactions, and simply specify that they
are required.  You apparently don't... well, I don't like to argue
religion either.

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

A rollback is a lot simpler to use at the application level... so it is
more likely to be used.

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

See above.  For a web server handling a lot of transactions, _any_ time
the server fails it will be likely to interfere with a transaction.  
And, again, transactions should not be applied to trans-connection
recovery.

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

Yes, in the real world screwing the supplier is frowned on at least as
much as screwing the customer.

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

True, but the issue of how to update the "current state" of the order
remains.  Even if you parse the entire transaction history every time you
want to figure that information out, you will need to lock multiple tables
to do so, and then you are dealing with the Dining Philosopher's problem.
You might go a long time without encountering access conflict, but that
just makes any bugs in your code harder to find.

Transactions address these issues with tested algorithm implementations.

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

Is that relevant, or just a snide comment? Please stay out of the gutter.

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

Commit/rollback won't help user-level issues, but it is a lot easier to
update multiple tables with it than with locking, and both resource
failures and shared access contention are covered.

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil@dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...2k
---------------------------------------------------------------------------