mysql> show global status like '%Innodb_row_lock_current_waits%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 2 | +-------------------------------+-------+ 1 row in set (0.00 sec) mysql> show processlist ; +----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 1103 | Waiting on empty queue | NULL | | 45 | root | localhost | information_schema | Query | 0 | starting | show processlist | +----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+ 2 rows in set (0.00 sec) mysql> show global status like '%Innodb_row_lock_current_waits%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 2 | +-------------------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW ENGINE INNODB STATUS \G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2019-03-25 06:51:36 0x7f564c1aa700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 46 srv_active, 0 srv_shutdown, 1069 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 205 OS WAIT ARRAY INFO: signal count 260 RW-shared spins 494, rounds 620, OS waits 126 RW-excl spins 215, rounds 1383, OS waits 31 RW-sx spins 15, rounds 195, OS waits 3 Spin rounds per wait: 1.26 RW-shared, 6.43 RW-excl, 13.00 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-03-25 06:49:15 0x7f5634219700 *** (1) TRANSACTION: TRANSACTION 5053, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 112, OS thread handle 140008619550464, query id 10098 172.17.0.1 root updating DELETE FROM sbtest1 WHERE id=5045 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 6 page no 101 n bits 144 index PRIMARY of table `DB248671`.`sbtest1` trx id 5053 lock_mode X locks rec but not gap waiting Record lock, heap no 46 PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len 4; hex 800013b5; asc ;; 1: len 6; hex 0000000013ab; asc ;; 2: len 7; hex 010000022c0468; asc , h;; 3: len 4; hex 80000804; asc ;; 4: len 30; hex 38323334323138313034332d36333836363433303930392d393033393734; asc 82342181043-63866430909-903974; (total 120 bytes); 5: len 30; hex 34373836323931363138342d33353239383936323037302d313030333037; asc 47862916184-35298962070-100307; (total 60 bytes); *** (2) TRANSACTION: TRANSACTION 5035, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3 MySQL thread id 118, OS thread handle 140008218531584, query id 10357 172.17.0.1 root update INSERT INTO sbtest1 (id, k, c, pad) VALUES (5045, 4985, '97556727135-97185965815-14832765717-93225430987-93753515563-18588139289-06962548127-15912952070-21662256451-07027890440', '02931374635-35284371082-33740492837-19112571419-63209910036') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 6 page no 101 n bits 144 index PRIMARY of table `DB248671`.`sbtest1` trx id 5035 lock_mode X locks rec but not gap Record lock, heap no 46 PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len 4; hex 800013b5; asc ;; 1: len 6; hex 0000000013ab; asc ;; 2: len 7; hex 010000022c0468; asc , h;; 3: len 4; hex 80000804; asc ;; 4: len 30; hex 38323334323138313034332d36333836363433303930392d393033393734; asc 82342181043-63866430909-903974; (total 120 bytes); 5: len 30; hex 34373836323931363138342d33353239383936323037302d313030333037; asc 47862916184-35298962070-100307; (total 60 bytes); *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 6 page no 101 n bits 144 index PRIMARY of table `DB248671`.`sbtest1` trx id 5035 lock mode S waiting Record lock, heap no 46 PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len 4; hex 800013b5; asc ;; 1: len 6; hex 0000000013ab; asc ;; 2: len 7; hex 010000022c0468; asc , h;; 3: len 4; hex 80000804; asc ;; 4: len 30; hex 38323334323138313034332d36333836363433303930392d393033393734; asc 82342181043-63866430909-903974; (total 120 bytes); 5: len 30; hex 34373836323931363138342d33353239383936323037302d313030333037; asc 47862916184-35298962070-100307; (total 60 bytes); *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 5122 Purge done for trx's n:o < 5122 undo n:o < 0 state: running but idle History list length 65 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421483746723664, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (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: 0 871 OS file reads, 3320 OS file writes, 561 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 0, seg size 2, 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 0 buffer(s) Hash table size 34679, node heap has 8 buffer(s) Hash table size 34679, node heap has 3 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 33946898 Log buffer assigned up to 33946898 Log buffer completed up to 33946898 Log written up to 33946898 Log flushed up to 33946898 Added dirty pages up to 33946898 Pages flushed up to 33946898 Last checkpoint at 33946898 1425 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 407293 Buffer pool size 8192 Free buffers 6846 Database pages 1334 Old database pages 473 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 846, created 488, written 1741 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: 1334, 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=1, Main thread ID=140008260495104 , state=sleeping Number of rows inserted 10535, updated 1287, deleted 495, read 207644 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.00 sec) mysql> show global status like '%Innodb_row_lock_current_waits%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 2 | +-------------------------------+-------+ 1 row in set (0.00 sec) mysql> show processlist ; +----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 1125 | Waiting on empty queue | NULL | | 45 | root | localhost | information_schema | Query | 0 | starting | show processlist | +----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+ 2 rows in set (0.00 sec) mysql> select * from sys.innodb_lock_waits; Empty set (0.14 sec)