前提

performance_schema = on; 

實(shí)驗(yàn)

1、建一個(gè)表，插入三條數(shù)據(jù)

mysql> use test1; 
Database changed 
mysql> create table action1(id int); 
Query OK, 0 rows affected (0.11 sec) 
  
mysql> insert into action1 values(1),(2),(3); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0   
mysql> select * from action1; 
+------+ 
| id   | 
+------+ 
|    1 | 
|    2 | 
|    3 | 
+------+3 rows in set (0.00 sec) 

2、開啟一個(gè)事務(wù)，刪除掉一行記錄，但不提交

mysql> begin; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> delete from action1 where id = 3; 
Query OK, 1 row affected (0.00 sec) 

3、另開啟一個(gè)事務(wù)，更新這條語(yǔ)句，會(huì)被鎖住

mysql> update action1 set id = 7 where id = 3; 

4、通過(guò) show processlist 只能看到一條正在執(zhí)行的 SQL 語(yǔ)句

mysql> show processlist; 
| 22188 | root        | localhost          | test1 | Sleep   |  483 |          | NULL                                   | 
| 22218 | root        | localhost          | NULL  | Query   |    0 | starting | show processlist                       | 
| 22226 | root        | localhost          | test1 | Query   |    3 | updating | update action1 set id = 7 where id = 3 | 
+-------+-------------+--------------------+-------+---------+------+----------+----------------------------------------+ 

5、接下來(lái)就是我們知道的，通過(guò) information_schema 庫(kù)里的 INNODBTRX、INNODBLOCKS 、INNODBLOCK_WAITS 獲得的一個(gè)鎖信息

mysql> select * from INNODB_LOCK_WAITS; 
+-------------------+-------------------+-----------------+------------------+ 
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | 
+-------------------+-------------------+-----------------+------------------+ 
| 5978292           | 5978292:542:3:2   | 5976374         | 5976374:542:3:2  | 
+-------------------+-------------------+-----------------+------------------+1 row in set, 1 warning (0.00 sec) 
  
mysql> select * from INNODB_LOCKs; 
+-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+ 
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index      | lock_space | lock_page | lock_rec | lock_data      | 
+-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+ 
| 5978292:542:3:2 | 5978292     | X         | RECORD    | `test1`.`action1` | GEN_CLUST_INDEX |        542 |         3 |        2 | 0x00000029D504 | 
| 5976374:542:3:2 | 5976374     | X         | RECORD    | `test1`.`action1` | GEN_CLUST_INDEX |        542 |         3 |        2 | 0x00000029D504 | 
+-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+2 rows in set, 1 warning (0.00 sec) 
   
mysql> select trx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_id from INNODB_TRX; 
+---------+---------------------+-----------------------+----------------------------------------+---------------------+ 
| trx_id  | trx_started         | trx_requested_lock_id | trx_query                              | trx_mysql_thread_id | 
+---------+---------------------+-----------------------+----------------------------------------+---------------------+ 
| 5978292 | 2020-07-26 22:55:33 | 5978292:542:3:2       | update action1 set id = 7 where id = 3 |               22226 | 
| 5976374 | 2020-07-26 22:47:33 | NULL                  | NULL                                   |               22188 | 
+---------+---------------------+-----------------------+----------------------------------------+---------------------+ 

6、從上面可以看出來(lái)是 thread_id 為 22188 的執(zhí)行的 SQL 語(yǔ)句鎖住了后面的更新操作，但是我們從上文中 show processlist 中并未看到這條事務(wù)，測(cè)試環(huán)境我們可以直接 kill 掉對(duì)應(yīng)的線程號(hào)，但如果是生產(chǎn)環(huán)境中，我們需要找到對(duì)應(yīng)的 SQL 語(yǔ)句，根據(jù)相應(yīng)的語(yǔ)句再考慮接下來(lái)應(yīng)該怎么處理

7、需要結(jié)合 performance_schema.threads 找到對(duì)應(yīng)的事務(wù)號(hào)

mysql> select * from performance_schema.threads where processlist_ID = 22188\G 
*************************** 1. row *************************** 
          THREAD_ID: 22225  //perfoamance_schema中的事務(wù)計(jì)數(shù)器               NAME: thread/sql/one_connection 
               TYPE: FOREGROUND 
     PROCESSLIST_ID: 22188  //從show processlist中看到的id   PROCESSLIST_USER: root 
   PROCESSLIST_HOST: localhost 
     PROCESSLIST_DB: test1 
PROCESSLIST_COMMAND: Sleep 
   PROCESSLIST_TIME: 1527  PROCESSLIST_STATE: NULL 
   PROCESSLIST_INFO: NULL 
   PARENT_THREAD_ID: NULL 
               ROLE: NULL 
       INSTRUMENTED: YES 
            HISTORY: YES 
    CONNECTION_TYPE: Socket 
       THREAD_OS_ID:8632 1 row in set (0.00 sec) 

8、找到事務(wù)號(hào)，可以從 events_statements_current 找到對(duì)應(yīng)的 SQL 語(yǔ)句：SQL_TEXT

mysql> select * from events_statements_current where THREAD_ID = 22225\G 
*************************** 1. row *************************** 
              THREAD_ID: 22225               EVENT_ID: 14           END_EVENT_ID: 14             EVENT_NAME: statement/sql/delete 
                 SOURCE: 
            TIMER_START: 546246699055725000              TIMER_END: 546246699593817000             TIMER_WAIT: 538092000              LOCK_TIME: 238000000               SQL_TEXT: delete from action1 where id = 3  //具體的sql語(yǔ)句                 DIGEST: 8f9cdb489c76ec0e324f947cc3faaa7c 
            DIGEST_TEXT: DELETE FROM `action1` WHERE `id` = ? 
         CURRENT_SCHEMA: test1 
            OBJECT_TYPE: NULL 
          OBJECT_SCHEMA: NULL 
            OBJECT_NAME: NULL 
  OBJECT_INSTANCE_BEGIN: NULL 
            MYSQL_ERRNO: 0      RETURNED_SQLSTATE: 00000           MESSAGE_TEXT: NULL 
                 ERRORS: 0               WARNINGS: 0          ROWS_AFFECTED: 1              ROWS_SENT: 0          ROWS_EXAMINED: 3CREATED_TMP_DISK_TABLES: 0     CREATED_TMP_TABLES: 0       SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0           SELECT_RANGE: 0     SELECT_RANGE_CHECK: 0            SELECT_SCAN: 0      SORT_MERGE_PASSES: 0             SORT_RANGE: 0              SORT_ROWS: 0              SORT_SCAN: 0          NO_INDEX_USED: 0     NO_GOOD_INDEX_USED: 0       NESTING_EVENT_ID: NULL 
     NESTING_EVENT_TYPE: NULL 
    NESTING_EVENT_LEVEL: 01 row in set (0.00 sec) 

9、可以看到是一條 delete 阻塞了后續(xù)的 update，生產(chǎn)環(huán)境中可以拿著這條 SQL 語(yǔ)句詢問(wèn)開發(fā)，是不是有 kill 的必要。