Transactions!

[Benton, Kevin]

Hi Max,

> 	Note that you can call bz_start_transaction several times and
> "nest" transactions (but read the docs there on how it works).

One of the things we went over in my MySQL DBA class was: starting a new
transaction automatically commits a previous transaction.  To quote
MySQL 5.0 Certification Study Guide p. 418,

    "If you issue any of the following statements, InnoDB
     implicitly commits the preceding uncommitted statements
     of the current transaction and begins a new transaction:

     ALTER TABLE
     BEGIN
     CREATE INDEX
     DROP DATABASE
     DROP INDEX
     DROP TABLE
     RENAME TABLE
     TRUNCATE TABLE
     LOCK TABLES
     UNLOCK TABLES
     SET AUTOCOMMIT = 1
     START TRANSACTION

     UNLOCK TABLES implicitly commits only if you have
     explicitly locked tables with LOCK TABLES.
     SET AUTOCOMMIT = 1 implicitly commits only if autocommit
     mode wasn't already enabled."

I'm concerned that developers might get confused by this if they're used
to the way that MySQL handles transactions.  How does
bz_start_transaction deal with this?

It's good, however, that the transaction isolation level is settable at
the global and session levels so that mod_perl clients.  It seems that
having the ability to set the isolation level dynamically makes sense as
a parameter to bz_start_transaction, but sets it by default to
repeatable read.  Then, the isolation level would remain in effect only
for that transaction.  I think that would be a very good improvement to
an already nice set of improvements to the Bugzilla DB.

For those that are not familiar with MySQL transactions, the reason this
is so critical with mod_perl clients is that starting a transaction
doesn't just impact inserts and updates (writes) to the database.  It
also impacts selects (reads).  So, as soon as a transaction is started,
with repeatable read, the database essentially takes a snapshot of the
data at that point in time and provides results accordingly.  If that
transaction is not closed out, that session will only get to see data
from the point in time where the transaction was started.  In other
words, even though subsequent commits may have been done, that mod_perl
session will not (can not) see those updates.  That's why I'm concerned
about bz_start_transaction implementing transaction nesting with
repeatable read isolation.

If you'd like to read more about InnoDB Isolation levels,
multi-versioning, and concurrency, check out MySQL 5.0 Certification
Study Guide p. 420-422.

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.
 

> -----Original Message-----
> From: developers-owner at bugzilla.org
[mailto:developers-owner at bugzilla.org]
> On Behalf Of Max Kanat-Alexander
> Sent: Wednesday, March 14, 2007 10:46 PM
> To: developers at bugzilla.org
> Subject: Transactions!
> 
> 
> 	I just checked in full transaction support in the database
> layer, for Bugzilla.
> 
> 	For anybody who doesn't know, a transaction is a way of doing a
> bunch of SQL commands, and only writing to the disk once they've all
> succeeded.
> 
> 	Also, with transactions you get the same benefits of locking
> without having to lock the whole table.
> 
> 	Let me explain that last part (about getting the advantages of
> locking) a bit more. When you do a transaction, this is what happens:
> 
> 	1) You start the transaction. ($dbh->bz_start_transaction())
> 
> 	2) You run at least one SQL statement.
> 
> 	3) From that point on, every other statement sees the database
> as it was immediately before you ran that first SQL statement. That
is,
> if the database gets modified somehow while you're in the middle of
> your transaction, it won't affect your transaction. This is a Good
> Thing, because it avoids race conditions. That's why we have locks in
> the first place, for the most part--to avoid race conditions. (For
> those who know about transactions, we use REPEATABLE READ. That could
> always change, though, if we decide READ COMMITTED or SERIALIZABLE are
> more appropriate.)
> 
> 	4) You commit the transaction. ($dbh->bz_commit_transaction())
> 
> 	5) The database writes your changes to disk.
> 
> 	Instead of committing, you can also rollback the transaction,
> which means that none of your changes will be written to disk. Only
> ThrowError and Bugzilla::_cleanup() need to do this, really.
> 
> 	Don't depend on rollback() for the correctness of your code.
> It's there to handle critical cases, not to undo things that the code
> decides it shouldn't have done. In other words, don't use rollback in
> your code. (It should only be in the places where it is now.)
> 
> 	You can see the POD docs for the transaction methods here:
> 
>
http://www.bugzilla.org/docs/tip/html/api/Bugzilla/DB.html#Transacti
> on_Methods
> 
> 	Note that you can call bz_start_transaction several times and
> "nest" transactions (but read the docs there on how it works).
> 
> 	Generally, we'll be replacing every bz_lock_tables call with a
> call to bz_start_transaction.
> 
> 	Under MySQL, however, the longdescs table isn't transactional.
> That is, every write to it happens immediately. So it still needs to
be
> locked.
> 
> 	If anybody has any questions about transactions, feel free to
> ask here or in #mozwebtools.
> 
> 	I'll probably be doing most of the conversions from
> bz_lock_tables to bz_start_transaction myself, but if somebody wants
to
> help, they're welcome to file bugs and write patches. The tracking bug
> is:
> 
> 	https://bugzilla.mozilla.org/show_bug.cgi?id=121069
> 
> 	-Max
> -
> To view or change your list settings, click here:
> <http://bugzilla.org/cgi-bin/mj_wwwusr?user=kevin.benton@amd.com>
>