How to implement ACID transactions in MYSQL

[AditiPatil]

Hi All, In MYSQL 1)we migrated one project from Oracle to MYSQL. There is one Procedure that is called by many other procedures. We will call the procedure Level2 (Called Procedure) The Level1 (Calling ) Procedure is calling the Level2 Procedure. If something Fails in Procedure2 - Level2 (.. Called Procedure ); Any other statements in Level1 Procedure before calling proc level2 - should be rolled back since autocommit=0; In Oracle this works fine. Can some one tell How to implement the same in MySQL - Innodb. Or do we have to use falcon for the same. (In MySql as soon as we call the Level2 Procedure in Level1 - In Level2 procedure there is begin and due to this begin the previous trnasactions are commited ) Details : Table T DROP TABLE IF EXISTS `bestr`.`t`; CREATE TABLE `bestr`.`t` ( `c` binary(3) DEFAULT NULL, `Col1` varchar(45) NOT NULL DEFAULT '', `Col2` varchar(45) DEFAULT NULL, `LCT` datetime DEFAULT NULL, `DeleteFlag` varchar(1) DEFAULT NULL, PRIMARY KEY (`Col1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Insert into t Values ('1', '1', '1', CURRENT_TIMESTAMP, 'N'); Insert into t Values ('1', '2', '1', CURRENT_TIMESTAMP, 'N'); Insert into t Values ('1', '3', '1', CURRENT_TIMESTAMP, 'N'); -------------------- Proc 1 --------- DELIMITER $$ DROP PROCEDURE IF EXISTS `bestr`.`lEVEL1` $$ CREATE DEFINER=`BESTR`@`%` PROCEDURE `lEVEL1`( INO varchar(26), Out R2 Int, Out RC Int ) Begin Declare R1 Int; Set AutoCommit=0; Set R1=0; Set R2=0; START TRANSACTION ; Update T Set COL2 = Col2 + 1 Where COL1 = INO; -- Block1 Call Level2 ( INO, R1, RC); Set R2=R1; -- Block 1 end (Note if Block 1 is commented the transaction is implemented properly. -- Set R2= ROW_COUNT(); if R1 > 0 then Commit; else RollBack; end if; end $$ DELIMITER ; ======================================================== DELIMITER $$ DROP PROCEDURE IF EXISTS `bestr`.`lEVEL2` $$ CREATE DEFINER=`BESTR`@`%` PROCEDURE `lEVEL2`( INO2 varchar(26), Out R1 Int, Out RC int ) Begin Set AutoCommit=0; Set R1=0; START TRANSACTION; Select Count(1) into RC from T Where COL1 = INO2; Update T Set LCT = CURRENT_TIMESTAMP Where COL1 = INO2; Update T Set C = 'FailTest' Where COL1 = INO2; Select ROW_COUNT() into R1; if R1 > 0 then Commit ; else Rollback ; end if; end $$ DELIMITER ; ========================================================== ---------------------------------------------