lock wait transaction time out


#1

I am running MySQL 4.0.20 on redhat 9.

But i always facing a problem of that the table is being lock.
i see from the process list that i am the only one trying to update the record and the rest of the connection do nothing. Below is the log file i get when i try to show the innodb status when the table is lock.

—TRANSACTION 0 39597242, not started, process no 6755, OS thread id 36874

MySQL thread id 1, query id 3913 192.168.100.30 root

—TRANSACTION 0 39662855, ACTIVE 1 sec, process no 6777, OS thread id 98328

mysql tables in use 1, locked 0

MySQL thread id 16, query id 69698 192.168.100.125 root Sending data

SELECT * FROM proditem
Trx read view will not see trx with id >= 0 39662856, sees < 0 39615888

—TRANSACTION 0 39615888, ACTIVE 1831 sec, process no 6761, OS thread id 61455

1610 lock struct(s), heap size 93504, undo log entries 1

MySQL thread id 7, query id 60176 192.168.100.37 root

Trx read view will not see trx with id >= 0 39615896, sees < 0 39615896


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

152857 OS file reads, 2407 OS file writes, 1065 OS fsyncs

147.89 reads/s, 21774 avg bytes/read, 0.09 writes/s, 0.09 fsyncs/s


INSERT BUFFER AND ADAPTIVE HASH INDEX


Ibuf for space 0: size 1, free list len 5, seg size 7,

481 inserts, 481 merged recs, 321 merges
Hash table size 34679, used cells 1200, node heap has 2 buffer(s)

739.43 hash searches/s, 529.01 non-hash searches/s

LOG

Log sequence number 6 883631228
Log flushed up to 6 883631228

Last checkpoint at 6 883631228
0 pending log writes, 0 pending chkp writes

682 log i/o’s done, 0.04 log i/o’s/second

BUFFER POOL AND MEMORY

Total memory allocated 18582296; in additional pool allocated 1048576
Buffer pool size 512

Free buffers 0
Database pages 505
Modified db pages 0

Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0

Pages read 225724, created 8, written 1675
196.55 reads/s, 0.00 creates/s, 0.02 writes/s

Buffer pool hit rate 950 / 1000


ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue

Main thread process no. 6738, id 28680, state: sleeping

Number of rows inserted 119, updated 165, deleted 7, read 2443535

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1481.69 reads/s

I dont know how to analyze this, can someone expert tell me how to solve this problem? How this problem happen?


#2

I suspect you will get better luck on a forum specifically for mysql such as http://forums.mysql.com/ . Although this particular group on this board is focused on MySQL, It, typically, is more geared for the querries and interaction with PHP.

My intent is not to discourage you from posting here, but to know what you are up against.

If however, you do find a solution, (on another forum or here), please post the solution here so that others may benefit.

Good luck in your search, and I only wish I could be more helpful.