CODEWITHSUNDEEP
sqlmysql
richs
richs

Reputation: 4769

mysql Stored Procedure not releasing lock

I have a store procedure that gets a lock, runs a select, does some simple processing and runs an insert. This has worked fine for over a year but today every so often a connection will hold the lock and not release it until the lock times out. So if i use the 

select IS_USED_LOCK('up_XML_insertUIAudit_lock');

I can determine what connection is holding the lock and kill it. Any ideas what might cause this?

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 ;

Upvotes: 1

Views: 616

Answers (1)

longneck
longneck

Reputation: 12226

if the procedure errors before RELEASE_LOCK(), it will not be released.

why not just use transactions instead?

Upvotes: 1

Related Questions