表锁住了,而且无法解锁
本帖最后由 fuhao009 于 2023-10-17 16:28 编辑版本：8.0.25-16
TRANSACTIONS
------------
Trx id counter 402901548
Purge done for trx's n:o < 402901548 undo n:o < 0 state: running but idle
History list length 80
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421986673117032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421986673115208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421986673114296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 402851757, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 5
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 402851757 lock mode IX
---TRANSACTION 402851749, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 7
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 402851749 lock mode IX
---TRANSACTION 402851742, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 11
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 402851742 lock mode IX
---TRANSACTION 383545873, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 51
TABLE LOCK table `intell`.`dws_gantry_min_flow` trx id 383545873 lock mode IX
---TRANSACTION 383545866, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 64
TABLE LOCK table `intell`.`dws_gantry_min_flow` trx id 383545866 lock mode IX
---TRANSACTION 383545859, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 73
TABLE LOCK table `intell`.`dws_gantry_min_flow` trx id 383545859 lock mode IX
---TRANSACTION 374488323, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 13
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 374488323 lock mode IX
---TRANSACTION 374488322, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 4
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 374488322 lock mode IX
---TRANSACTION 374488315, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 7
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 374488315 lock mode IX
---TRANSACTION 356501551, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 12
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 356501551 lock mode IX
---TRANSACTION 356501550, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 13
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 356501550 lock mode IX
---TRANSACTION 356501542, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 22
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 356501542 lock mode IX
---TRANSACTION 285556683, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 33
TABLE LOCK table `intell`.`dws_gantry_min_flow` trx id 285556683 lock mode IX
---TRANSACTION 285556682, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 20
TABLE LOCK table `intell`.`dws_gantry_min_flow` trx id 285556682 lock mode IX
---TRANSACTION 285556675, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 32
TABLE LOCK table `intell`.`dws_gantry_min_flow` trx id 285556675 lock mode IX
查询事务的线程id是0
有效信息太少，请提供更多详细信息
能看到的信息显示当前有活跃事务，例如：
---TRANSACTION 402851757, ACTIVE (PREPARED) 93 sec recovered trx yejr 发表于 2023-10-17 13:49
有效信息太少，请提供更多详细信息
能看到的信息显示当前有活跃事务，例如：
dws_gantry_min_flow 我的目标是这张表被锁了 fuhao009 发表于 2023-10-17 13:52
dws_gantry_min_flow 我的目标是这张表被锁了
---TRANSACTION 374488315, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 7
TABLE LOCK table `intell`.`dws_station_min_flow` trx id 374488315 lock mode IX fuhao009 发表于 2023-10-17 13:53
---TRANSACTION 374488315, ACTIVE (PREPARED) 93 sec recovered trx
1 lock struct(s), heap size 1136, ...
(Tue Oct 17 13:53:47 2023)[(none)]>SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_id = 374488315\G;
*************************** 1. row ***************************
trx_mysql_thread_id: 0
1 row in set (0.00 sec) fuhao009 发表于 2023-10-17 13:54
(Tue Oct 17 13:53:47 2023)[(none)]>SELECT trx_mysql_thread_id FROM information_sche ...
show processlist完整结果提供下 yejr 发表于 2023-10-17 15:27
show processlist完整结果提供下
D USER HOST DB COMMAND TIME STATE INFO TIME_MS ROWS_SENT ROWS_EXAMINED
4256 hsyq 10.33.187.50:60766 intellSleep 203 NULL 20344942 171
3432 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927385 0 0
3456 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927376 0 0
3408 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927397 0 0
3424 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927388 0 0
3448 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927379 0 0
3416 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927393 0 0
3440 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927382 0 0
3400 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927402 0 0
3457 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927375 0 0
3433 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927385 0 0
3449 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927378 0 0
3425 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927388 0 0
3401 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927402 0 0
3417 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927392 0 0
3441 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927382 0 0
4233 hsyq 10.33.187.50:60226 intellSleep 419 NULL 41887619 260
3409 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927397 0 0
3394 system user NULL Connect 4927 waiting for handler commit Group replication applier module 4927411 0 0
3434 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927384 0 0
3458 system user NULL Connect 4927 Waiting for an event from Coordinator NULL 4927375 0 0
3410 system user NULL Connect 4927 Waiting for an event from Coordinator NULL fuhao009 发表于 2023-10-17 15:31
D USER HOST DB COMMAND TIME STATE INFO TIME_MS ROWS_SENT ROWS_EXAMIN ...
是用最高权限用户 root@localhost 跑的这些SQL吗，再执行 show grants; 确认下 fuhao009 发表于 2023-10-17 15:31
D USER HOST DB COMMAND TIME STATE INFO TIME_MS ROWS_SENT ROWS_EXAMIN ...
运行环境还有配置参数等信息也都提供 yejr 发表于 2023-10-17 15:36
运行环境还有配置参数等信息也都提供
这应该是XA事务，所以查询information_schema.innodb_trx时会看到trx_mysql_thread_id=0，可以执行xa recover查看所有xa事务，然后执行xa rollback 'xx' 回滚这些事务。
页:
[1]
2