Bug #9565 wrong locking in stored procedure if a sub-sequent procedure is called
Submitted: 1 Apr 2005 16:25 Modified: 18 Jul 2005 20:08
Reporter: Jan Kneschke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Linux (Linux/x86)
Assigned to: Dmitry Lenev CPU Architecture:Any

[1 Apr 2005 16:25] Jan Kneschke 
Description:
When a stored procedure is entered the referenced tables are locked for the statements in the SP. 

For a SELECT only a READ lock is taken, for all writing statements a WRITE-LOCK. 

In the case that a procedure is calling another procedure this might result in impossible LOCKing if the call procedure needs a WRITE lock as the READ lock might be already taken the calling procedure.

How to repeat:
DELIMITER $$
DROP TABLE IF EXISTS __test2$$
CREATE TABLE __test2 (
  a INT
)$$
DROP PROCEDURE IF EXISTS test2_sub$$
CREATE PROCEDURE test2_sub()
BEGIN
  INSERT INTO __test2 VALUES ( 1 );
END$$
DROP PROCEDURE IF EXISTS test2$$
CREATE PROCEDURE test2()
BEGIN
  CALL test2_sub();
  SELECT * FROM __test2;
END$$
CALL test2()$$
## ERROR 1099 (HY000): Table '__test2' was locked with a READ lock and can't be updated
DELIMITER ;

Suggested fix:
As workaround it is possible to execute a dummy call to a writing statement which takes the WRITE-lock. But this is nothing for the future.

Fixed the LOCK handling. Either by testing all the lock-requirements of all sub-calls or get the LOCK when you need them.
[1 Jun 2005 0:49] Duke Bitson 
Should the severity of this bug be increased to S2 or S1?  Without a fix there are valid (and I would imagine, common) stored procedures that will not work.
[7 Jun 2005 3:52] Stas Nichiporovich 
Agree.... Without fixing this bug this is impossible to normal use of many common SP.
[15 Jun 2005 9:15] Dmitry Lenev 
Look at bug #11302 (http://bugs.mysql.com/bug.php?id=11302) for yet another
manifestation of this problem (but instead of subsequent routine call it uses statement using view).
[15 Jul 2005 9:36] Dmitry Lenev 
Hi!

This bug was fixed in 5.0.10 by the same patch as bug #9597.
I have added test case for it to our test suite.
[18 Jul 2005 20:08] Paul DuBois 
Noted in 5.0.10 changelog.