1、对一个表进行ddl操作
1
2
3
| mysql>ALTER TABLE deposit_coin_order_user add `txid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '区块链id' ;
提示报错：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
|
查看该表数据只有39行数据：
1
2
3
4
5
6
| select count(*) from deposit_coin_order_user;
+----------+
| count(*) |
+----------+
| 39 |
+----------+
|
2、查看事物表Innodb_trx是否记录相关事物，如果有找到该事物的‘trx_mysql_thread_id’
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 421462261342800
trx_state: RUNNING
trx_started: 2018-08-10 08:24:58
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 12989053
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
|
使用show full processlist查看是否有‘trx_mysql_thread_id’对应的进程，如果有就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。没有的话看看有没有正在执行的很慢SQL记录线程。
1
2
| mysql > show full processlist;
| 12989053 | cwreadonly | 10.1.10.9:51650 | exchange_market | Sleep | 1189 | | NULL
|
3、发现有id为12989053的sql，需要手动kill掉
mysql > KILL 12989053;
4、再次执行ddl语句，正常执行
ALTER TABLE deposit_coin_order_user add txid varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘区块链id’ ;