Bug #5010 Problem with LOCK TABLES and subqueries

• Submitted: 12 Aug 2004 2:32
• Modified: 13 Aug 2004 8:31
• Reporter: Artur Miarecki
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.3-beta-standard
• OS: Linux (Redhat Linux 9)
• Assigned to: Timour Katchaounov
• CPU Architecture: Any

[12 Aug 2004 2:32] Artur Miarecki

Description:
I observed an interesting phenomena (InnoDB engine). In code similar to this:

lock tables T1 write, T2 write, T3 write
select * from T1 where (....) and T1.f1 in (select T2.f2 from T2 where (....))

an error occurs:
"Table `T2` was not locked with LOCK TABLES"

Why? It looks, that using subqueries unlocks tables, like another LOCK TABLES or UNLOCK TABLES.

How to repeat:
descripted in previous section.

Suggested fix:
?

[12 Aug 2004 2:35] Artur Miarecki

a little correction - SQL query causing error looks as below:

lock tables T1 write, T2 write, T3 write
select * from T1,T2 where T1.r1=T2.r2 and (....) and T1.f1 in (select T2.f2 from T2 where (....))

Last time I forgot to bind a relation to T2 in selects fields place.

[12 Aug 2004 3:56] Matthew Lord

This is not innodb specific.  I was able to repeat the problem using myisam tables.
--using our world db

root@192.168.1.28:world~> lock tables city read;
Query OK, 0 rows affected (0.00 sec)

root@192.168.1.28:world~> select * from city where id in (select id from city where 
district='New York');
ERROR 1100 (HY000): Table 'city' was not locked with LOCK TABLES

[13 Aug 2004 8:31] Timour Katchaounov

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Section 14.4.5 LOCK TABLES and UNLOCK TABLES Syntax
  (at http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html)
says:

"Also, you cannot use a locked table multiple times in one
 query - use aliases for that. Note that in that case you
 must get a lock for each alias separately."

According to this suggestion the problematic statements:

  lock tables T1 write, T2 write, T3 write;
  select * from T1, T2
  where T1.r1 = T2.r2 and
        T1.f1 in (select T2.f2 from T2 where T2.r2 > 0);

and 

  lock tables city read;
  select * from city
  where id in (select id from city where district='New York');

can be rewritten into:

  lock tables T1 write, T2 write, T2 as T2_positive write, T3 write;
  select * from T1, T2
  where T1.r1 = T2.r2 and
        T1.f1 in (select T2.f2 from T2 as T2_positive where T2.r2 > 0);

and

  lock tables city read, city as nwcity read;
  select * from city
  where id in (select id from city as nwcity where district='New York');

which work fine.