Bug #31080 LOCK TABLES + subqueries: Table 'test' was not locked with LOCK TABLES
Submitted: 18 Sep 2007 14:12 Modified: 18 Sep 2007 15:24
Reporter: Martin Huber Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.45 OS:Windows (2000 Server)
Assigned to: CPU Architecture:Any
Tags: lock tables, locking, subqueries

[18 Sep 2007 14:12] Martin Huber 
Description:
If I lock a table with LOCK TABLES and make a SELECT query with a subquery I get the error "Table 'test' was not locked with LOCK TABLES" although I have locked the table.

A workaround is to set an alias table name in the subquery and lock also the alias table:

LOCK TABLES test READ, test_2 READ;
SELECT * FROM test WHERE id IN
(
SELECT id FROM test AS test_2 WHERE id < 5
);
UNLOCK TABLES;

How to repeat:
LOCK TABLES test READ;
SELECT * FROM test WHERE id IN
(
SELECT id FROM test WHERE id < 5
);
UNLOCK TABLES;
[18 Sep 2007 15:24] MySQL Verification Team 
Thank you for the bug report. Please read the Manual:

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

"You cannot use a locked table multiple times in a single query. Use aliases instead, in which case you must obtain a lock for each alias separately:"