背景
大家有没有遇到这样的情况

某个SQL执行特别慢,导致整个transaction一直处于running阶段
某个Session的SQL已经执行完了,但是迟迟没有commit,一直处于sleep阶段
某个Session处于lock wait阶段,迟迟没有结束
以上,大部分原因都是大事务导致的,接下来我们好好聊聊相关话题

关键字
环境

  1. MySQL5.7.22
    低版本MySQL这边不再考虑,就像还有使用SAS盘的公司一样,费时费力,MySQL5.7+ 标配
  2. InnoDB存储引擎
  3. CentOS 6
    大事务的相关特征

  4. transaction开启到结束的时间非常长,我们这边举例为10s
  5. 正在执行的事务
  6. 未提交的事务
    实战
    如何监控那些正在执行的事务
  7. select * from sys.processlist
  8. show processlist
  9. select * from information_schema.processlist
  10. select * from sys.session
  11. select * from information_schema.innodb_trx;
  12. select from performance_schema.events_statements_current
    如何监控那些未提交的事务
    1
    select
    from information_schema.innodb_trx
    如何两者结合

select trx_id,INNODB_TRX.trx_state,INNODB_TRX.trx_started,se.conn_id as processlist_id,trx_lock_memory_bytes,se.user,se.command,se.state,se.current_statement,se.last_statement from information_schema.INNODB_TRX,sys.session as se where trx_mysql_thread_id=conn_id;
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
| trx_id | trx_state | trx_started | processlist_id | trx_lock_memory_bytes | user | command | state | current_statement | last_statement |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
| 1592104 | LOCK WAIT | 2018-06-26 11:51:17 | 3 | 1136 | NULL | Query | updating | update lc_1 set id=4 where id = 1 | NULL |
| 1592100 | RUNNING | 2018-06-26 11:49:08 | 2 | 1136 | NULL | Sleep | NULL | NULL | update lc_1 set id=3 where id = 1 |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
大家可以看到,通过这个可以立马发现事务语句处于running阶段 , 哪些事务处于lock wait阶段 , 如果遇到这种情况,我们应该如何处理呢?
聪明的你,一定会去根据trx_started去寻找蛛丝马迹,可是如果再生产环境中,这是一件非常复杂和繁忙的事情
不过没关系,我们还有神器可以使用

如何快速解决锁等待问题
dba:sys> select * from sys.innodb_lock_waits\G
1. row
wait_started: 2018-06-26 11:49:58
wait_age: 00:00:03
wait_age_secs: 3
locked_table: lc.lc_1
locked_index: GEN_CLUST_INDEX
locked_type: RECORD
waiting_trx_id: 1592102
waiting_trx_started: 2018-06-26 11:49:58
waiting_trx_age: 00:00:03
waiting_trx_rows_locked: 2
waiting_trx_rows_modified: 0
waiting_pid: 3
waiting_query: update lc_1 set id=4 where id = 1
waiting_lock_id: 1592102:32:3:4
waiting_lock_mode: X
blocking_trx_id: 1592100
blocking_pid: 2
blocking_query: NULL
blocking_lock_id: 1592100:32:3:4
blocking_lock_mode: X
blocking_trx_started: 2018-06-26 11:49:08
blocking_trx_age: 00:00:53
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
MySQL最终非常贴心都连kill SQL 语句都生产了,你只需要复制、粘贴即可

细心的你会发现,通过innodb_lock_waits你只能看到被lock的语句,但是看不到是哪个query语句拥有的锁,这又是为什么呢?

不卖关子,因为拥有锁的事务中可能拥有多条query语句,也可能已经执行完,但是没有commit,所以无法给出所有query语句。

那怎么办呢?哈哈,如果幸运的话,你可以根据我上述的案例 current_statement,last_statement 得到答案。

再换句话说,即便没有找到那条query,也不妨碍你解决当前的问题哈

总结
MySQL5.7 默默的提供了非常多的实用工具和新特性,需要DBA们去挖掘和探索。将看似平淡无奇的特性挖掘成黑武器,你才能成为那闪着光芒的Top5 MySQLer
工欲善其事必先利其器
1、具有1-5工作经验的,面对目前流行的技术不知从何下手,需要突破技术瓶颈的可以加群。

2、在公司待久了,过得很安逸,但跳槽时面试碰壁。需要在短时间内进修、跳槽拿高薪的可以加群。

3、如果没有工作经验,但基础非常扎实,对java工作机制,常用设计思想,常用java开发框架掌握熟练的,可以加群。

4、觉得自己很牛B,一般需求都能搞定。但是所学的知识点没有系统化,很难在技术领域继续突破的可以加群

5.群号:高级架构群 230419550 备注好信息!

6.阿里Java高级架构师直播讲解知识点,分享知识,多年工作经验的梳理和总结,带着大家全面、科学地建立自己的技术体系和技术认知

最后,希望找工作的朋友都能找到一份满意的工作。下面具体列出了面试常见的知识点,供大家参考,希望对你有所帮助。