Transactions!

[Benton, Kevin]

> On Thu, 15 Mar 2007 09:03:03 -0700 "Benton, Kevin"
> <kevin.benton at amd.com> wrote:
> > One of the things we went over in my MySQL DBA class was: starting a
> > new transaction automatically commits a previous transaction.
> 
> 	You need to read the cooooooode... :-)

Hi Max,

I did read the code.  As I said, this can be a source of confusion for
developers who don't expect nested transactions (quoting you from bug
374012 - "Because transactions are nestable in Bugzilla").

I see problems as documented in the following:

https://bugzilla.mozilla.org/show_bug.cgi?id=374012
https://bugzilla.mozilla.org/show_bug.cgi?id=374004
https://bugzilla.mozilla.org/show_bug.cgi?id=374093

To summarize: Since this code makes it possible for multiple calls to
$dbh->bz_start_transaction, it introduces a weakness.  Rollback rolls
everything back to the real start of the transaction, not the nested
starting point.

So, here is one scenario that would demonstrate the weakness:

Time 1:  Routine A starts a transaction using
$dbh->bz_start_transaction.

Time 2:  Routine A begins an update or insert.

Time 3:  Routine A calls routine B.

Time 4:  Routine B starts a transaction using
$dbh->bz_start_transaction.

Time 5:  Routine B begins an update or insert.

Time 6:  Routine B discovers it must roll back.

Time 7:  Routine B calls $dbh->bz_rollback_transaction.  This causes
         bz_rollback_transaction to actually roll back the entire
         transaction.

Time 8:  Routine B returns to routine A without returning an
         error or warning back to Routine A.

Time 9:  Routine A calls routine C.

Time 10: Routine C starts a transaction using
$dbh->bz_start_transaction.

Time 11: bz_start_transaction does a new BEGIN to the DB.

Time 12: Routine C updates or inserts into the DB.

Time 13: Routine C calls $dbh->bz_commit_transaction.  This causes
         bz_commit_transaction to actually commit the transaction to the
         DB because there were no "nested" transactions that were
active.
         Routine C's updates have been committed to the database.
Time 14: Routine C returns - no errors.

Time 15: Routine A does a little more updating and/or inserting but does
         Not call $dbh->bz_commit_transaction again (why should it - it
         didn't know it had to).

Time 16: Routine A calls $dbh->bz_commit_transaction.  This causes
         RaiseError to be called because there is no active transaction.

Note that because of the rollback from "second" transaction, this
negatively impacted the first, and by implication, the "third".  Each of
these is really supposed to be a part of one real transaction, but
they're technically separate because each calls
$dbh->bz_start_transaction.  Each caller currently has no way of knowing
that another transaction is already active.

You could argue that we don't have this condition today - you'd be
right.  The problem is, we do nothing to prevent it, nor do we have
documentation to help developers & reviewers to avoid it.  The only way
to really prevent this from happening is to make sure that each CGI (or
other form of main program) calls $dbh->bz_start_transaction as it
starts (implicitly turning off autocommit), then calls
$dbh->bz_commit_transaction as it completes.  At the same time, make any
call to $dbh->bz_rollback_transaction execute the rollback and then
throw an error.  That way, all processing is done within a "root"
transaction.  We would need to agree as reviewers to refuse to allow
transactions to occur one after another in series.

This would not work, however, for mod_perl sessions since they persist
beyond one user's connection.  Another method would need to be used in
order to make that work.

Because $dbh->bz_commit() causes RaiseError to be called (killing the
CGI), it's difficult if not impossible to ask a CGI to clean up after
itself given the code in place today.

Kevin Benton
Senior Software Developer
MSS Silicon Design Engineering
Advanced Micro Devices
 
The opinions stated in this communication do not necessarily reflect the
view of Advanced Micro Devices and have not been reviewed by management.
This communication may contain sensitive and/or confidential and/or
proprietary information.  Distribution of such information is strictly
prohibited without prior consent of Advanced Micro Devices.  This
communication is for the intended recipient(s) only.  If you have
received this communication in error, please notify the sender, then
destroy any remaining copies of this communication.