MySQL Bugs: #26396: Stored function locks MyISAM table

Bug #26396	Stored function locks MyISAM table
Submitted:	15 Feb 2007 9:12	Modified:	4 May 2007 7:44
Reporter:	ILya Kogan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.1.x tested	OS:	Linux (Linux, Windows XP Professional)
Assigned to:		CPU Architecture:	Any
Tags:	lock, stored function, trigger

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