How Can MySQL Automatically Roll Back Transactions on Exceptions?

MySQL Transaction: Automatic Rollback in Case of Exception

It is essential to handle errors appropriately while executing multiple MySQL commands within a transaction to ensure data integrity. In such cases, it is desirable to roll back the transaction if any exception occurs to prevent partial updates or data corruption.

Solution: DECLARE ... HANDLER Syntax

MySQL provides the DECLARE ... HANDLER syntax to handle exceptions and automatically roll back the transaction in case of an error. Here's how to use it:

1. Create a Procedure: Encapsulate the transaction within a stored procedure to handle exceptions centrally.
2. Declare a Boolean Variable: Declare a Boolean variable (e.g., _rollback) to track if an error has occurred.
3. DECLARE CONTINUE HANDLER: Use the DECLARE CONTINUE HANDLER statement to specify the action to take when an SQLEXCEPTION (or specific exception type) is encountered. Set the _rollback variable to 1 to indicate that a rollback is necessary.
4. Execute the Transaction: Execute the SQL statements within the transaction.
5. Check for Errors: After executing the statements, check the value of _rollback. If it is 1, manually roll back the transaction using ROLLBACK. If it is 0, commit the transaction using COMMIT.

By leveraging the DECLARE ... HANDLER syntax, you can ensure that any exception during a transaction will trigger a rollback, preventing data inconsistencies and maintaining database integrity.

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