开发的话不可信

今天开发告诉我说表损坏不能查也不能删，当然开发的话不能信。

自己测试下

mysql> select * from order_lines_field_b limit 1;

一直是卡死状态

一直在想为啥不报错的，如果损坏了要报错。

于是百度看下，说看锁，我就晕了，又忘记了，mysql的select可能造成阻塞。第一感觉是有备份吗？但是是测试环境。没备份，到底问题出在哪里。那只能查了。

DDL语句被阻塞通常因为存在获取资源后未及时提交释放的长事务。因此，查找kill掉事务运行时间大于DDL运行时间的会话即可使DDL语句顺利下发，SQL语句如下：

查找事务运行时间 >= DDL等待时间的线程

SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >=
    (SELECT MAX(Time)
     FROM INFORMATION_SCHEMA.processlist
     WHERE STATE='Waiting for table metadata lock'
       AND INFO LIKE 'alter%table%' OR INFO LIKE 'truncate%table%') ;

一下子就定位到了     ID: 86299这线程信息

但是不放心老套路按照时间排序吧。这个就是所谓的方法2。排序的方法

mysql> select id,time,state from information_schema.processlist where Command != 'Sleep' and Info is not null order by time desc;
+--------+-------+---------------------------------+
| id     | time  | state                           |
+--------+-------+---------------------------------+
|  86299 | 89948 | Sending data                    |
|  87553 | 88802 | Waiting for table metadata lock |
|  90359 | 82235 | Waiting for table metadata lock |
|  92874 | 79665 | Waiting for table metadata lock |
|  93530 | 78552 | Waiting for table metadata lock |
|  94232 | 77326 | Waiting for table metadata lock |
|  96794 | 69071 | Waiting for table metadata lock |
| 102063 | 64900 | Waiting for table metadata lock |
| 103001 | 63039 | Waiting for table metadata lock |
| 102983 | 63011 | Waiting for table metadata lock |
| 103003 | 62950 | Waiting for table metadata lock |
| 106923 | 57829 | Waiting for table metadata lock |
| 107105 | 56936 | Waiting for table metadata lock |
| 107120 | 56882 | Waiting for table metadata lock |
| 108895 | 54634 | Waiting for table metadata lock |
|  98368 | 54293 | Waiting for table metadata lock |
| 106919 | 54184 | Waiting for table metadata lock |
| 108140 | 53924 | Waiting for table metadata lock |
| 109180 | 53362 | Waiting for table metadata lock |
|  88660 | 52449 | Waiting for table metadata lock |
| 109201 | 52331 | Waiting for table metadata lock |
| 115642 | 40268 | Waiting for table metadata lock |
| 115643 | 40233 | Waiting for table metadata lock |
| 130884 |  4157 | Waiting for table metadata lock |
| 130881 |  4139 | Waiting for table metadata lock |
| 131086 |  4018 | Waiting for table metadata lock |
| 131394 |  3733 | Waiting for table metadata lock |
| 132527 |   613 | Waiting for table metadata lock |
| 132644 |     0 | executing                       |
+--------+-------+---------------------------------+
29 rows in set (0.00 sec)

mysql> select id,time,state,info from information_schema.processlist where Command != 'Sleep' and Info is not null order by time desc;
+--------+------+-----------+--------------------------------------------------------------------------------------------------------------------------------+
| id     | time | state     | info                                                                                                                           |
+--------+------+-----------+--------------------------------------------------------------------------------------------------------------------------------+
+--------+------+-----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如上所示kill 86299之后所有的阻塞的线程开始顺序执行，执行完成被释放。

如下是之前就被定为到的线程的信息

select * from information_schema.processlist where id = 86299G
************************** 1. row ***************************
     ID: 86299
   USER: ordercenter_bjya1
   HOST: 1.1.1.1:37145
     DB: db
COMMAND: Query
   TIME: 89737
  STATE: Sending data
   INFO: SELECT
        *
        FROM
        ol
        left join f on f.p_id = ol.id and f.deleted = 0
        WHERE ol.deleted = 0
            and ol.org_code in
             (  
                'C0'
             , 
                'B0'
             ) 
            and ol.org_code= 'BJYA0'
        ORDER BY
        input_date
        DESC
        ,order_line_number
        DESC
        limit
        200,50
1 row in set (0.01 sec)


参考： https://www.cnblogs.com/digdeep/p/4892953.html