lock wait transaction time out

[jayson_13]

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?

[Author]

Posts