Mysql lock error or error?

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 don't lock the table. pd: This is just an exam simplifying the question .. in real life there is DELETE ...

Answer 1

when you use lock tables, you need to lock all tables in the query. When you use a subquery, it creates a table. and you do not block it. because of this you get an error.

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

give an alias to the internal table

test 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 do 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

Perhaps you have autocommit = 1 even after committing the release of the table.

try:

 SET AUTOCOMMIT = 0 

before starting a transaction.

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

Answer 3

You are locking tables for WRITE . You need to lock the tables for READ , since you are using a SELECT , which is just read from the tables.

However, you should not lock tables, as this prevents concurrency.

EDIT

You also need to use aliases since you use the same table twice in the query.

i.e.

 LOCK TABLES radcheck AS read1 READ, radcheck AS read2 READ; SELECT * FROM radcheck AS read1 WHERE id NOT IN ( SELECT id FROM radcheck AS read2 WHERE attribute = 'Password' GROUP BY UserName HAVING COUNT(*) > 1 );