SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'\p;

DROP DATABASE IF EXISTS lock_test_db;
CREATE DATABASE lock_test_db;
USE lock_test_db;


CREATE TABLE `agent` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `agent_guid` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6198 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO agent (id, agent_guid) VALUES (1, 'guid 1');
INSERT INTO agent (id, agent_guid) VALUES (2, 'guid 1');
INSERT INTO agent (id, agent_guid) VALUES (3, 'guid 2');
INSERT INTO agent (id, agent_guid) VALUES (4, 'guid 55');
INSERT INTO agent (id, agent_guid) VALUES (5, 'guid 2');

delimiter $$

CREATE PROCEDURE lock_test
(
    IN  a_agent_id      smallint unsigned
,   IN  a_id_pack       bigint unsigned
)
l_proc:
BEGIN
    IF
        a_id_pack IN
        (
            SELECT id from agent where agent_guid = (SELECT agent_guid FROM agent WHERE id = a_agent_id)
        )
    THEN
      select 1;
    ELSE
      select 0;
    END IF;

END\p$$