deadlocks in the innodb status
- by shantanuo
Mysql sever has suddenly become very slow. There are no queries in the slow query log but the innodb status shows something like the following. Does it mean that it is due to innodb deadlock? if Yes, what is the way out?
*************************** 1. row ***************************
Status:
=====================================
100315 12:55:29 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 187532, signal count 188120
Mutex spin waits 0, rounds 61908654, OS waits 33052
RW-shared spins 89241, OS waits 41948; RW-excl spins 5857, OS waits 1557
------------------------
LATEST DETECTED DEADLOCK
------------------------
100315 12:43:02
*** (1) TRANSACTION:
TRANSACTION 0 56996536, ACTIVE 0 sec, process no 5000, OS thread id 3031395216 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, undo log entries 6
MySQL thread id 994, query id 7699751 localhost application Searching rows for update
UPDATE QUERY
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4073 n bits 296 index `PRIMARY` of table `dbII/tbl_ticket_block_master` trx id 0 56996536 lock_mode X locks r
ec but not gap waiting
Record lock, heap no 141 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 7; hex 33353837393936; asc 3587996;; 1: len 4; hex 800001f4; asc ;; 2: len 1; hex 47; asc G;; 3: len 2; hex 6f6b; asc ok;; 4: le
n 6; hex 0000035957fe; asc YW ;; 5: len 7; hex 000000401737c0; asc @ 7 ;; 6: SQL NULL; 7: SQL NULL; 8: SQL NULL; 9: len 3; hex 8fb46e;
asc n;; 10: SQL NULL; 11: len 1; hex 30; asc 0;; 12: len 0; hex ; asc ;; 13: SQL NULL; 14: len 1; hex 33; asc 3;; 15: len 4; hex 4b9ceebe
; asc K ;; 16: len 1; hex 30; asc 0;; 17: len 4; hex 80006ae8; asc j ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ;
asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 56996527, ACTIVE 0 sec, process no 5000, OS thread id 2961476496 fetching rows, thread declared inside InnoDB 237
mysql tables in use 3, locked 3
121 lock struct(s), heap size 11584, undo log entries 16
MySQL thread id 995, query id 7699729 localhost application Searching rows for update
UPDATE QUERY
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 4073 n bits 296 index `PRIMARY` of table `DBII/tbl_ticket_block_master` trx id 0 56996527 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 7; hex 33353837343631; asc 3587461;; 1: len 4; hex 800001f4; asc ;; 2: len 1; hex 47; asc G;; 3: len 6; hex 497373756564; asc Is
sued;; 4: len 6; hex 000003425295; asc BR ;; 5: len 7; hex 8000000464012c; asc d ,;; 6: SQL NULL; 7: len 4; hex 80000058; asc X;;
8: len 1; hex 43; asc C;; 9: len 3; hex 8fb465; asc e;; 10: len 3; hex 8fb46d; asc m;; 11: len 1; hex 30; asc 0;; 12: len 0; hex ; asc ;
; 13: SQL NULL; 14: len 1; hex 33; asc 3;; 15: len 4; hex 4b9b33a2; asc K 3 ;; 16: len 3; hex 756d67; asc umg;; 17: len 4; hex 80006744; asc
gD;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;;
