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

[Matt Roper]

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

Regardless of how stable MySQL may be, your database server is still
susceptible to power outages, hardware failures, etc.  The idea behind
transactions is that they're "all or nothing."  The idea behind
transactions is not to make sure that a single query succeeds, but that
a sequence of queries all succeed or fail together.

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

The credit card number might be in the database already, but the actual
list of items ordered are not.  But instead of using the credit card
example, let's just suppose you're maintaining the user's account
balance in the database.  When the user makes an order, you need to
perform two operations:  perform an "insert" on the order table and
perform an "update" on the "account" table.  If you perform these
queries without transactions, there's the possibility that something
will happen between the two SQL statements (power outage, etc) and only
the first one will actually be recorded on the hard drive.  If you
update the account balance first, the user will have been billed for
items, but since you never got around to placing those items in the
order table, they'll never be shipped.  On the other hand, if you place
the items in the order table first and then the crash occurs, the user
will wind up getting the items for free (i.e. when you resurrect the
server, you have no way of knowing that the account balance was never
updated).

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

You're missing Micah's point.  Suppose the web application is written in
PHP.  Without transactions, your PHP script sends the SQL statements one
at a time and the results are immediate.  If the web server crashes
(either software crash or hardware crash) between two consequetive SQL
statements, only the first one will actually get shipped off to the
database server and take effect.  Without transactions, the DB server
has no way of knowing that the webserver was planning sending more
updates.  Your data is left in an inconsistent state since the database
doesn't realize that more commands should have been coming and when you
resurrect the web server, it won't know that it stopped halfway through
the PHP script.

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

Yes, but if you have a sequence of SQL statements that should be
performed as a package (all or nothing), there's no way to ensure that
the changes from the first statements only take effect if _every single_
statement got through.  If something (web server, DB server, network
connection, etc.) dies halfway through, your data is inconsistent.


Matt

-- 

*************************************************
* Matt Roper <matt@mattrope.com>                *
* http://www.mattrope.com                       *
* PGP Key: http://www.mattrope.com/mattrope.asc *
*************************************************