Very occasionally (perhaps once per four months of uptime per server) we see a situation where an InnoDB thread "hangs" in the "Update" state of a simple "INSERT INTO". When this happens, the only way to kill the thread is to "kill -9" the overall process (the thread doesn't respond to a normal "KILL HARD query_id", other than changing the state to "Killed"). It doesn't seem to be a deadlock.
This problem is not limited to a single database or hardware server; I've seen it on several different databases and physical servers.
Id User Host db Command Time State Info Progress
8597007 redacted localhost redacted Query 72 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:23', 1, '', '') 0.000
8597220 redacted localhost redacted Query 42 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:52', 1, '', '') 0.000
8597326 redacted localhost redacted Query 32 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:03', 1, '', '') 0.000
8597439 redacted localhost redacted Sleep 1 NULL 0.000
8597458 redacted localhost redacted Query 16 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('<redacted>', '2025-04-24 07:50:18', 1, '', '') 0.000
8597682 redacted localhost redacted Query 2 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:32', 1, '', '') 0.000
8597699 redacted localhost redacted Sleep 0 NULL 0.000
8597706 root localhost NULL Query 0 starting SHOW FULL PROCESSLIST 0.000
=====================================
2025-04-23 23:50:35 0x7fbb111f66c0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 30 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 835 srv_active, 0 srv_shutdown, 798067 srv_idle
srv_master_thread log flush and writes: 798902
----------
SEMAPHORES
----------
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-04-22 22:47:02 0x7fbb9c06c6c0
*** (1) TRANSACTION:
TRANSACTION 15896049065, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 213 lock struct(s), heap size 24696, 18312 row lock(s)
MariaDB thread id 7561112, OS thread handle 140443753301696, query id 1249654721 localhost redacted init for update
UPDATE wp_actionscheduler_actions SET claim_id=3709294, last_attempt_gmt='2025-04-23 05:47:02', last_attempt_local='2025-04-23 06:47:02' WHERE claim_id = 0 AND scheduled_date_gmt <= '2025-04-23 05:47:02' AND status='pending' ORDER BY priority ASC, attempts ASC, scheduled_date_gmt ASC, action_id ASC LIMIT 25
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1369764 page no 991 n bits 768 index status of table `redacted`.`wp_actionscheduler_actions` trx id 15896049065 lock_mode X waiting
Record lock, heap no 671 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b9bb78; asc x;;
*** CONFLICTING WITH:
RECORD LOCKS space id 1369764 page no 991 n bits 768 index status of table `redacted`.`wp_actionscheduler_actions` trx id 15896049073 lock_mode X locks rec but not gap
Record lock, heap no 671 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b9bb78; asc x;;
98
*** CONFLICTING WITH:
RECORD LOCKS space id 1369764 page no 991 n bits 768 index status of table `redacted`.`wp_actionscheduler_actions` trx id 15896049065 lock_mode X
Record lock, heap no 662 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b800cf; asc ;;
Record lock, heap no 663 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b8044b; asc K;;
Record lock, heap no 664 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b84042; asc @B;;
Record lock, heap no 665 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b95118; asc Q ;;
Record lock, heap no 666 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b9909f; asc ;;
Record lock, heap no 667 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b99a64; asc d;;
Record lock, heap no 668 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 70656e64696e67; asc pending;;
1: len 8; hex 0000000000b99a65; asc e;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 15900136305
Purge done for trx's n:o < 15900136296 undo n:o < 0 state: running
History list length 1407
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7fbb9f404380), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fbb9f406f80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fbb9f3ff680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fbb9f404e80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fbb9f405980), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 15900136247, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 8597682, OS thread handle 140443754120896, query id 1424570532 localhost redacted Update
INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:32', 1, '', '')
---TRANSACTION (0x7fbb9f402d80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fbb9f400c80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 15900135437, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 8597458, OS thread handle 140444290127552, query id 1424555212 localhost redacted Update
INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('<redacted>', '2025-04-24 07:50:18', 1, '', '')
---TRANSACTION (0x7fbb9f407a80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 15900134864, ACTIVE 32 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 8597326, OS thread handle 140443754325696, query id 1424540084 localhost redacted Update
INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:03', 1, '', '')
---TRANSACTION 15900134271, ACTIVE 43 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 8597220, OS thread handle 140441414899392, query id 1424530951 localhost redacted Update
INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:52', 1, '', '')
---TRANSACTION 15900132917, ACTIVE 72 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 8597007, OS thread handle 140441423496896, query id 1424510658 localhost redacted Update
INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:23', 1, '', '')
---TRANSACTION (0x7fbb9f3feb80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync): 0
166351415 OS file reads, 6987933 OS file writes, 3639237 OS fsyncs
156.66 reads/s, 7878 avg bytes/read, 71.50 writes/s, 13.17 fsyncs/s
-------------
INSERT BUFFER
-------------
size 6, free list len 11863, seg size 11870, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
---
LOG
---
Log sequence number 17970676587722
Log flushed up to 17970676583883
Pages flushed up to 17970643193368
Last checkpoint at 17970643193368
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2181038080
Dictionary memory allocated 92637472
Buffer pool size 129791
Free buffers 1396
Database pages 128387
Old database pages 47412
Modified db pages 80
Percent of dirty pages(LRU & free pages): 0.062
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 15
Pages made young 196468759, not young 3200160866
65.33 youngs/s, 3222.73 non-youngs/s
Pages read 166314664, created 2924018, written 6791751
156.66 reads/s, 3.00 creates/s, 70.53 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 2 / 1000 not 108 / 1000
Pages read ahead 81.03/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 128387, unzip_LRU len: 0
I/O sum[10256]:cur[2292], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 read views open inside InnoDB
state: sleeping
----------------------------
END OF INNODB MONITOR OUTPUT
============================