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: | |
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 |
[15 Feb 2007 9:12]
ILya Kogan
[15 Feb 2007 10:53]
Valeriy Kravchuk
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.
[15 Feb 2007 16:23]
ILya Kogan
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).
[19 Feb 2007 13:42]
Konstantin Osipov
This is a consequence of pre-locking algorithm used for locking of stored functions and triggers. I can't find it documented anywhere.
[19 Feb 2007 14:11]
ILya Kogan
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
[4 May 2007 7:37]
ILya Kogan
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
[4 May 2007 7:44]
ILya Kogan
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