MySQL Forums
Forum List  »  InnoDB

Lock timeout
Posted by: David Wynter
Date: June 30, 2005 02:43PM

I have a single thread working doing an insert. I get the following output from InnoDb status. I cannot understand why it is locking, looking at the output does not seem to tell me why, a single transaction running on one thread?

=====================================
050630 22:39:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28, signal count 28
Mutex spin waits 38, rounds 372, OS waits 8
RW-shared spins 28, OS waits 14; RW-excl spins 5, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 57373881
Purge done for trx's n:o < 0 57373869 undo n:o < 0 0
History list length 3
Total number of lock structs in row lock hash table 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 57373869, not started, OS thread id 3452
MySQL thread id 23, query id 6091 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 0, not started, OS thread id 2696
MySQL thread id 19, query id 6083 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 0, not started, OS thread id 3672
MySQL thread id 18, query id 6009 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 57373880, not started, OS thread id 2300
MySQL thread id 16, query id 6433 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 0, not started, OS thread id 2308
MySQL thread id 8, query id 1187 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, OS thread id 3600
MySQL thread id 3, query id 6451 localhost 127.0.0.1 root
SHOW INNODB STATUS
---TRANSACTION 0 57373878, ACTIVE 46 sec, OS thread id 3188 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 21, query id 6335 ROAMWARE-2EG62K 192.168.0.6 tLoader update
INSERT INTO CONVERTIBLE_BOND_INFO (STK_BUYBACK_CD, SETTLE_PERIOD_VAL, TYPE_CD, CONV_CURR_CD, SETTLE_PERIOD_CD, EXERCISE_CD, UNDERLYING_ASSET_DESC, BASKET_WEIGHT_CD, UNDERLYING_ASSET_ID, PREMIUM_AT_ISSUE, DILUTION_PROTECT_FL, PROCESSED, CONV_INT_CD, ASSET_ID, CONVERSION_RATE, NOTICE_PERIOD_VAL, ISSUE
------- TRX HAS BEEN WAITING 46 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 44132 n bits 736 index `CONVERTIBLE_BOND_INFO_FK_1` of table `securitymaster/convertible_bond_info` trx id 0 57373878 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 8; hex 800246000185c808; asc F …È ;; 1: len 8; hex 800000000000045b; asc [;;

------------------
---TRANSACTION 0 57373871, ACTIVE 137 sec, OS thread id 3608
4 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 24, query id 6090 ROAMWARE-2EG62K 192.168.0.6 tLoader
--------
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
2335 OS file reads, 143 OS file writes, 62 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 for space 0: size 1, free list len 5, seg size 7, is empty
Ibuf for space 0: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 3320377, used cells 5335, node heap has 6 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 6 4264099529
Log flushed up to 6 4264099529
Last checkpoint at 6 4264099529
0 pending log writes, 0 pending chkp writes
39 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 895864448; in additional pool allocated 1122944
Buffer pool size 51200
Free buffers 48868
Database pages 2326
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2325, created 1, written 95
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
Main thread id 2976, state: sleeping
Number of rows inserted 12, updated 6, deleted 14, read 50402
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Win 2003 Std, Java 1.4.2_06, MySQL 4.1.12a

Ideas on where I should look next?

David

Options: ReplyQuote


Subject
Views
Written By
Posted
Lock timeout
4212
June 30, 2005 02:43PM
2225
July 06, 2005 03:56PM
2560
July 06, 2005 04:54PM
2521
July 07, 2005 01:34AM
2251
July 07, 2005 06:09AM


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.