mysql lock error or bug?

Question

here we go:

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

mysql> SELECT * FROM radcheck WHERE id NOT IN (
    ->     SELECT id FROM (
    ->         SELECT id FROM radcheck  WHERE attribute = 'Password' GROUP BY UserName HAVING COUNT(*) > 1
    ->      ) AS c
    -> );
ERROR 1100 (HY000): Table 'radcheck' was not locked with LOCK TABLES

WTF?

EDIT

SET AUTOCOMMIT = 0
    -> ;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT * FROM radcheck WHERE id NOT IN ( SELECT id FROM radcheck  WHERE attribute = 'Password' GROUP BY UserName HAVING COUNT(*) > 1 );
ERROR 1100 (HY000): Table 'radcheck' was not locked with LOCK TABLES



mysql> LOCK TABLES radcheck READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM radcheck WHERE id NOT IN ( SELECT id FROM radcheck  WHERE attribute = 'Password' GROUP BY UserName HAVING COUNT(*) > 1 );
ERROR 1100 (HY000): Table 'radcheck' was not locked with LOCK TABLES

pd: The query works fine if I not lock the table. pd: This is only an examply ti simplify the question.. in real life is an DELETE...

Answer 1

when you use lock tables, you need to lock all tables in your query. When you use a subquery it creates a table. and you are not locking it. because of that you are getting error.

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

give an alias to inner table

tested sample:

lock tables products as p1 write, products as p2 write ;
select product_id  from products as p1
where product_id  not in ( 
select product_id from products p2 where product_id in (1,2)
) 

And probably you need to this:

lock tables radcheck as r1 write, radcheck as r2 write ;

 SELECT * FROM radcheck r1 WHERE id NOT IN (
SELECT id FROM (
  SELECT id FROM radcheck r2 WHERE attribute = 'Password' GROUP BY UserName HAVING COUNT(*) > 1) AS c
 );

Answer 2

Probably you have autocommit = 1 and after commit release the tables.

try with:

SET AUTOCOMMIT = 0

before to start your transaction.

http://dev.mysql.com/doc/refman/5.0/es/innodb-and-autocommit.html