transaction basics in mysql

See more:

MySQL

database

Hello all,
long time lerker, normally just hang out in the code lounge, but
I would really appreciate some help with mysql as I am new to
it. I am a long time Sybase ASE user... so some things in
mysql don't quick click for me as easy as they should...

Ever deal with transactions in mysql? they seam to work not as I
expect from my exp with sybase and mssql and just wanted to see how
you handled em...
first off, yeah, i know you have to use the innodb storage engine
instead of the default myisam... so far that hasn't been a problem,
I've heard people say that innodb just doesn't have the performance
that myisam does but I haven't run into that... have you?
plus it seams easy to convert between the two:
"ALTER TABLE tablename ENGINE=INNODB;"

anyway, how I know a transaction would look like this in a stored
procedure in mysql:

................................cut.................................
DELIMITER $$
DROP PROCEDURE IF EXISTS someProc$$
CREATE PROCEDURE someProc(IN params VARCHAR(80))
SQL SECURITY INVOKER
BEGIN
IF NOT EXISTS ([.. some condition ..]) THEN
[.. do some stuff ..];
[.. do some stuff ..];
[.. do some stuff ..];
ELSE
[.. do some stuff ..];
START TRANSACTION;
[.. these are the UPDATE statements you want in a transaction ..]
UPDATE sometable1 set somevalue=value WHERE something=something;
UPDATE sometable2 set somevalue=value WHERE something=something;
UPDATE sometable3 set somevalue=value WHERE something=something;
UPDATE sometable4 set somevalue=value WHERE something=something;
[.. these are the UPDATE statements you want in a transaction ..]
COMMIT;

[.. do some more stuff ..];
END IF;
END$$
DELIMITER ;
................................cut.................................

What I would expect the above to do, is that the statements in
the transaction block either all fire or all fail/rollback if
there was an error with one of them... this is how it was in sybase..

however, I end up with inconstant data in sometable3 and 4 if
the update statement failed on sometable2.

what I found I have to do, is call an explicit rollback on an error
condition, so adding:
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK;

will call a rollback if an error occurs of type sqlexecption,
sqlwarning or 'not found'. (basically, any error)

I add it as the 1st line in the stored procedure after the begin
block and now the above works as I expect it too (well almost) now
if one of the updates to sometable1-4 fail, all are returned to the
pre transaction state.

just wondering if this is how you handled transactions, or if I
am off base and missing something more fundamental...

the only other difference with mysql from sybase is that a rollback
does not raise an error back to the application... I can see this
being handy in places, but for this case, I do want the behavior, so
I changed my exit handler to this:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN ROLLBACK; CALL ERROR_ROLLBACK_OCCURRED; END;

this will issue a rollback then call a non-existent stored procedure
to raise an error (poor-man's raise error until mysql6 is mainstream)

oh well, have a good one, I'd appreciate any feedback,

Thanks,
-Jam