MySQL GET_LOCK() 在应该的时候没有失败

Question

MySQL 版本 = 5.7.16

我需要自动处理数据库中的一些数据行。我有一个表的主键队列，以及应该协同工作的三段代码:

1. 每十秒触发一次的事件，调用:
2. 一个控制程序，它从队列表中选择单独的行，并将它们传递给:
3. 行级过程，对数据的各个行执行业务逻辑

通常，要完成的工作量远远超过 10 秒才能完成，因此该事件将在它调用的过程完成之前再次触发。这使得进程争用相同的行，所以我不希望这种情况发生。

我基本上用 if (get_lock()) 语句包装了控制过程中的所有内容:

drop procedure if exists schema.controlling_procedure;

delimiter $$
create procedure schema.controlling_procedure()
begin
  declare lnRowsToProcess int default 0;

  declare continue handler for sqlexception
  begin
    do release_lock('controlling_procedure');
  end;

  if (get_lock('controlling_procedure',1)) then

    select count(*)
    into   lnRowsToProcess
    from   vcs_raw.sys_pfq_1;

    if (lnRowsToProcess > 0) then
      begin
        ...
        declare zzzzzz
        ...    

        read_loop: loop
          select min(primary_key)
          into   thePrimaryKey
          from   vcs_raw.sys_pfq_1;

          if (thePrimaryKey is null)then
            leave read_loop;
          end if;

          call schema.row_level_procedure(thePrimaryKey);

          delete
          from  vcs_raw.sys_pfq_1
          where job_id = thePrimaryKey;

          set thePrimaryKey = null;
        end loop;
      end;
    end if;
  end if;

  do release_lock('controlling_procedure');
end$$
DELIMITER ;

我希望发生的是，如果 controlling_procedure 的一个实例已经在运行，那么同一过程的任何新实例都将无法获得锁，并在不从队列表读取或调用 row_level_procedure 的情况下退出。

但是当我查看 Workbench 的客户端连接屏幕时，我可以看到越来越多的连接，所有连接的信息值都设置为:

call schema.row_level_procedure(thePrimaryKey);

新连接以事件指定的频率出现在表中(我已经试验过事件时间表)。

看起来 if (get_lock)) 测试总是通过，即使同一控制过程的其他实例已经在运行。

我误解了什么或做错了什么？

Answer 1

我无法用一个简单的例子重现这个问题:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> CREATE TABLE IF NOT EXISTS `tbl_attempts` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `connection_id` BIGINT UNSIGNED,
    ->   `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl_test` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `sp_test`()
    -> BEGIN
    ->   INSERT INTO `tbl_attempts` (`connection_id`) VALUES (CONNECTION_ID());
    ->   IF (GET_LOCK('controlling_procedure', 0)) THEN
    ->     DO BENCHMARK(35000000, AES_ENCRYPT('hello', 'goodbye'));
    ->     DO RELEASE_LOCK('controlling_procedure');
    ->     INSERT INTO `tbl_test` (`id`) VALUES (NULL);
    ->   END IF;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CREATE EVENT `evt_test` ON SCHEDULE EVERY 1 SECOND
    -> STARTS CURRENT_TIMESTAMP
    -> ENDS CURRENT_TIMESTAMP + INTERVAL 10 SECOND
    -> ON COMPLETION PRESERVE
    ->   DO CALL `sp_test`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `connection_id`, `created_at`
    -> FROM `tbl_attempts`;
+----+---------------+---------------------+
| id | connection_id | created_at          |
+----+---------------+---------------------+
|  1 |            62 | 2010-01-01 00:00:17 |
|  2 |            63 | 2010-01-01 00:00:18 |
|  3 |            64 | 2010-01-01 00:00:19 |
|  4 |            65 | 2010-01-01 00:00:20 |
|  5 |            66 | 2010-01-01 00:00:21 |
|  6 |            67 | 2010-01-01 00:00:22 |
|  7 |            68 | 2010-01-01 00:00:23 |
|  8 |            69 | 2010-01-01 00:00:24 |
|  9 |            70 | 2010-01-01 00:00:25 |
| 10 |            71 | 2010-01-01 00:00:26 |
| 11 |            72 | 2010-01-01 00:00:27 |
+----+---------------+---------------------+
11 rows in set (0.00 sec)

mysql> SELECT `id`, `created_at`
    -> FROM `tbl_test`;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2010-01-01 00:00:26 |
|  2 | 2010-01-01 00:00:35 |
+----+---------------------+
2 rows in set (0.00 sec)