select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i, (select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p where timestampdiff(second, i.trx_started, now()) > p.time and i.trx_mysql_thread_id not in (connection_id(),p.id);
select id,State,command from information_schema.processlist where State="Waiting for table metadata lock"; select timediff(sysdate(),trx_started) timediff,sysdate(),trx_started,id,USER,DB,COMMAND,STATE,trx_state,trx_query from information_schema.processlist,information_schema.innodb_trx where trx_mysql_thread_id=id; show processlist; select concat('kill ',trx_mysql_thread_id,';') from information_schema.processlist,information_schema.innodb_trx where trx_mysql_thread_id=id and State!="Waiting for table metadata lock";
=============================== mysql> show processlist; +----+------+-----------+-----------+---------+------+---------------------------------+-----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-----------+---------+------+---------------------------------+-----------------------------------------+ | 6 | root | localhost | uplooking | Query | 275 | Waiting for table metadata lock | alter table booboo add q6 int default 0 | | 7 | root | localhost | uplooking | Sleep | 269 | | NULL | | 8 | root | localhost | uplooking | Query | 0 | starting | show processlist | | 10 | root | localhost | uplooking | Sleep | 249 | | NULL | | 12 | root | localhost | uplooking | Sleep | 191 | | NULL | +----+------+-----------+-----------+---------+------+---------------------------------+-----------------------------------------+ 5 rows in set (0.00 sec) # 查看当前进程发现除了Alter之外没有对booboo表的操作
mysql> select timediff(sysdate(),trx_started) timediff,sysdate(),trx_started,id,USER,DB,COMMAND,STATE,trx_state,trx_query from information_schema.processlist,information_schema.innodb_trx where trx_mysql_thread_id=id; +----------+---------------------+---------------------+----+------+-----------+---------+---------------------------------+-----------+-----------------------------------------+ | timediff | sysdate() | trx_started | id | USER | DB | COMMAND | STATE | trx_state | trx_query | +----------+---------------------+---------------------+----+------+-----------+---------+---------------------------------+-----------+-----------------------------------------+ | 00:05:38 | 2017-08-18 20:21:07 | 2017-08-18 20:15:29 | 6 | root | uplooking | Query | Waiting for table metadata lock | RUNNING | alter table booboo add q6 int default 0 | | 00:05:38 | 2017-08-18 20:21:07 | 2017-08-18 20:15:29 | 10 | root | uplooking | Sleep | | RUNNING | NULL | +----------+---------------------+---------------------+----+------+-----------+---------+---------------------------------+-----------+-----------------------------------------+ 2 rows in set (0.00 sec) # 查看innodb_trx表可以看到除了alter之外有未完成的事务，但是看不到具体query，得到线程id为10 # 就可以kill 10来结束事务 # 之后Alter正常操作
也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志
解决方法：确认有错误事务未提交或回滚，找到该事务的sessionid然后杀死（难点）
# 场景3的出现和前两种不同 # 查看线程情况，看到alter操作metadata锁，还有其他的select操作有metadata锁 ## 第一反应就是有可能是场景1，于是kill掉执行select的线程，再次查看线程情况，就只剩下执行alter线程了 ## 接下来查看未完成的事务，如果是场景1，在kill掉冲突的线程后应该出现两种情况（A.alter操作正常执行B.线程中只有alter操作为waiting metadata lock状态；未完成事务中存在未完成事务） ## 但是却发现和B情况有所不同的是：未完成事务中不存在未完成事务，总结第三种情况(C.线程中只有alter操作为waiting metadata lock状态；未完成事务中不存在未完成事务） # 通过搜索资料定位到是场景3，但资料中没有说怎么解决问题，又不能重新启动服务器，只有一个资料里提到了方法（确认有错误事务未提交或回滚，找到该事务的sessionid然后杀死，关键就是如何找到sessionid呢？performance_schema.events_statements_current中的thread_id为线程id并不是sessionid或者说会话id、连接id，如何通过thread_id找到session_id成为了难点？5.7中有个session表可以直接查到，而5.6中必须通过三表才能查到，分别为performance_schema.events_statements_current,performance_schema.threads,information_schema.processlist表。） ===================================================================================== #kill掉除了写操作以外的query select concat('kill ',id) from information_schema.processlist where State="Waiting for table metadata lock" and substring(info, 1, 5) not in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat');
# 寻找未提交或未回滚的错误事务，并kill即可 select t.processlist_id,t.processlist_time,e.sql_text from performance_schema.threads t,performance_schema.events_statements_current e where t.thread_id=e.thread_id and e.SQL_TEXT like '%t1%'; # 案例中假设是在t1表上有MDL锁，则，e.sql_text 近似匹配t1 # 本方法5.5 5.6 5.7 都通用。 =============================================================================
mysql> show processlist; +----+------+-----------+-----------+---------+------+---------------------------------+-----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-----------+---------+------+---------------------------------+-----------------------------------------+ | 6 | root | localhost | uplooking | Query | 86 | Waiting for table metadata lock | alter table booboo add q9 int default 0 | | 8 | root | localhost | uplooking | Query | 0 | starting | show processlist | | 15 | root | localhost | uplooking | Sleep | 97 | | NULL | +----+------+-----------+-----------+---------+------+---------------------------------+-----------------------------------------+ 3 rows in set (0.00 sec)
mysql> select timediff(sysdate(),trx_started) timediff,sysdate(),trx_started,id,USER,DB,COMMAND,STATE,trx_state,trx_query from information_schema.processlist,information_schema.innodb_trx where trx_mysql_thread_id=id; Empty set (0.00 sec)
mysql> select * from information_schema.processlist where id=(select PROCESSLIST_ID from performance_schema.threads where thread_id=40); +----+------+-----------+-----------+---------+------+-------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+------+-----------+-----------+---------+------+-------+------+ | 15 | root | localhost | uplooking | Sleep | 466 | | NULL | +----+------+-----------+-----------+---------+------+-------+------+ 1 row in set (0.00 sec)
