Lock timeout
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