MySQL:事务内的过程调用导致提交

Question

我有一个调用生成过程的事务，但是如果在该过程之后发生错误，我会注意到该过程期间和之前的语句已被提交。程序如下:

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:

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 )

我需要此过程来进行错误检查。无论如何，在使用事务中的过程时是否可以避免在事务中提交？

Answer 1

通过一个简单的例子，我无法重现该问题:

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)