Posted by Gordon Burditt on 06/13/06 22:11

>Often times when writing a php script to handle some mysql DB
>transactions, I must write code that performs, say, an insert into a
>MySQL DB, then retrieves the last item's index, and makes a new
>insertion into another table on the DB that requires the previously
>obtained index. Both queries must be executed and completed - if query
>one succeeds and query two fails, I roll back the changes of query one.

Of course, you are using transactions here.

>Obviously, there will be times when many interdependent queries are
>involved - rolling back in such cases is a headache, albeit a necessary
>one. We all know that a robust web system must have a lot of error
>checking, and the intergrity of the databases must be protected at all
>times.
>
>Now, for the big problem that I cannot find an *eloquent* solution
>to...
>
>Suppose that in the original example, query one is executed and
>completed. However, just before query two is made by the PHP script,
>the whole darn server goes down. Thus, query one is complete, query
>two never took place, and when the server is restarted, the database is
>corrupt!

Isn't an *UNCOMMITTED* transaction supposed to be rolled back, if it
in fact ever made it to disk in the first place, on a server restart?

I believe MySQL with InnoDB tables is capable of this.

Gordon L. Burditt

[Back to original message]