===================================== 121107 11:52:00 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 5 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 31342, signal count 29550 Mutex spin waits 0, rounds 840109, OS waits 12568 RW-shared spins 33962, OS waits 15079; RW-excl spins 19418, OS waits 2663 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 121107 0:41:00 *** (1) TRANSACTION: TRANSACTION 0 440484985, ACTIVE 2 sec, process no 12377, OS thread id 1159248192 fetching rows mysql tables in use 1, locked 1 LOCK WAIT 121 lock struct(s), heap size 14320, undo log entries 1120 MySQL thread id 322765, query id 11756708 localhost sciennet_rpausic UPDATE account SET total = total + 1.35 WHERE id = 1 and type_id = 2 and country_code = 'at' and acc_id > 3602500 and active = 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 55117 n bits 144 index `PRIMARY` of table `test_table/account` trx id 0 440484985 lock_mode X waiting Record lock, heap no 72 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 0: len 4; hex 80372b07; asc 7+ ;; 1: len 6; hex 00001a414478; asc ADx;; 2: len 7; hex 8000000e110110; asc ;; 3: len 4; hex 8000153d; asc =;; 4: len 2; hex 8002; asc ;; 5: len 4; hex 50999fc8; asc P ;; 6: len 30; hex 31342f3134202d726520544b3250503120573135313639392028706f6b6c; asc some note (pokl;...(truncated); 7: len 4; hex 80047854; asc xT;; 8: len 4; hex 8003bd14; asc ;; 9: len 5; hex 8000000219; asc ;; 10: len 5; hex 800001d241; asc A;; 11: len 5; hex 8000041419; asc ;; 12: len 5; hex 800002413c; asc A<;; 13: len 5; hex 8000000000; asc ;; 14: len 5; hex 8000000000; asc ;; 15: len 2; hex 8003; asc ;; 16: len 4; hex 5099a00a; asc P ;; 17: len 4; hex 80025093; asc P ;; 18: len 4; hex 80000000; asc ;; 19: len 3; hex 617420; asc at ;; 20: len 3; hex 455552; asc EUR;; 21: len 1; hex 81; asc ;; 22: len 1; hex 81; asc ;; *** (2) TRANSACTION: TRANSACTION 0 440484984, ACTIVE 2 sec, process no 12377, OS thread id 1160579392 starting index read, thread declared inside InnoDB 134 mysql tables in use 5, locked 1 3715 lock struct(s), heap size 374768, undo log entries 1 MySQL thread id 335499, query id 11756746 localhost sciennet_rpausic Sorting result SELECT * FROM account WHERE id=1 AND type_id=2 AND country_code='at' AND active=1 ORDER BY acc_id DESC LIMIT 1 FOR UPDATE *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 55117 n bits 144 index `PRIMARY` of table `test_table/account` trx id 0 440484984 lock_mode X locks rec but not gap Record lock, heap no 72 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 0: len 4; hex 80372b07; asc 7+ ;; 1: len 6; hex 00001a414478; asc ADx;; 2: len 7; hex 8000000e110110; asc ;; 3: len 4; hex 8000153d; asc =;; 4: len 2; hex 8002; asc ;; 5: len 4; hex 50999fc8; asc P ;; 6: len 30; hex 31342f3134202d726520544b3250503120573135313639392028706f6b6c; asc some note (pokl;...(truncated); 7: len 4; hex 80047854; asc xT;; 8: len 4; hex 8003bd14; asc ;; 9: len 5; hex 8000000219; asc ;; 10: len 5; hex 800001d241; asc A;; 11: len 5; hex 8000041419; asc ;; 12: len 5; hex 800002413c; asc A<;; 13: len 5; hex 8000000000; asc ;; 14: len 5; hex 8000000000; asc ;; 15: len 2; hex 8003; asc ;; 16: len 4; hex 5099a00a; asc P ;; 17: len 4; hex 80025093; asc P ;; 18: len 4; hex 80000000; asc ;; 19: len 3; hex 617420; asc at ;; 20: len 3; hex 455552; asc EUR;; 21: len 1; hex 81; asc ;; 22: len 1; hex 81; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 63384 n bits 184 index `PRIMARY` of table `test_table/account` trx id 0 440484984 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 0: len 4; hex 8036f84a; asc 6 J;; 1: len 6; hex 00001a414479; asc ADy;; 2: len 7; hex 0000000e180110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 2; hex 8002; asc ;; 5: len 4; hex 5098f075; asc P u;; 6: len 30; hex 544b315050343120284b6172696a65726e6920626f6e7573203a204e696b; asc some note;...(truncated); 7: len 4; hex 80047420; asc t ;; 8: len 4; hex 80000000; asc ;; 9: len 5; hex 8000000032; asc 2;; 10: len 5; hex 8000585f3c; asc X_<;; 11: len 5; hex 8000585f3c; asc X_<;; 12: len 5; hex 8000000000; asc ;; 13: len 5; hex 8000000000; asc ;; 14: len 5; hex 8000000000; asc ;; 15: len 2; hex 8004; asc ;; 16: len 4; hex 5099a00a; asc P ;; 17: len 4; hex 80020f25; asc %;; 18: len 4; hex 80000000; asc ;; 19: len 3; hex 617420; asc at ;; 20: len 3; hex 455552; asc EUR;; 21: len 1; hex 81; asc ;; 22: len 1; hex 81; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 440789481 Purge done for trx's n:o < 0 440789160 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 12377, OS thread id 1169365312 MySQL thread id 421931, query id 15273557 localhost root SHOW INNODB STATUS -------- 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 (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 62762 OS file reads, 230543 OS file writes, 63809 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 2407, seg size 2409, 8430 inserts, 8430 merged recs, 6442 merges Hash table size 2212699, used cells 1879877, node heap has 6175 buffer(s) 52.19 hash searches/s, 8.80 non-hash searches/s --- LOG --- Log sequence number 10 3092651740 Log flushed up to 10 3092651740 Last checkpoint at 10 3092651740 0 pending log writes, 0 pending chkp writes 44850 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1293727416; in additional pool allocated 971520 Buffer pool size 65536 Free buffers 49 Database pages 59312 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 80430, created 894, written 201326 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 12377, id 1155787072, state: sleeping Number of rows inserted 33013, updated 933650, deleted 13, read 98901653005 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 75605.48 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================