Bug #60460 Locked threads are not killed with 'KILL <process_id>'
Submitted: 14 Mar 2011 12:37 Modified: 15 Mar 2011 8:57
Reporter: Ashwin kumar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.53 OS:Any
Assigned to: CPU Architecture:Any
Tags: thread locking, write lock

[14 Mar 2011 12:37] Ashwin kumar
Description:
I have a table described below:

CREATE TABLE `u` (
  `t` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8

and sample data as 1;2;3;4 for each insert.

Ok, now I make an another connection using the CLI and execute
LOCK TABLES u WRITE;

Executes successfully. Then I get back to my another connection (from CLI) and execute
SELECT * FROM u;

Obviously, the tables are locked and hence the select thread goes to a 'Locked' state.

Now I execute 
SHOW FULL PROCESSLIST;
and I will get the information on all the running threads.

Good!! 

I find one of the thread is in 'Locked' state and I know its process id.

So, I execute 
KILL <process-id>;

It executes successfully. 

But when I execute 
SHOW FULL PROCESSLIST;
I can see the 'Locked' thread again with different process-id.

My questions?
1. Why are their two 'Locked' threads?
2. Why does KILL command cannot kill the thread at once?

How to repeat:
Simple, Follow the steps above... :-)

Suggested fix:
I do not know the code internals of this kind of execution.

Pls explain me what is the cause for the problem I see.
[14 Mar 2011 12:45] Ashwin kumar
Edited Synopsis of the report.
[14 Mar 2011 13:25] Ashwin kumar
FYI, I am using the built-in InnoDB plug-in that is shipped with the MSI Installer package.
[14 Mar 2011 13:46] Peter Laursen
I think I confused Ashwin (he is a colleague of mine).

It is tested with the (default) 'built-in' InnoDB - and not the (non-default) 'plugin' InnoDB code.  We do not know yet it it matters or if same is reproducible with MyISAM. We are checking MySQL 5.5 now.
[14 Mar 2011 14:40] Valeriy Kravchuk
Please, check with a newer version, 5.1.56. 

This is what I see with current mysql-5.1 and built-in InnoDB engine:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `u` (    ->   `t` INT(11) DEFAULT NULL
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into u values (1), (2), (3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> lock tables u write;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;+----+------+-----------+------+---------+------+--------+------------------+
| Id | User | Host      | db   | Command | Time | State  | Info             |
+----+------+-----------+------+---------+------+--------+------------------+
|  1 | root | localhost | test | Query   |    0 | NULL   | show processlist |
|  2 | root | localhost | test | Query   |   10 | Locked | SELECT * FROM u  |
+----+------+-----------+------+---------+------+--------+------------------+
2 rows in set (0.00 sec)

mysql> kill 2;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | test | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Maybe I miss something, but I do not see any problem with killing thread in the Locked state.
[15 Mar 2011 8:57] Ashwin kumar
This is not an issue, the actual client re-connected and re-executed the statement.