5.7.18 innodb表锁排查

在5.7以前的版本，我们通常通过innodb_locks,innodb_trx,innodb_table_waits,show processlist,show engine innodb status这些系统视图和命令进行排查,5.7之后，安装之后多了一个sys的数据库，这个数据库下的x$session系统会对排查表锁提供了很大帮助，如下显示系统当前的会话信息，关键的几个信息:

thd_id : 线程id

last_statement:上次执行的sql

current_statement:当前执行的sql

state:状态信息

从如下的输出可以看出thd_id=94741执行了lock table操作，罪魁祸首就是它,直接kill 94741，完事!

mysql> select * from sys.x$session \G;
*************************** 1. row ***************************
                thd_id: 94741
               conn_id: 94716
                  user:
                    db: ngoss_dim
               command: Sleep
                 state: NULL
                  time: 7484
     current_statement: NULL
     statement_latency: NULL
              progress: NULL
          lock_latency: 77000000
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: lock table tb_test_innodb read
last_statement_latency: 101336000
        current_memory: 0
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: NULL
             trx_state: NULL
        trx_autocommit: NULL
                   pid: 14632
          program_name: mysql
*************************** 2. row ***************************
                thd_id: 94930
               conn_id: 94905
                  user:
                    db: ngoss_dim
               command: Query
                 state: Waiting for table metadata lock
                  time: 7311
     current_statement: insert into tb_test_innodb values(3,'name3')
     statement_latency: 7311914509668000
              progress: NULL
          lock_latency: 0
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 0
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: NULL
             trx_state: NULL
        trx_autocommit: NULL
                   pid: 14641
          program_name: mysqllast_statement