Mysql 操作死锁解决方案

起因：用Navicat批量删除表时，卡死；中止后，再次执行删除数据或表仍是卡死；

分析：应该是触发死锁。

使用 show processlist; 发现Waiting for table metadata lock 问题；

mysql> show processlist;

+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+
| ID    | USER            | HOST      | DB   | COMMAND | TIME   | STATE                           | INFO                                                                  |
+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+
|     1 | event_scheduler | localhost | NULL | Daemon  | 365869 | Waiting on empty queue          | NULL                                                                  |
| 13006 | root            | localhost | NULL | Query   |     35 | Waiting for table metadata lock | alter table test.t add column s int                                   |
| 13040 | root            | localhost | NULL | Query   |      0 | executing                       | select * from information_schema.processlist where command != 'sleep' |
| 13044 | root            | localhost | NULL | Query   |      4 | Waiting for table metadata lock | update test.t set b = 10 where a = 3                                  |
| 13043 | root            | localhost | NULL | Query   |     21 | Waiting for table metadata lock | select * from test.t                                                  |
+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+

尝试1：kill mysql 进程，重新执行删除表等DDL操作仍出现死锁，报Waiting for table metadata lock；

mysql> kill 13006;

尝试2：重启Mysql服务，仍会死锁；

尝试3: 设置kill_idle_transation ，失败；

mysql>  set global kill_idle_transaction=5;
1193 - Unknown system variable 'kill_idle_transaction'

尝试4：修改lock_wait_timeout配置，缩短超时时间，无效;

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
mysql> SET GLOBAL innodb_lock_wait_timeout=500;
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

尝试5：查看事务信息表 SELECT * FROM information_schema.INNODB_TRX;发现有2个死锁。但trx_mysql_thread_id=0; 无法直接kill相关进程;

mysql> SELECT * FROM information_schema.INNODB_TRX;
+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id     | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| 2544640445 | RUNNING   | 2022-09-26 15:26:38 | NULL                  | NULL             |          5 |                   0 | NULL      | NULL                |                 0 |                 2 |                3 |                  1136 |               1 |                 2 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |
| 2451894785 | RUNNING   | 2022-09-26 15:26:38 | NULL                  | NULL             |          0 |                   0 | NULL      | NULL                |                 0 |                 0 |                0 |                  1136 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |
+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+

本案例正确处理：trx_mysql_thread_id=0的事务为XA事务，需对XA事务回滚；

步骤一：需使用xa recover获取XA事务信息。

mysql> xa recover;
+----------+--------------+--------------+-------------------------------------------+
| formatID | gtrid_length | bqual_length | data                                      |
+----------+--------------+--------------+-------------------------------------------+
|        1 |           39 |            2 | 297d0fba-8a0c-4945-adab-bf1eafedf1d6:3734 |
|        1 |           39 |            2 | 418ffc3a-7d9c-4687-87fb-7ca6814a2dbc:5050 |
+----------+--------------+--------------+-------------------------------------------+

步骤二：XA事务回滚，根据上一步获取到的XA事务信息，根据gtrid_length,bqual_length分割data值，代入下面的格式：

# xa rollback 'left(data,gtrid_length)'，'substr(data,gtrid_length+1,bqual_length)', formatID;

本案例回滚SQL:

mysql> xa rollback '297d0fba-8a0c-4945-adab-bf1eafedf1d6:37','34', 1;
Query OK, 0 rows affected

mysql> xa rollback '418ffc3a-7d9c-4687-87fb-7ca6814a2dbc:50','50', 1;
Query OK, 0 rows affected

再次删除表，可正常删除。完成！