I am running MySQL 4.0.20 on redhat 9.
But i frequently face 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 tell me how to solve this problem? How does this problem happen?
But i frequently face 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 tell me how to solve this problem? How does this problem happen?