MySQL Forums
Forum List  »  InnoDB

Re: InnoDB Locking Problem
Posted by: Geoff Mina
Date: June 16, 2005 01:43PM

Here is an updated SHOW INNODB STATUS with the innodb_lock_monitor option enabled:


Status

=====================================
050616 15:42:19 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 72, signal count 72
Mutex spin waits 25, rounds 227, OS waits 4
RW-shared spins 118, OS waits 59; RW-excl spins 7, OS waits 7
------------------------
LATEST FOREIGN KEY ERROR
------------------------
050616 15:26:41 Cannot drop table `intelliqueue/tbl_acd_agent_logins`
because it is referenced by `intelliqueue/tbl_acd_agent_login_gates`
------------
TRANSACTIONS
------------
Trx id counter 0 3081132
Purge done for trx's n:o < 0 3081118 undo n:o < 0 0
History list length 7
Total number of lock structs in row lock hash table 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 3081128, not started, OS thread id 492
MySQL thread id 41, query id 16086 localhost 127.0.0.1 IntelliQueue
---TRANSACTION 0 3081130, not started, OS thread id 2148
MySQL thread id 40, query id 16092 localhost 127.0.0.1 IntelliQueue
---TRANSACTION 0 3081131, not started, OS thread id 3420
MySQL thread id 39, query id 16100 localhost 127.0.0.1 IntelliQueue
---TRANSACTION 0 3081083, not started, OS thread id 272
MySQL thread id 17, query id 16101 localhost 127.0.0.1 root
SHOW innodb status
---TRANSACTION 0 3081127, ACTIVE 4 sec, OS thread id 3208 inserting
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 42, query id 16068 localhost 127.0.0.1 IntelliQueue Sending data
INSERT INTO tbl_acd_agent_logins SELECT * FROM tbl_acd_agent_logins_history WHERE agent_login_id = 34078722
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1544 n bits 72 index `agent_id_unique` of table `intelliqueue/tbl_acd_agent_logins` trx id 0 3081127 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 32
0: len 4; hex 80000155; asc U;; 1: len 8; hex 8000000002080002; asc ;;

------------------
TABLE LOCK table `intelliqueue/tbl_acd_agent_logins_history` trx id 0 3081127 lock mode IS
RECORD LOCKS space id 0 page no 1547 n bits 72 index `PRIMARY` of table `intelliqueue/tbl_acd_agent_logins_history` trx id 0 3081127 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 16; 1-byte offs TRUE; info bits 0
0: len 8; hex 8000000002080002; asc ;; 1: len 6; hex 0000002f039c; asc / ?;; 2: len 7; hex 8000000a440084; asc D ?;; 3: len 4; hex 80000155; asc U;; 4: len 10; hex 39353436343936373037; asc 9546496707;; 5: len 9; hex 415641494c41424c45; asc AVAILABLE;; 6: len 8; hex 8000123c65da7858; asc <e?xX;; 7: len 8; hex 8000123c65da7858; asc <e?xX;; 8: len 8; hex 8000123c65da7860; asc <e?x`;; 9: len 4; hex 80000000; asc ;; 10: SQL NULL, size 0 ; 11: SQL NULL, size 8 ; 12: len 8; hex 8000123c65da7858; asc <e?xX;; 13: len 8; hex 8000123c65da785d; asc <e?x];; 14: len 10; hex 31323334353637383930; asc 1234567890;; 15: len 1; hex 80; asc ;;

TABLE LOCK table `intelliqueue/tbl_acd_agent_logins` trx id 0 3081127 lock mode IX
RECORD LOCKS space id 0 page no 52 n bits 72 index `PRIMARY` of table `intelliqueue/tbl_acd_agent_logins` trx id 0 3081127 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 16; 1-byte offs TRUE; info bits 0
0: len 8; hex 8000000002080002; asc ;; 1: len 6; hex 0000002f03a7; asc / ?;; 2: len 7; hex 00000006072cbb; asc ,?;; 3: len 4; hex 80000155; asc U;; 4: len 10; hex 39353436343936373037; asc 9546496707;; 5: len 9; hex 415641494c41424c45; asc AVAILABLE;; 6: len 8; hex 8000123c65da7858; asc <e?xX;; 7: len 8; hex 8000123c65da7858; asc <e?xX;; 8: len 8; hex 8000123c65da7860; asc <e?x`;; 9: len 4; hex 80000000; asc ;; 10: SQL NULL, size 0 ; 11: SQL NULL, size 8 ; 12: len 8; hex 8000123c65da7858; asc <e?xX;; 13: len 8; hex 8000123c65da785d; asc <e?x];; 14: len 10; hex 31323334353637383930; asc 1234567890;; 15: len 1; hex 80; asc ;;

RECORD LOCKS space id 0 page no 1544 n bits 72 index `agent_id_unique` of table `intelliqueue/tbl_acd_agent_logins` trx id 0 3081127 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 32
0: len 4; hex 80000155; asc U;; 1: len 8; hex 8000000002080002; asc ;;

---TRANSACTION 0 3081125, ACTIVE 4 sec, OS thread id 3736
2 lock struct(s), heap size 320
MySQL thread id 38, query id 16054 localhost 127.0.0.1 IntelliQueue
Trx read view will not see trx with id >= 0 3081126, sees < 0 3081126
TABLE LOCK table `intelliqueue/tbl_acd_agent_logins` trx id 0 3081125 lock mode IX
RECORD LOCKS space id 0 page no 1544 n bits 72 index `agent_id_unique` of table `intelliqueue/tbl_acd_agent_logins` trx id 0 3081125 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 32
0: len 4; hex 80000155; asc U;; 1: len 8; hex 8000000002080002; asc ;;

--------
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 (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
698 OS file reads, 1000 OS file writes, 361 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.86 writes/s, 0.64 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 5.93 non-hash searches/s
---
LOG
---
Log sequence number 0 65526154
Log flushed up to 0 65526154
Last checkpoint at 0 65526154
0 pending log writes, 0 pending chkp writes
196 log i/o's done, 0.36 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 14280490; in additional pool allocated 598272
Buffer pool size 512
Free buffers 1
Database pages 510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 688, created 4, written 726
0.00 reads/s, 0.00 creates/s, 1.36 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1148, state: sleeping
Number of rows inserted 45, updated 26, deleted 41, read 155
0.14 inserts/s, 0.07 updates/s, 0.14 deletes/s, 0.79 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Options: ReplyQuote


Subject
Views
Written By
Posted
3342
June 16, 2005 12:08PM
2035
June 16, 2005 12:20PM
Re: InnoDB Locking Problem
2226
June 16, 2005 01:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.