Try .... catch in mysql for transaction?

Question

How can I start a transaction, if there is any error in the SQL statements, the system will automatically roll back from the changes?

MySQL Transaction

PHP + MySQL transaction examples

in php

try { // First of all, let begin a transaction $db->beginTransaction(); // A set of queries; if one fails, an exception should be thrown $db->query('first query'); $db->query('second query'); $db->query('third query'); // If we arrive here, it means that no exception was thrown // ie no query has failed, and we can commit the transaction $db->commit(); } catch (Exception $e) { // An exception has been thrown // We must rollback the transaction $db->rollback(); } 

How to repeat logic without PHP, only MYSQL

Answer 1

This is my last transaction related work in sql, maybe the sample code below may help you. The code was developed for the MS SQL server, and you may need to modify it a bit for the MySQL server.

The main idea is to place the main request (which can be more than one) inside the sentences "try" and "transaction", and then, if the request is successful, then the request will be transferred to the database, otherwise in case of an error, the error will be raised in the catch section before the transaction is completely rejected.

 BEGIN TRY BEGIN TRANSACTION --Insert Your Queries Here-- COMMIT END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); IF @@TRANCOUNT > 0 ROLLBACK RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH 

Answer 2

You can write multiple queries to a MySQL procedure / function, and you can support a transaction, as with the example below. Basically, you declare an error handler that will trigger a rollback.

 PROCEDURE `myprocedure`() BEGIN .. Declare statements .. DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN .. set any flags etc eg. SET @flag = 0; .. ROLLBACK; END; START TRANSACTION; .. Query 1 .. .. Query 2 .. .. Query 3 .. COMMIT; .. eg. SET @flag = 1; .. END 

See the links below for more details.

MySQL: transaction inside stored procedure

How can I use transactions in my MySQL stored procedure?