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?
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;
Just an alternative to the code by rkosegi,
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