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

Question

MySQL版本= 5.7.16

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

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

通常，要完成的工作量要比完成的时间长得多，因此在调用过程完成之前，事件将再次触发。这使得进程争夺相同的行，所以我不希望这种情况发生。

基本上，我用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的客户端连接屏幕时，我可以看到越来越多的连接，它们的Info值都设置为：

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)

Answer 2

问题是这样的代码：

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

行级过程的个别调用通常正常工作(超过95%的时间)，但有些调用会抛出异常。上面的处理程序捕获了异常，释放了锁，然后继续。由于锁现在可用，下次事件触发时，它调用的控制过程可以获得锁，现在又有另一个控制过程在运行。最终，它可能也会放弃锁，允许运行另一个过程。

相反，应该让处理程序捕获异常、释放锁并终止，而不是继续，或者在运行时根本不释放锁。