Bug #11703 Incorrect locking in nested stored procedures
Submitted: 2 Jul 2005 16:56 Modified: 2 Jul 2005 19:12
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.7 OS:Windows (WinXP Pro)
Assigned to: CPU Architecture:Any

[2 Jul 2005 16:56] [ name withheld ]
Description:
Similar to bugs #9565 and #11302.
The following gives an error:
"ERROR 1099 (HY000): Table 'Test' was locked with a READ lock and can't be update"

DELIMITER $

DROP TABLE IF EXISTS test$

CREATE TABLE test (
  id INT,
  descr varchar(20)
)$

INSERT INTO test(id, descr)
VALUES
(1, 'aaa'),
(2, 'bbb'),
(3, 'ccc')$

DROP PROCEDURE IF EXISTS CheckTest$

CREATE PROCEDURE CheckTest(IN TestID INT, OUT error INT)
CheckTest:BEGIN
  IF NOT EXISTS(SELECT id FROM test WHERE id = TestID) THEN
    SELECT error = 1;
    LEAVE CheckTest;
  END IF;
END$

DROP PROCEDURE IF EXISTS TestNoDups$

CREATE PROCEDURE TestNoDups(IN TestID INT, IN TestDescr VARCHAR(20), out error INT)
TestNoDups:BEGIN
IF EXISTS (SELECT * 
           FROM test
           WHERE id = TestID
           -- comment the following line out and it works
           AND descr = TestDescr
           ) THEN
       SET error = 1;
       LEAVE TestNoDups;
  END IF;      
END$

DROP PROCEDURE IF EXISTS TestUpdate$

CREATE PROCEDURE TestUpdate(IN TestID INT, IN TestDescr VARCHAR(20), out error INT)
TestUpdate:BEGIN
  -- comment the following line out and it works
  call CheckTest(TestID, error);
  if (error <> 0 ) THEN
    leave TestUpdate;
  END IF;
  
  CALL TestNoDups(TestID, TestDescr, error);
  IF (error <> 0 ) THEN
    LEAVE TestUpdate;
  END IF;

  UPDATE Test
  SET descr = TestDescr
  WHERE id = TestID;
END$

DELIMITER ;

CALL TestUpdate(3, 'ddd', @error);

ERROR 1099 (HY000): Table 'Test' was locked with a READ lock and can't be update

When you comment out either
"and descr = TestDescr"
or 
"call CheckTest(TestID, error);"
It does not throw the error and works as expected (except, of course, that it does not do the checks that were commented out).

This is an issue with nesting stored procedures, because when you perform the statements of CheckTest and TestNo Dups in TestUpdate, it works as expected.  For instance, the following does not throw an error:

DELIMITER $

DROP TABLE IF EXISTS test$

CREATE TABLE test (
  id INT,
  descr varchar(20)
)$

INSERT INTO test(id, descr)
VALUES
(1, 'aaa'),
(2, 'bbb'),
(3, 'ccc')$

DROP PROCEDURE IF EXISTS TestUpdate$

CREATE PROCEDURE TestUpdate(IN TestID int, IN TestDescr varchar(20), out error int)
TestUpdate:BEGIN
  IF NOT EXISTS(SELECT id FROM test WHERE id = TestID) THEN
    SELECT error = 1;
    LEAVE TestUpdate;
  END IF;
  
 IF EXISTS (SELECT * 
           FROM test
           WHERE id = TestID
           AND descr = TestDescr
           ) THEN
       SET error = 1;
       LEAVE TestUpdate;
 END IF;

  UPDATE Test
  SET descr = TestDescr
  WHERE id = TestID;
END$

DELIMITER ;

CALL TestUpdate(3, 'ddd', @error);

How to repeat:
As above
[2 Jul 2005 19:12] MySQL Verification Team
Thank you for the bug report. It is duplicate as you mentioned.

mysql> DELIMITER ;
mysql>
mysql> CALL TestUpdate(3, 'ddd', @error);
ERROR 1099 (HY000): Table 'Test' was locked with a READ lock and can't be updated
mysql>
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.9-beta-nt |
+---------------+
1 row in set (0.01 sec)