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

Answer 1

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?