MySQL : transaction within a procedure

Question

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?

Answer 1

You can use transaction in a procedure. *We cannot use transaction in a function.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Then, you create my_proc() procedure which updates num to 5, then causes error by SIGNAL statement, then rollbacks with ROLLBACK statement in a transaction (START TRANSACTION and COMMIT statements) as shown below:

DELIMITER $$

CREATE PROCEDURE my_proc() 
BEGIN
  START TRANSACTION;
    UPDATE test SET num = 5;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
    ROLLBACK;
  COMMIT;
END$$ 

DELIMITER ;

Then, calling my_proc() gets the error but num is not rollbacked to 2 as shown below:

mysql> CALL my_proc();
ERROR 1644 (45000): An error occurred
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    5 |
+------+

Now, you can rollback the transaction in my_proc() procedure with DECLARE ... HANDLER statement as shown below. *Be careful, my_proc() procedure without a transaction (START TRANSACTION and COMMIT statements) doesn't rollback num to 2:

DELIMITER $$

CREATE PROCEDURE my_proc() 
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;
  START TRANSACTION;
    UPDATE test SET num = 5;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
  COMMIT;
END$$ 

DELIMITER ;

Or, you can rollback the transaction in my_proc() procedure with this below:

DELIMITER $$

CREATE PROCEDURE my_proc() 
BEGIN
  DECLARE `_rollback` BOOL DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
  BEGIN  
    SET `_rollback` = 1;
  END;
  START TRANSACTION;
    UPDATE test SET num = 5;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
  IF `_rollback` THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END$$ 

DELIMITER ;

Then, calling my_proc() doesn't get error because the error is handled by DECLARE ... HANDLER statement, then num is rollbacked to 2 as shown below:

mysql> CALL my_proc();
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    2 |
+------+

Answer 2

[This is just an explanation not addressed in other answers]

At least in recent versions of MySQL, your first query is not committed.

If you query it under the same session you will see the changes, but if you query it from a different session, the changes are not there, they are not committed.

What's going on?

When you open a transaction, and a query inside it fails, the transaction keeps open, it does not commit nor rollback the changes.

So BE CAREFUL, any table/row that was locked with a previous query like SELECT ... FOR SHARE/UPDATE, UPDATE, INSERT or any other locking-query, keeps locked until that session is killed (and executes a rollback), or until a following query commits it explicitly (COMMIT) or implicitly, thus making the partial changes permanent (which might happen hours later, while the transaction was in a waiting state).

That's why the solution involves declaring handlers to immediately ROLLBACK when an error happens.

---

Extra

Inside the handler you can also re-raise the error using RESIGNAL, otherwise the stored procedure executes "Successfully":

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            ROLLBACK;
            RESIGNAL;
        END;

    START TRANSACTION;
        -- .. Query 1 ..
        -- .. Query 2 ..
        -- .. Query 3 ..
    COMMIT;
END;

Answer 3

Here's an example of a transaction that will rollback on error and return the error code.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CREATE_SERVER_USER`(
    IN P_server_id VARCHAR(100),
    IN P_db_user_pw_creds VARCHAR(32),
    IN p_premium_status_name VARCHAR(100),
    IN P_premium_status_limit INT,
    IN P_user_tag VARCHAR(255),
    IN P_first_name VARCHAR(50),
    IN P_last_name VARCHAR(50)
)
BEGIN

    DECLARE errno INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
    SELECT errno AS MYSQL_ERROR;
    ROLLBACK;
    END;

    START TRANSACTION;

    INSERT INTO server_users(server_id, db_user_pw_creds, premium_status_name, premium_status_limit)
    VALUES(P_server_id, P_db_user_pw_creds, P_premium_status_name, P_premium_status_limit);

    INSERT INTO client_users(user_id, server_id, user_tag, first_name, last_name, lat, lng)
    VALUES(P_server_id, P_server_id, P_user_tag, P_first_name, P_last_name, 0, 0);

    COMMIT WORK;

END$$
DELIMITER ;

This is assuming that autocommit is set to 0. Hope this helps.

Answer 4

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;

Answer 5

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