Keep local variable after rollback in mysql

Question

I wish to keep local variable from stored procedure even if rollback occurres.

In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as arguments. When I call the procedure with user_id = 1, value_from = 10, value_to = 20, it registered numbers 10, 11, ..., 19, 20 to user 1. Every number can be registered only once and only to one user.

There are two possible conflicts: I try to register number, which is already registered to another user, or I try to register number, which is already mine.

I wish to determine, which of these options occurred and store that value.

Here is my procedure, which takes also OUT parameter, which describes the result of the operation

-- create table
CREATE TABLE test
(
    value INT NOT NULL UNIQUE,
    user_id INT NOT NULL
) ENGINE = INNODB;

-- fast procedure with transactions
DELIMITER $$
CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;
            ROLLBACK;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END$$
DELIMITER ;

The problem is, that ROLLBACK not only throws away all new records in table test, it also resets the value of @retval.

Is there any way to keep the custom error message even in case of ROLLBACK

-- test - should return OK - work fine
SET @retval := 'UNKNOWN';
CALL sp_insert_values(1, 10, 20, @retval);
SELECT @retval;

-- test - should return 'ERROR_ANOTHER_USER' - throws error and resets @retval, should
SET @retval := 'UNKNOWN';
CALL sp_insert_values(1, 1, 30, @retval);
SELECT @retval;

-- test - should return 'ERROR_ALREADY_HAS' - throws error and resets @retval
SET @retval := 'UNKNOWN';
CALL sp_insert_values(2, 15, 30, @retval);
SELECT @retval;

Answer 1

It's not because of ROLLBACK. Value is reset because error is thrown later during INSERT. Handling the error inside procedure body (for instance, with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ) will give you what you want. Alternatively, in your case you can just LEAVE inside IF @uid IS NOT NULL THEN block :

CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
proc_body:
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;

            ROLLBACK;
            LEAVE proc_body;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END proc_body;

Answer 2

Plan A: Put it in a MEMORY or MyISAM table.

Plan B: Have two connections; use the second to store the value you don't want rolled back.