try....catch in mysql for transaction?

Question

How can I start a transaction when there is any error in the SQL statements the system will rollback the changes automatically?

Transaction MySQL

PHP + MySQL transactions examples

in PHP

try {
    // First of all, let's 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
    // i.e. 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 the logic without PHP,only MYSQL

Rakesh Soni · Answer 1 · 2020-04-02T06:47:58.373

We can write the multiple queries into the MySQL procedure/function and can maintain the transaction. Please refer the sample given below. Basically, you should declare an error handler which will call 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

Please see the links below for more details

MySQL : transaction within a stored procedure

How can I use transactions in my MySQL stored procedure?

I need try catch in Transaction, not in procedure transaction — Harikrushna Patel, Mar 10 '22 at 10:24

score -10 · Answer 2 · edited Apr 19 '18 at 21:58

This is my Last work related to transaction in SQL, maybe the code sample below can help you. The code was developed for MS SQL server. Note that you can not use it in MySQL servers, because MySQL does not have that functionality.

The Main idea is to place the main query (which can be more than one) inside "try" and "transaction" clauses, then if the query executes successfully, hence the query will be committed in the database, otherwise in case of failure, an error will be raised in the "catch" section before the transaction gets rollbacked entirely.

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

This is not useful since the question asks for try/catch in mysql. — Zach Smith, Oct 14 '18 at 18:52

try....catch in mysql for transaction?

2 Answers2