Connection 3> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2020-09-06 12:26:25 0x875c INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 26 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 360 srv_active, 0 srv_shutdown, 35385 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 428 OS WAIT ARRAY INFO: signal count 414 RW-shared spins 46, rounds 51, OS waits 5 RW-excl spins 126, rounds 2190, OS waits 70 RW-sx spins 23, rounds 632, OS waits 20 Spin rounds per wait: 1.11 RW-shared, 17.38 RW-excl, 27.48 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-09-06 12:24:20 0x8650 *** (1) TRANSACTION: TRANSACTION 541847, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 171, OS thread handle 31048, query id 49358 localhost ::1 root updating UPDATE world.city SET Population = Population + 1 WHERE ID = 130 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 1940 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 541847 lock_mode X locks rec but not gap waiting Record lock, heap no 131 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000082; asc ;; 1: len 6; hex 000000083d95; asc = ;; 2: len 7; hex 8100000127079e; asc ' ;; 3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes); 4: len 3; hex 415553; asc AUS;; 5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;; 6: len 4; hex 8031fdaf; asc 1 ;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1940 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 541847 lock_mode X locks rec but not gap waiting Record lock, heap no 131 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000082; asc ;; 1: len 6; hex 000000083d95; asc = ;; 2: len 7; hex 8100000127079e; asc ' ;; 3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes); 4: len 3; hex 415553; asc AUS;; 5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;; 6: len 4; hex 8031fdaf; asc 1 ;; *** (2) TRANSACTION: TRANSACTION 541848, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 170, OS thread handle 12104, query id 49360 localhost ::1 root updating UPDATE world.city SET Population = Population + 1 WHERE ID = 130 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1940 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 541848 lock mode S locks rec but not gap Record lock, heap no 131 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000082; asc ;; 1: len 6; hex 000000083d95; asc = ;; 2: len 7; hex 8100000127079e; asc ' ;; 3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes); 4: len 3; hex 415553; asc AUS;; 5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;; 6: len 4; hex 8031fdaf; asc 1 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1940 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 541848 lock_mode X locks rec but not gap waiting Record lock, heap no 131 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000082; asc ;; 1: len 6; hex 000000083d95; asc = ;; 2: len 7; hex 8100000127079e; asc ' ;; 3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes); 4: len 3; hex 415553; asc AUS;; 5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;; 6: len 4; hex 8031fdaf; asc 1 ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 541850 Purge done for trx's n:o < 541850 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283565038602848, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283565038602016, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283565038601184, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283565038600352, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283565038599520, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283565038598688, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283565038597856, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 1 1800 OS file reads, 442921 OS file writes, 10733 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 13, seg size 15, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 273 buffer(s) Hash table size 34679, node heap has 7 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 7 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 6082452138 Log buffer assigned up to 6082452138 Log buffer completed up to 6082452138 Log written up to 6082452138 Log flushed up to 6082452138 Added dirty pages up to 6082452138 Pages flushed up to 6082452138 Last checkpoint at 6082452138 424616 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137363456 Dictionary memory allocated 643386 Buffer pool size 8192 Free buffers 1024 Database pages 6876 Old database pages 2518 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3399, not young 758698 0.00 youngs/s, 0.00 non-youngs/s Pages read 1746, created 9841, written 13021 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 6876, unzip_LRU len: 0 I/O sum[0]: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=33984, Main thread ID=0000000000006758 , state=sleeping Number of rows inserted 3919015, updated 8346, deleted 0, read 49199 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 544, updated 730, deleted 544, read 10013 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.0009 sec)