ActiveMQ“Classic”消息代理日志中出现以下错误。我正在寻找解决方案来查找根本原因并修复此问题。
Sep 6 16:10:46 SERVER activemq_db8159[1168628]: WARN | Error while closing connection: Deadlock found when trying to get lock; try restarting transaction
Sep 6 16:10:46 SERVER activemq_db8159[1168628]: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
稍后，由于服务重新启动，代理正在重新启动，连续的客户端消息被卡住。
2023-09-06 16:10:46,332 | INFO | Stopping BrokerService[dbaas_broker] due to exception, java.io.IOException: Deadlock found when trying to get lock; try restarting transaction | org.apache.activemq.util.DefaultIOExceptionHandler | ActiveMQ Transport: ssl:///<IPadress>:54970
JDBC 连接：
<jdbcPersistenceAdapter dataSource="#mysql-ds" lockKeepAlivePeriod="10000">
<bean class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" id="mysql-ds">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://mysqldb.com:3307/8159_activemq?useSSL=true&requireSSL=true&socketTimeout=10000&connectTimeout=5000&autoReconnect=true&maxReconnects=60" />
版本详细信息
- ActiveMQ“经典”5.17.2
- MySQL 连接器 8.0.31
INNODB状态：
MariaDB [(none)]> SHOW ENGINE INNODB STATUS \G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2023-09-08 05:44:03 0x7fc5ddd00700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 658347 srv_active, 0 srv_shutdown, 3052597 srv_idle
srv_master_thread log flush and writes: 3710151
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13196031
OS WAIT ARRAY INFO: signal count 7507305
RW-shared spins 3175113, rounds 52954335, OS waits 1496241
RW-excl spins 1004776, rounds 5875228, OS waits 14950
RW-sx spins 3900, rounds 714889, OS waits 23471
Spin rounds per wait: 16.68 RW-shared, 5.85 RW-excl, 183.30 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-09-07 11:30:08 0x7fc5ec0f5700
*** (1) TRANSACTION:
TRANSACTION 59108994, ACTIVE 22 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 10 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 965528, OS thread handle 140488046327552, query id 18506205 10.4.28.55 8159_activemq_rw Sending data
DELETE FROM ACTIVEMQ_MSGS WHERE (PRIORITY=0 AND ID <= ( SELECT min(ACTIVEMQ_ACKS.LAST_ACKED_ID) FROM ACTIVEMQ_ACKS WHERE ACTIVEMQ_ACKS.CONTAINER=ACTIVEMQ_MSGS.CONTAINER AND ACTIVEMQ_ACKS.PRIORITY=0) )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 62 page no 22720 n bits 96 index PRIMARY of table `8159_activemq`.`ACTIVEMQ_MSGS` trx id 59108994 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 10; compact format; info bits 32
0: len 8; hex 800000000000c2db; asc ;;
1: len 6; hex 00000385ef32; asc 2;;
2: len 7; hex 77000039360733; asc w 96 3;;
3: len 20; hex 71756575653a2f2f544f504841545f494e565f46; asc queue://topic_INV_F;;
4: len 30; hex 49443a464d5341503331302d34393933362d313639343038303939383932; asc ID:FMSAP310-49936-169408099892; (total 41 bytes);
5: len 8; hex 8000000000000006; asc ;;
6: len 8; hex 8000000000000000; asc ;;
7: len 30; hex 000001c91c0000008e00017b01002549443a464d5341503331302d343939; asc { %ID:FMSAP310-499; (total 461 bytes);
8: len 8; hex 8000000000000000; asc ;;
9: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 59109170, ACTIVE 1 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 965524, OS thread handle 140488045713152, query id 18506329 <IP adress> 8159_activemq_rw Updating
DELETE FROM ACTIVEMQ_MSGS WHERE ID=49883
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 62 page no 22720 n bits 96 index PRIMARY of table `8159_activemq`.`ACTIVEMQ_MSGS` trx id 59109170 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 10; compact format; info bits 32
0: len 8; hex 800000000000c2db; asc ;;
1: len 6; hex 00000385ef32; asc 2;;
2: len 7; hex 77000039360733; asc w 96 3;;
3: len 20; hex 71756575653a2f2f544f504841545f494e565f46; asc queue://topic_INV_F;;
4: len 30; hex 49443a464d5341503331302d34393933362d313639343038303939383932; asc ID:FMSAP310-49936-169408099892; (total 41 bytes);
5: len 8; hex 8000000000000006; asc ;;
6: len 8; hex 8000000000000000; asc ;;
7: len 30; hex 000001c91c0000008e00017b01002549443a464d5341503331302d343939; asc { %ID:FMSAP310-499; (total 461 bytes);
8: len 8; hex 8000000000000000; asc ;;
9: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 62 page no 7 n bits 792 index ACTIVEMQ_MSGS_PIDX of table `8159_activemq`.`ACTIVEMQ_MSGS` trx id 59109170 lock_mode X locks rec but not gap waiting
Record lock, heap no 558 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000000; asc ;;
1: len 8; hex 800000000000c2db; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 59338587
Purge done for trx's n:o < 59338587 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421962821473120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821456256, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821481552, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821468904, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821477336, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821464688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821460472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821452040, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421962821447824, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (read thread)
I/O thread 11 state: waiting for i/o request (read thread)
I/O thread 12 state: waiting for i/o request (read thread)
I/O thread 13 state: waiting for i/o request (read thread)
I/O thread 14 state: waiting for i/o request (read thread)
I/O thread 15 state: waiting for i/o request (read thread)
I/O thread 16 state: waiting for i/o request (read thread)
I/O thread 17 state: waiting for i/o request (read thread)
I/O thread 18 state: waiting for i/o request (read thread)
I/O thread 19 state: waiting for i/o request (read thread)
I/O thread 20 state: waiting for i/o request (read thread)
I/O thread 21 state: waiting for i/o request (read thread)
I/O thread 22 state: waiting for i/o request (read thread)
I/O thread 23 state: waiting for i/o request (read thread)
I/O thread 24 state: waiting for i/o request (read thread)
I/O thread 25 state: waiting for i/o request (read thread)
I/O thread 26 state: waiting for i/o request (read thread)
I/O thread 27 state: waiting for i/o request (read thread)
I/O thread 28 state: waiting for i/o request (read thread)
I/O thread 29 state: waiting for i/o request (read thread)
I/O thread 30 state: waiting for i/o request (read thread)
I/O thread 31 state: waiting for i/o request (read thread)
I/O thread 32 state: waiting for i/o request (read thread)
I/O thread 33 state: waiting for i/o request (read thread)
I/O thread 34 state: waiting for i/o request (read thread)
I/O thread 35 state: waiting for i/o request (read thread)
I/O thread 36 state: waiting for i/o request (read thread)
I/O thread 37 state: waiting for i/o request (read thread)
I/O thread 38 state: waiting for i/o request (read thread)
I/O thread 39 state: waiting for i/o request (read thread)
I/O thread 40 state: waiting for i/o request (read thread)
I/O thread 41 state: waiting for i/o request (read thread)
I/O thread 42 state: waiting for i/o request (read thread)
I/O thread 43 state: waiting for i/o request (read thread)
I/O thread 44 state: waiting for i/o request (read thread)
I/O thread 45 state: waiting for i/o request (read thread)
I/O thread 46 state: waiting for i/o request (read thread)
I/O thread 47 state: waiting for i/o request (read thread)
I/O thread 48 state: waiting for i/o request (read thread)
I/O thread 49 state: waiting for i/o request (read thread)
I/O thread 50 state: waiting for i/o request (read thread)
I/O thread 51 state: waiting for i/o request (read thread)
I/O thread 52 state: waiting for i/o request (read thread)
I/O thread 53 state: waiting for i/o request (read thread)
I/O thread 54 state: waiting for i/o request (read thread)
I/O thread 55 state: waiting for i/o request (read thread)
I/O thread 56 state: waiting for i/o request (read thread)
I/O thread 57 state: waiting for i/o request (read thread)
I/O thread 58 state: waiting for i/o request (read thread)
I/O thread 59 state: waiting for i/o request (read thread)
I/O thread 60 state: waiting for i/o request (read thread)
I/O thread 61 state: waiting for i/o request (read thread)
I/O thread 62 state: waiting for i/o request (read thread)
I/O thread 63 state: waiting for i/o request (read thread)
I/O thread 64 state: waiting for i/o request (read thread)
I/O thread 65 state: waiting for i/o request (read thread)
I/O thread 66 state: waiting for i/o request (write thread)
I/O thread 67 state: waiting for i/o request (write thread)
I/O thread 68 state: waiting for i/o request (write thread)
I/O thread 69 state: waiting for i/o request (write thread)
I/O thread 70 state: waiting for i/o request (write thread)
I/O thread 71 state: waiting for i/o request (write thread)
I/O thread 72 state: waiting for i/o request (write thread)
I/O thread 73 state: waiting for i/o request (write thread)
I/O thread 74 state: waiting for i/o request (write thread)
I/O thread 75 state: waiting for i/o request (write thread)
I/O thread 76 state: waiting for i/o request (write thread)
I/O thread 77 state: waiting for i/o request (write thread)
I/O thread 78 state: waiting for i/o request (write thread)
I/O thread 79 state: waiting for i/o request (write thread)
I/O thread 80 state: waiting for i/o request (write thread)
I/O thread 81 state: waiting for i/o request (write thread)
I/O thread 82 state: waiting for i/o request (write thread)
I/O thread 83 state: waiting for i/o request (write thread)
I/O thread 84 state: waiting for i/o request (write thread)
I/O thread 85 state: waiting for i/o request (write thread)
I/O thread 86 state: waiting for i/o request (write thread)
I/O thread 87 state: waiting for i/o request (write thread)
I/O thread 88 state: waiting for i/o request (write thread)
I/O thread 89 state: waiting for i/o request (write thread)
I/O thread 90 state: waiting for i/o request (write thread)
I/O thread 91 state: waiting for i/o request (write thread)
I/O thread 92 state: waiting for i/o request (write thread)
I/O thread 93 state: waiting for i/o request (write thread)
I/O thread 94 state: waiting for i/o request (write thread)
I/O thread 95 state: waiting for i/o request (write thread)
I/O thread 96 state: waiting for i/o request (write thread)
I/O thread 97 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
84348099 OS file reads, 45353143 OS file writes, 7813858 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 5.60 writes/s, 2.58 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 326 merges
merged operations:
insert 150, delete mark 267, delete 22
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
3.85 hash searches/s, 0.25 non-hash searches/s
---
LOG
---
Log sequence number 2500024278373
Log flushed up to 2500024276737
Pages flushed up to 2500024276737
Last checkpoint at 2500024276128
0 pending log flushes, 0 pending chkp writes
6087092 log i/o's done, 2.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 301989888
Dictionary memory allocated 43920
Buffer pool size 16054
Free buffers 1024
Database pages 15022
Old database pages 5525
Modified db pages 50
Percent of dirty pages(LRU & free pages): 0.312
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7648268, not young 94650352
0.00 youngs/s, 0.00 non-youngs/s
Pages read 84348078, created 11140151, written 37578055
0.00 reads/s, 0.00 creates/s, 2.96 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15022, unzip_LRU len: 0
I/O sum[157]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140484596745984, state: sleeping
Number of rows inserted 55948, updated 665215, deleted 55946, read 8771119
0.00 inserts/s, 0.33 updates/s, 0.00 deletes/s, 2.35 reads/s
Number of system rows inserted 824726, updated 0, deleted 824703, read 824744
0.33 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.001 sec)
ERROR: No query specified
MariaDB [(none)]>
我尝试整理所有信息和日志，并尝试在互联网上进行研究，但没有找到解决此问题的方法？
添加 InnoDB 状态：
MariaDB [8374_activemq]> SHOW TABLE STATUS LIKE 'ACTIVEMQ_LOCK' \G;
*************************** 1. row ***************************
Name: ACTIVEMQ_LOCK
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2023-02-17 00:25:26
Update_time: 2023-10-03 21:48:28
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [8374_activemq]> SHOW TABLE STATUS LIKE 'ACTIVEMQ_MSGS' \G;
*************************** 1. row ***************************
Name: ACTIVEMQ_MSGS
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 81920
Data_free: 0
Auto_increment: NULL
Create_time: 2023-02-17 00:25:26
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [8374_activemq]>
SHOW CREATE TABLE ACTIVEMQ_MSGS \G;
*************************** 1. row ***************************
Table: ACTIVEMQ_MSGS
Create Table: CREATE TABLE `ACTIVEMQ_MSGS` (
`ID` bigint(20) NOT NULL,
`CONTAINER` varchar(250) DEFAULT NULL,
`MSGID_PROD` varchar(250) DEFAULT NULL,
`MSGID_SEQ` bigint(20) DEFAULT NULL,
`EXPIRATION` bigint(20) DEFAULT NULL,
`MSG` longblob DEFAULT NULL,
`PRIORITY` bigint(20) DEFAULT NULL,
`XID` varchar(250) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `ACTIVEMQ_MSGS_MIDX` (`MSGID_PROD`,`MSGID_SEQ`),
KEY `ACTIVEMQ_MSGS_CIDX` (`CONTAINER`),
KEY `ACTIVEMQ_MSGS_EIDX` (`EXPIRATION`),
KEY `ACTIVEMQ_MSGS_PIDX` (`PRIORITY`),
KEY `ACTIVEMQ_MSGS_XIDX` (`XID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
1 row in set (0.000 sec)
ERROR: No query specified
答案1
考虑
优化表8159_activemq。ACTIVEMQ_MSGS
解决 SHOW ENGINE INNODB STATUS 死锁报告中的“无间隙”消息。
并在时间允许的情况下提供额外工作量分析。