大量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
共同學習，寫下你的評論
評論加載中...
作者其他優質文章
