mysql 排查死锁

操作步骤：

1、session 1 执行：

      start transaction;

      select *from t1;

2、session 2 在第1步执行完后执行：

     drop table t1;

此时session 2的drop语句被阻塞。那么怎么分析查看元数据锁呢？

方法：

1）执行show processlist;，可以看到drop语句在等待元数据锁

[sql]  view plain  copy

1. mysql> show processlist;  
2. +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+  
3. | Id | User        | Host      | db   | Command | Time    | State                                                                       | Info             |  
4. +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+  
5. |  5 | system user |           | NULL | Connect | 1050234 | Waiting for master to send event                                            | NULL             |  
6. |  6 | system user |           | NULL | Connect |  983193 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |  
7. |  8 | root        | localhost | yzs  | Sleep   |      93 |                                                                             | NULL             |  
8. |  9 | root        | localhost | yzs  | Query   |       3 | Waiting for table metadata lock                                             | drop table t1    |  
9. | 10 | root        | localhost | NULL | Query   |       0 | init                                                                        | show processlist |  
10. +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+  
11. 5 rows in set (0.00 sec)  

2）可以看到当前正在运行的事务的线程是trx_mysql_thread_id:8，那么这个线程在干什么呢？

[sql]  view plain  copy

1. mysql> select *from information_schema.innodb_trx\G  
2. *************************** 1. row ***************************  
3.                     trx_id: 17683  
4.                  trx_state: RUNNING  
5.                trx_started: 2017-10-18 05:32:46  
6.      trx_requested_lock_id: NULL  
7.           trx_wait_started: NULL  
8.                 trx_weight: 0  
9.        trx_mysql_thread_id: 8  
10.                  trx_query: NULL  
11.        trx_operation_state: NULL  
12.          trx_tables_in_use: 0  
13.          trx_tables_locked: 0  
14.           trx_lock_structs: 0  
15.      trx_lock_memory_bytes: 320  
16.            trx_rows_locked: 0  
17.          trx_rows_modified: 0  
18.    trx_concurrency_tickets: 0  
19.        trx_isolation_level: REPEATABLE READ  
20.          trx_unique_checks: 1  
21.     trx_foreign_key_checks: 1  
22. trx_last_foreign_key_error: NULL  
23.  trx_adaptive_hash_latched: 0  
24.  trx_adaptive_hash_timeout: 10000  
25.           trx_is_read_only: 0  
26. trx_autocommit_non_locking: 0  
27. 1 row in set (0.03 sec)  

3）可以看到这个线程执行的是select语句，如果执行show engine innodb status;可以看到该事务处于sleep状态，也就是说这个事务语句执行完了，但是没有提交。

执行kill 8，将该事务的线程杀掉就可以了。或者检查业务的SQL语句，检查下是否有未提交的SQL语句。

[sql]  view plain  copy

1. mysql> select *from performance_schema.events_statements_current\G  
2. *************************** 1. row ***************************  
3.               THREAD_ID: 27  
4.                EVENT_ID: 15  
5.            END_EVENT_ID: 15  
6.              EVENT_NAME: statement/sql/select  
7.                  SOURCE: mysqld.cc:962  
8.             TIMER_START: 1050544992900922000  
9.               TIMER_END: 1050544993740836000  
10.              TIMER_WAIT: 839914000  
11.               LOCK_TIME: 196000000  
12.                SQL_TEXT: select *from t1  
13.                  DIGEST: 1aa32397c8ec37230aed78ef16126571  
14.             DIGEST_TEXT: SELECT * FROM `t1`   
15.          CURRENT_SCHEMA: yzs  
16.             OBJECT_TYPE: NULL  
17.           OBJECT_SCHEMA: NULL  
18.             OBJECT_NAME: NULL  
19.   OBJECT_INSTANCE_BEGIN: NULL  
20.             MYSQL_ERRNO: 0  
21.       RETURNED_SQLSTATE: NULL  
22.            MESSAGE_TEXT: NULL  
23.                  ERRORS: 0  
24.                WARNINGS: 0  
25.           ROWS_AFFECTED: 0  
26.               ROWS_SENT: 10  
27.           ROWS_EXAMINED: 10  
28. CREATED_TMP_DISK_TABLES: 0  
29.      CREATED_TMP_TABLES: 0  
30.        SELECT_FULL_JOIN: 0  
31.  SELECT_FULL_RANGE_JOIN: 0  
32.            SELECT_RANGE: 0  
33.      SELECT_RANGE_CHECK: 0  
34.             SELECT_SCAN: 1  
35.       SORT_MERGE_PASSES: 0  
36.              SORT_RANGE: 0  
37.               SORT_ROWS: 0  
38.               SORT_SCAN: 0  
39.           NO_INDEX_USED: 1  
40.      NO_GOOD_INDEX_USED: 0  
41.        NESTING_EVENT_ID: NULL  
42.      NESTING_EVENT_TYPE: NULL