sql - mysql存储过程不释放锁

Question

我有一个存储过程，它获取一个锁、运行一个选择、进行一些简单的处理并运行一个插入。这已经运行了一年多，但现在每隔一段时间，一个连接就会持有锁，直到锁超时才释放它。所以如果我使用

select IS_USED_LOCK('up_XML_insertUIAudit_lock');

我可以确定持有锁的连接并将其终止。有什么想法可能导致这种情况吗？

DELIMITER $$

DROP PROCEDURE IF EXISTS `aquadev`.`up_XML_insertUIAudit` $$
CREATE DEFINER=`richard`@`%` PROCEDURE `up_XML_insertUIAudit`(in UserName VARCHAR(45),
  in ActionType VARCHAR(45),
  in Version VARCHAR(45),
  in WaitInterval BIGINT,
  in StartAgainTS DATETIME)
BEGIN
  declare id int;
  declare lockStatus int;
  declare WaitIntervalStr VARCHAR(40);
  declare StartAgainTSStr VARCHAR(19);
  declare Description VARCHAR(255);
  set id = null;
  select GET_LOCK('up_XML_insertUIAudit_lock',600) into lockStatus;

  select max(UIAuditID) into id from UIAudit;
  if (id is not null) then
    set id = id + 1;
  else
    set id = 0;
  end if;

  if (WaitInterval is null) then
   set WaitIntervalStr = '';
  else
   set WaitIntervalStr = convert(WaitInterval,char(40));
  end if;

  if (StartAgainTS is null) then
   set StartAgainTSStr = '';
  else
   set StartAgainTSStr = convert(StartAgainTS,char(19));
  end if;

  set Description = concat(WaitIntervalStr,StartAgainTSStr);

  insert into UIAudit (UIAuditID,UserName,ActionType,Version,ProcessID,Description,UpdateTS)
  values(id,UserName,ActionType,Version,null,Description,now());

  select RELEASE_LOCK('up_XML_insertUIAudit_lock') into lockStatus;

END $$

DELIMITER ;

Answer 1

如果在RELEASE_LOCK()之前程序出错，则不会被释放。

为什么不直接使用事务呢？