大量locked进程中，找出哪条SQL语句locked致使其他语句waiting

大量locked进程中，找出哪条SQL语句locked致使其他语句waiting

1.创建一个测试表

mysql> use test

mysql> create table admin (

    -> username varchar(35) not null unique,

    -> password varchar(64) not null);

2.创建长执行进程

mysql> select sleep(10000) from admin;

3.查看进程

mysql> show full processlist\G

*************************** 2. row ***************************

     Id: 43803

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: NULL

   Info: show full processlist

*************************** 3. row ***************************

     Id: 43806

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 61

  State: User sleep

   Info: select sleep(10000) from admin

4.插入测试数据

mysql> insert into admin values ('e','555');

Query OK, 1 row affected (0.00 sec)

5.删除测试数据

mysql> delete from test.admin where username='e';

6.再次插入测试数据

mysql> insert into test.admin values ('f','666');

7.查询测试数据

mysql> select * from test.admin;

8.查看进程

mysql> show full processlist\G

*************************** 2. row ***************************

     Id: 43803

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 1000

  State: Locked

   Info: delete from test.admin where username='e'

*************************** 3. row ***************************

     Id: 43806

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 1187

  State: User sleep

   Info: select sleep(10000) from admin

*************************** 4. row ***************************

     Id: 43824

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 448

  State: Locked

   Info: insert into test.admin values ('f','666')

*************************** 5. row ***************************

     Id: 43830

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 212

  State: Locked

   Info: select * from test.admin

*************************** 6. row ***************************

     Id: 43837

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: NULL

   Info: show full processlist、

9.开启mysql服务器写入调试信息

# /usr/local/mysql/bin/mysqladmin debug

10.查看mysql错误日志

# tail 132file.err 

Thread  database.table_name        Locked/Waiting      Lock_type

43803   test.admin                Waiting - write       High priority write lock

43806   test.admin                Locked - read        Low priority read lock

43830   test.admin                Waiting - read        Low priority read lock

进程ID43806锁定了表test.admin，阻塞了进程ID43803，43830的执行，是select sleep(10000) from admin这个SQL语句。

©著作权归作者所有：来自51CTO博客作者xhh_0168的原创作品，如需转载，请注明出处，否则将追究法律责任

mysqlsqlsleepMySQL