CODEWITHSUNDEEP
mysqllocking
Emiliano Gustavo Nuñez
Emiliano Gustavo Nuñez

Reputation: 815

mysql lock error or bug?

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

Upvotes: 6

Views: 17348

Answers (3)

Ed Heal
Ed Heal

Reputation: 59987

You are locking tables for WRITE. You need to lock tables for READ as you are using SELECT that just reads from the tables.

However you should not really be locking tables as this prevents concurrency.

EDIT

You also need to use aliases as you are using 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
   );

Upvotes: 0

Nesim Razon
Nesim Razon

Reputation: 9794

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

Upvotes: 12

jcho360
jcho360

Reputation: 3759

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

Upvotes: 1

Related Questions