basic transaction help
-
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 al
-
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 al
Hello. your problem is rollback your transaction to previous state if any error occur in transaction. you can use @@error cursor variable to recognize error. examlple begin transaction transaction1 update statment if(@@rowcount > 0) commit transaction transaction1 else if(@@error > 0) begin SET @errcode = error_message() insert into errorlog select @errcode rollback transaction transaction1 end
Regards: Ganu Sharma ;)