How to Automatically Roll Back MySQL Transactions on Exceptions?

Rolling Back MySQL Transactions on Exceptions

When executing a series of MySQL commands, it is crucial to handle errors to ensure data integrity. One way to achieve this is to automatically roll back the entire transaction in case any command encounters an exception.

To set up automatic rollback, utilize the DECLARE ... HANDLER syntax:

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 ;

This procedure defines a handler (CONTINUE HANDLER FOR SQLEXCEPTION) that sets the _rollback flag to 1 if an SQL exception occurs. After executing the individual commands within the transaction, it checks the _rollback flag to determine whether to roll back or commit the transaction.

By implementing this technique, any exception encountered during command execution will cause the entire transaction to be rolled back, ensuring that partial changes are not applied to the database.

The above is the detailed content of How to Automatically Roll Back MySQL Transactions on Exceptions?. For more information, please follow other related articles on the PHP Chinese website!