mysql transaction – roll back on any exception

by

You can use 13.6.7.2. DECLARE … HANDLER Syntax in the following way:

DELIMITER $$

CREATE PROCEDURE `sp_fail`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;
    INSERT INTO `tablea` (`date`) VALUES (NOW());
    INSERT INTO `tableb` (`date`) VALUES (NOW());
    INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END$$

DELIMITER ;

For a complete example, check the following SQL Fiddle.

More Related Contents:

  1. Automatic Rollback if COMMIT TRANSACTION is not reached
  2. How to debug Lock wait timeout exceeded on MySQL?
  3. MySQL AUTO_INCREMENT does not ROLLBACK
  4. Are nested transactions allowed in MySQL?
  5. MySQL : transaction within a stored procedure
  6. When to use SELECT … FOR UPDATE?
  7. How to enable INNODB in mysql
  8. How to display open transactions in MySQL
  9. How can I use transactions in my MySQL stored procedure?
  10. Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?
  11. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  12. How can I undo a mysql statement that I just executed?
  13. MySQL ‘select for update’ behaviour
  14. mysql_fetch_array() expects parameter 1 to be resource, boolean given in [closed]
  15. How to group by two rows in a column
  16. How to perform grouped ranking in MySQL
  17. How to abort INSERT operation in MySql trigger?
  18. Mysql or/and precedence?
  19. CHECK constraint in MySQL is not working
  20. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
  21. Migrate from Oracle to MySQL
  22. Which is more efficient: Multiple MySQL tables or one large table?
  23. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  24. MySQL CREATE FUNCTION Syntax
  25. MySQL sum elements of a column
  26. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  27. How to store decimal in MySQL?
  28. MySQL cumulative sum grouped by date
  29. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  30. Dump only the data with mysqldump without any table information?

Leave a Comment