Search code examples
sqlmysqldatabasestored-procedurestransactions

MySQL : transaction within a procedure

The basic structure of my procedure is:

BEGIN
  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?
Solution

  • 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;