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