MySQL锁表等待的原因

-- 1.查看当前数据库锁表的情况

SELECT * FROM information_schema.INNODB_TRX;

  省略 了 trx_前缀 ，例子 wait_started (是trx_wait_started)       
      
   wait_started: 2018-07-16 16:25:17  //锁等待开始的时间,16:25开始等待
                    wait_age: 00:10:08    //发现问题时已经等待了10分钟了
               wait_age_secs: 608   //608秒，也就是等10分钟了
                locked_table: `iws`.`busi_reconciliationgbgsinfo_inputdetails` //被锁住的表名
                locked_index: PRIMARY  //被锁住的索引
                 locked_type: RECORD //锁的类型为行锁
              waiting_trx_id: 13666265  //waiting transaction id，正在等待事务的id号
         waiting_trx_started: 2018-07-16 16:24:54 //这个事务是从16:24开始等待
             waiting_trx_age: 00:10:31 //等了10分钟了
     waiting_trx_rows_locked: 1 //正在等待的这个事务锁住了1行记录
   waiting_trx_rows_modified: 0 //正在等待的这个事务修改了0行记录
                 waiting_pid: 441805 //这个等待事务的线程id是多少，通过show processlist 命令可以查到它，结果看到是一个sleep的线程，没有执行具体sql语句，见下
               waiting_query: update busi_reconciliationgbgs ...                where id = 4510 //等待锁释放的语句
             waiting_lock_id: 13666265:2924:21:94 //正在等待的锁id
           waiting_lock_mode: X //等待锁的类型是排它锁
             blocking_trx_id: 13666259 //这个事务id阻塞了waiting lock
                blocking_pid: 441803 阻塞事务的pid
              blocking_query: NULL  //阻塞事务的sql语句
            blocking_lock_id: 13666259:2924:21:94
          blocking_lock_mode: X
        blocking_trx_started: 2018-07-16 16:24:51
            blocking_trx_age: 00:10:34
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 441803
sql_kill_blocking_connection: KILL 441803

上面看到输出了很多的东西，其实只关注上面的waiting_pid、waiting_query和blocking_pid、blocking_query四个参数即可；其中waiting_pid和blocking_pid两个参数就是通过执行show processlist命令里面输出的线程id号，如下：
mysql> show full processlist 
*************************** 8. row ***************************
     Id: 441803
   User: iws
   Host: 172.16.21.7:46121
     db: iws
Command: Sleep
   Time: 655
  State: 
   Info: NULL
*************************** 9. row ***************************
     Id: 441805
   User: iws
   Host: 172.16.21.7:46122
     db: iws
Command: Query
   Time: 652
  State: updating
   Info: update busi_reconciliationgbgsinfo_inputdetails     set                bgs_id = 1622              ,         date = '2018-06-24 00:00:00'              ,         awbnumber = '006-85516771'              ,         incidental = 15.00              ,         entry_exit = 23.00              ,         warehousing_fee = 0.00              ,         loading_unloading = 0.00              ,         other = 0.00              ,         total = 38.00                     ,         state = 20              ,         comparison_resultsid = 30              ,         confirmation_method = '人工'              ,         confirmationid = 'root'              ,         confirmationtime = '2018-07-16 16:25:17'              ,         confirmation_note = '.'              ,         createtime = '2018-06-24 20:00:07'                     ,         createrid = '9862ebdbaf3249a88bcaa8f01bde0471'                        where id = 4510




3. 我们看到发生等待的线程441805对应的sql语句是：update busi_reconciliationgbgs ... where id = 4510，但是锁表的线程441803对应的sql语句竟然是Null。这就更让人迷惑了。

    于是我默默的翻开了ysql官方文档，原来里面已经对这个null专门做了说明。

    官方文档说，要想找到这个null值对应的阻塞语句，可以通过下面几个步骤寻找：

    a)、根据锁表的processlist id 441803，运用如下sql，找到null对应的sql语句，如下：
SELECT  SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID
in 
(SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=441803)

    b)、如果上面找到的sql语句，你还是不能分析出为什么他们会锁表，一直拿着锁不释放，那么你可以查看 performance_schema.events_statements_history表里面最近执行过的10条sql（假设上面查到的thread_id=28）:
SELECT EVENT_ID,CURRENT_SCHEMA, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID
in 
(SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=441803) order by event_id




其他：上面查询锁的sql可以只关注已下几个列，如下：
SELECT
wait_started,
wait_age,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query,
blocking_lock_mode,
sql_kill_blocking_query
FROM
sys.innodb_lock_waits

 

 

-- 2.杀掉查询结果中锁表的trx_mysql_thread_id

kill trx_mysql_thread_id

参考链接

http://blog.itpub.net/28916011/viewspace-2158012/