MySQL: Procedure calls within a transaction are causing commits

Question

I have a transaction which calls a generated procedure, however if an error occurs after the procedure I notice that the statements during and before the procedure have been committed. Here is the procedure:

DELIMITER $$    
CREATE PROCEDURE location_processor(in _city varchar(20), in _country_code varchar(2), out id int)
    begin
        select location_id into id from location where city = _city and country_code = _country_code limit 0,1;
        if id is null then
            select @id := max(location_id) from location;
            if @id is null then
                set @id = 0;
            end if;
            set @id = @id + 1;
            insert into location (location_id, city, country_code)
            values(@id, _city, _country_code);
            set id = @id;
        end if;
    end; $$
DELIMITER ;

Note: there is no start/end transaction syntax used within this procedure; although I have reason to believe that begin and end of the procedure itself is causing commit as:

Note:

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.

(https://dev.mysql.com/doc/refman/5.7/en/commit.html)

I need this procedure for error checking purposes. Is there anyway to avoid committing within a transaction whilst using a procedure within it?

wchiquito wchiquito · Accepted Answer · 2017-04-16T20:46:32

With a simple example, I can't reproduce the problem:

mysql> DROP PROCEDURE IF EXISTS `location_processor`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `location`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `location` (
    ->     `location_id` INT,
    ->     `city` VARCHAR(255),
    ->     `country_code` VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `location_processor`()
    -> BEGIN
    ->     INSERT INTO `location`
    ->         (`location_id`, `city`, `country_code`)
    ->     VALUES
    ->         (2, 'city', 'country_code');
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `location_id`, `city`, `country_code`
    -> FROM `location`;
Empty set (0.00 sec)

mysql> INSERT INTO `location`
    ->     (`location_id`, `city`, `country_code`)
    -> VALUES
    ->     (1, 'city', 'country_code');
Query OK, 1 row affected (0.00 sec)

mysql> CALL `location_processor`;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `location`
    ->     (`location_id`, `city`, `country_code`)
    -> VALUES
    ->     (3, 'city', 'country_code');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `location_id`, `city`, `country_code`
    -> FROM `location`;
+-------------+------+--------------+
| location_id | city | country_code |
+-------------+------+--------------+
|           1 | city | country_code |
|           2 | city | country_code |
|           3 | city | country_code |
+-------------+------+--------------+
3 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `location_id`, `city`, `country_code`
    -> FROM `location`;
Empty set (0.00 sec)

MySQL: Procedure calls within a transaction are causing commits

1 Answers