CODEWITHSUNDEEP
mysqlstored-procedures
James Barton
James Barton

Reputation: 23

MySQL GET_LOCK() not failing when it should

MySQL version = 5.7.16

I need to automatically process some rows of data in the DB. I have a queue of primary keys for the table, and three pieces of code that should work together:

  1. An event that fires every ten seconds, which calls:
  2. A controlling procedure which selects individual rows from the queue table, and passes them to:
  3. A row-level procedure which performs the business logic on individual rows of the data

Often, the amount of work to be done takes much longer than ten seconds to complete, so the event will fire again before the procedure it invokes has completed. This makes the processes contend for the same rows, so I don't want that to happen.

I've wrapped basically everything in the controlling procedure with an if (get_lock()) statement:

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 ;

What I hoped would happen is that, if an instance of controlling_procedure is already running, then any new instance of the same procedure would fail to get the lock, and exit without reading from the queue table or calling row_level_procedure.

But when I look in Workbench's Client Connections screen, I can see an increasing number of connections, all with their Info value set to:

call schema.row_level_procedure(thePrimaryKey);

New connections appear in the table with the frequency dictated by the event (I've experimented with the event schedule).

It looks like the if (get_lock)) test is always passing, even when other instances of the same controlling procedure are already running.

What have I misunderstood, or done wrong?

Upvotes: 1

Views: 847

Answers (2)

James Barton
James Barton

Reputation: 23

The problem was this code:

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

Individual invocations of the row-level procedure normally worked (over 95% of the time), but some threw exceptions. The handler above caught the exception, released the lock, and continued. Because the lock was now available, the next time the event fired, the controlling procedure that it invoked could get the lock, and now there was another controlling procedure running. Eventually it too might give up the lock, allowing another procedure to run.

What should happen instead is to have the handler catch the exception, release the lock, and terminate, rather than continue, or simply not release the lock while running.

Upvotes: 0

wchiquito
wchiquito

Reputation: 16551

I can't reproduce the problem with a simple example:

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)

Upvotes: 1

Related Questions