MySQL Bugs: #26396: Stored function locks MyISAM table

Bug #26396	Stored function locks MyISAM table
Submitted:	15 Feb 2007 10:12	Modified:	4 May 2007 9:44
Reporter:	ILya Kogan
Status:	Closed
Category:	Server: SP	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.1.x tested	OS:	Linux (Linux, Windows XP Professional)
Assigned to:		Target Version:
Tags:	lock, trigger, stored function

Description:
If stored function/trigger has some data modify statement, MySql locks table till the end
of stored function/trigger (not procedure). No transaction has been used. It looks like
function start a implicit transaction, with high isolation level

How to repeat:
-- db objects

CREATE TABLE t1 (
  ID INT(11) AUTO_INCREMENT DEFAULT NULL,
  PRIMARY KEY (ID)
)
AUTO_INCREMENT=1
/

FUNCTION `dead_func`()
  RETURNS int(11)
begin 
  INSERT INTO t1 VALUES(1);
  do sleep(20);
  return 1;
end;
/

PROCEDURE cool_proc()
BEGIN
  INSERT INTO t1 VALUES(2);
  do sleep(20);
END;
/

-- situation1. Locking table 
-- thread1
select dead_func();

-- thread2
select * from t1; # suspended till the end of dead_func

-- situation2. Everithing is ok
-- thread1
call cool_proc();

-- thread2 
select * from t1; # query is executed immediately. Value 2 is already in t1

Thank you for a problem report. Verified just as described with latest 5.0.36-BK on Linux,
with t1 created as MyISAM (!) table. If InnoDB storage engine is used, table is NOT locked
(expected result). 

May be related to bug #26141 also.

Yes, the problem is reproduced in triggers too. InnoDb table is not locked, but rows of
InnoDb table are still locked for update and invisible for select(handler).

This is a consequence of pre-locking algorithm used for locking of stored functions and
triggers. I can't find it documented anywhere.

2Konstantin Osipov: Me too. Is there a chance that this behaviour will change in the
future releases. Actually an implicit transaction is started before function/trigger
execution. I had some practice with using SET SESSION TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED before function call, to perform dirty read from tables, wich are updated by
function. But I still cannot modify data in this tables before function exits. This is a
really serious problem for my system. Because functions can be suspended for a long time,
waiting for event

Hi. I think the bug is closed. I found another bug request with the same problem and this
bug has been already fixed in version  5.0.30, 5.1.13 by Heikki Tuuri. You can see a wide
discussion at http://bugs.mysql.com/bug.php?id=18077

PS.
  I tested the situation on 5.1.17. Everything is ok in case of using InnoDB tables.
MySAM tables are locked as Valeriy Kravchuk said, because of table-level lock during
transaction