MySQL Bugs: #52296: LOCK TABLES, if using Subfunction mysql say table was not locked

Bug #52296	LOCK TABLES, if using Subfunction mysql say table was not locked
Submitted:	23 Mar 2010 9:21	Modified:	26 Mar 2010 18:25
Reporter:	Richard Teubel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.41, 5.1.46-bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, lock tables, SUBFUNCTION

Description:
I lock a table and select this table in a function. That work trouble-free but if I use this function in an other function mysql get an error and say that the table was not locked.

LOCK TABLE user write;
SELECT get_Name(1); <- ok
SELECT get_eMail(1); <- ok

SELECT get_NameAndMail(1); <- not ok
ERROR 1100 (HY000): Table 'user' was not locked with LOCK TABLES

The content of get_NameAndMail() is only 
RETURN CONCAT_WS(' - ', get_Name(p_iUserID), get_eMail(p_iUserID));

See "How to repeat"

How to repeat:
CREATE DATABASE lock_bug;
use lock_bug;

CREATE TABLE `user` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) COLLATE latin1_german2_ci DEFAULT NULL,
  `eMail` varchar(50) COLLATE latin1_german2_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO `user` (`ID`, `Name`, `eMail`) VALUES
(1, 'Hans', 'Hans@example.com'),
(2, 'Peter', 'Peter@example.com'),
(3, 'Paul', 'Paul@example.com');

DELIMITER //;

CREATE FUNCTION `get_Name`(
        p_iUserID INTEGER(11)
    )
    RETURNS varchar(20) CHARSET latin1
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
  DECLARE f_sReturn VARCHAR(20);
  
  SELECT Name
  INTO f_sReturn
  FROM user
  WHERE ID = p_iUserID;
  
  RETURN f_sReturn;
END; //;

CREATE FUNCTION `get_eMail`(
        p_iUserID INTEGER(11)
    )
    RETURNS varchar(50) CHARSET latin1
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
  DECLARE f_sReturn VARCHAR(20);
  
  SELECT eMail
  INTO f_sReturn
  FROM user
  WHERE ID = p_iUserID;
  
  RETURN f_sReturn;
END; //;

CREATE FUNCTION `get_NameAndMail`(
        p_iUserID INTEGER(11)
    )
    RETURNS varchar(70) CHARSET latin1
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
  RETURN CONCAT_WS(' - ', get_Name(p_iUserID), get_eMail(p_iUserID));
END; //;

DELIMITER ;

LOCK TABLES user WRITE;

SELECT get_Name(1);
+-------------+
| get_Name(1) |
+-------------+
| Hans        |
+-------------+
1 row in set (0.00 sec)

SELECT get_eMail(1);
+------------------+
| get_eMail(1)     |
+------------------+
| Hans@example.com |
+------------------+
1 row in set (0.00 sec)

   SELECT get_NameAndMail(1);
   ERROR 1100 (HY000): Table 'user' was not locked with LOCK TABLES

UNLOCK TABLES;

SELECT get_NameAndMail(1);
+-------------------------+
| get_NameAndMail(1)      |
+-------------------------+
| Hans - Hans@example.com |
+-------------------------+
1 row in set (0.01 sec)

SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.41-log |
+------------+

Thank you for the problem report. Verified just as described with recetn 5.1.46 from bzr on Linux:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE lock_bug;
Query OK, 1 row affected (0.07 sec)

mysql> use lock_bug;
Database changed
mysql>
mysql> CREATE TABLE `user` (
    ->   `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `Name` varchar(20) COLLATE latin1_german2_ci DEFAULT NULL,
    ->   `eMail` varchar(50) COLLATE latin1_german2_ci DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO `user` (`ID`, `Name`, `eMail`) VALUES
    -> (1, 'Hans', 'Hans@example.com'),
    -> (2, 'Peter', 'Peter@example.com'),
    -> (3, 'Paul', 'Paul@example.com');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> DELIMITER //;
mysql>
mysql> CREATE FUNCTION `get_Name`(
    ->         p_iUserID INTEGER(11)
    ->     )
    ->     RETURNS varchar(20) CHARSET latin1
    ->     DETERMINISTIC
    ->     READS SQL DATA
    ->     SQL SECURITY INVOKER
    ->     COMMENT ''
    -> BEGIN
    ->   DECLARE f_sReturn VARCHAR(20);
    ->
    ->   SELECT Name
    ->   INTO f_sReturn
    ->   FROM user
    ->   WHERE ID = p_iUserID;
    ->
    ->   RETURN f_sReturn;
    -> END; //;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE FUNCTION `get_eMail`(
    ->         p_iUserID INTEGER(11)
    ->     )
    ->     RETURNS varchar(50) CHARSET latin1
    ->     DETERMINISTIC
    ->     READS SQL DATA
    ->     SQL SECURITY INVOKER
    ->     COMMENT ''
    -> BEGIN
    ->   DECLARE f_sReturn VARCHAR(20);
    ->
    ->   SELECT eMail
    ->   INTO f_sReturn
    ->   FROM user
    ->   WHERE ID = p_iUserID;
    ->
    ->   RETURN f_sReturn;
    -> END; //;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE FUNCTION `get_NameAndMail`(
    ->         p_iUserID INTEGER(11)
    ->     )
    ->     RETURNS varchar(70) CHARSET latin1
    ->     DETERMINISTIC
    ->     READS SQL DATA
    ->     SQL SECURITY INVOKER
    ->     COMMENT ''
    -> BEGIN
    ->   RETURN CONCAT_WS(' - ', get_Name(p_iUserID), get_eMail(p_iUserID));
    -> END; //;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> LOCK TABLES user WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT get_Name(1);
+-------------+
| get_Name(1) |
+-------------+
| Hans        |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT get_eMail(1);
+------------------+
| get_eMail(1)     |
+------------------+
| Hans@example.com |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT get_NameAndMail(1);
ERROR 1100 (HY000): Table 'user' was not locked with LOCK TABLES
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT get_NameAndMail(1);
+-------------------------+
| get_NameAndMail(1)      |
+-------------------------+
| Hans - Hans@example.com |
+-------------------------+
1 row in set (0.00 sec)

You need to look the table with two aliases if it's used in two functions.
See the manual for LOCK TABLES:
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
--quote
You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias
--end quote

And by the way, if you are using tables inside a stored function, LOCK TABLES Is completely redundant -- MySQL will automatically pre-lock all used tables at start of the statement for you anyway.