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: | |
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 |
[23 Mar 2010 9:21]
Richard Teubel
[23 Mar 2010 9:35]
Valeriy Kravchuk
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)
[26 Mar 2010 18:25]
Konstantin Osipov
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
[26 Mar 2010 18:28]
Konstantin Osipov
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.