MySQL : transaction within a stored procedure

Question

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 1

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;