How Can I Ensure MySQL Transaction Rollback on Exception?

MySQL Transaction Rollback on Exception

You want to ensure that all MySQL commands in your transaction are successfully executed or the entire transaction is rolled back in the event of any error. A common approach involves using try/catch blocks or stored procedures with error handling. However, a more versatile option is to leverage the DECLARE ... HANDLER syntax in MySQL.

The DECLARE ... HANDLER syntax allows you to define a handler that is executed when a specified SQL exception occurs. By utilizing this feature, you can configure MySQL to automatically roll back the transaction upon encountering any error.

Here's how to implement it:

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 ;

In this example, a stored procedure named 'sp_fail' is created with a handler for SQL exceptions. If any exception occurs during the execution of the transaction, the handler sets the '_rollback' flag to '1'. After all the SQL statements have been executed, an IF statement checks the value of '_rollback'. If it is '1', the transaction is rolled back using the ROLLBACK statement. Otherwise, the transaction is committed using the COMMIT statement.

By leveraging this technique, you can ensure that your MySQL transactions are either fully executed or fully rolled back, preventing partial updates or data inconsistencies.

The above is the detailed content of How Can I Ensure MySQL Transaction Rollback on Exception?. For more information, please follow other related articles on the PHP Chinese website!