Priyank Kapasi Priyank Kapasi - 3 years ago 137
MySQL Question

MySQL : transaction within a stored procedure

The basic structure of my stored procedure is,

BEGIN

.. Declare statements ..

START TRANSACTION;

.. Query 1 ..
.. Query 2 ..
.. Query 3 ..

COMMIT;

END



MySQL version: 5.1.61-0ubuntu0.11.10.1-log


Currently, if 'query 2' fails, result of 'query 1' is committed.


  • How can I rollback the transaction if any of the query fails?


Answer Source

Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Basically you declare error handler which will call rollback

START TRANSACTION;

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        EXIT PROCEDURE;
    END;
COMMIT;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download