mysql锁定错误或错误? [英] mysql lock error or bug?

问题描述

我们去:

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?

编辑

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:如果我不锁定表，则查询工作正常. pd:这只是一个例子，可以简化问题..在现实生活中是DELETE ...

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...

推荐答案

使用锁定表时，需要锁定查询中的所有表. 当您使用子查询时，它将创建一个表.并且您没有锁定它. 因此，您会遇到错误.

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.

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

给内部表一个别名

测试样品:

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)
) 

也许您需要这样做:

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
 );

这篇关于mysql锁定错误或错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！