dear all! I am running mysql 4.0.13 (Linux-X86) my innodb table accasionally comes to a deadlock, when i run some sql such a "delete from tb_memfee where no='2000006681'". if it come to a deadlock, i cannot update any column of the row - where no is '2000006681' in the table tb_memfee.
how can i solve the problem? please help. thank you. # the following is a result of "show innodb status" when i met a deadlock. mysql> show innodb status; ===================================== 030611 9:43:50 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 25 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 124502, signal count 123284 Mutex spin waits 2765914, rounds 10463114, OS waits 34375 RW-shared spins 144868, OS waits 71456; RW-excl spins 6603, OS waits 4664 ------------ TRANSACTIONS ------------ Trx id counter 0 714450041 Purge done for trx's n:o < 0 713888592 undo n:o < 0 0 Total number of lock structs in row lock hash table 33 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 714447966, not started, process no 12337, OS thread id 794675 MySQL thread id 12217, query id 1209966 db1 10.10.7.1 dbdba ... cut cut cut ... ---TRANSACTION 0 714440674, not started, process no 17431, OS thread id 90124 MySQL thread id 10, query id 1195297 db2 10.10.7.2 dbdba ---TRANSACTION 0 714450019, ACTIVE 18 sec, process no 17756, OS thread id 229405 starting index read LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 65, query id 1214253 db2 10.10.7.2 web updating delete from tb_memfee where no='2000006681' ------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 170373 n bits 152 table pay/tb_memfee index PRIMARY trx id 0 71445001 9 lock_mode X waiting Record lock, heap no 38 RECORD: info bits 32 0: len 10; hex 32303030303036363831; asc 2000006681;; 1: ------------------ ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id 98317 19 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 9, query id 1213960 db2 10.10.7.2 web Trx read view will not see trx with id >= 0 713900170, sees < 0 713875499 ---TRANSACTION 0 713875499, ACTIVE 52484 sec, process no 17530, OS thread id 155668 22 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 27, query id 1214277 db2 10.10.7.2 web Trx read view will not see trx with id >= 0 713888581, sees < 0 713875612 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request 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 1078482 OS file reads, 229960 OS file writes, 113777 OS fsyncs 0.16 reads/s, 16384 avg bytes/read, 2.96 writes/s, 1.12 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 4286, seg size 4288, 36392 inserts, 36392 merged recs, 26899 merges Hash table size 1593833, used cells 99888, node heap has 102 buffer(s) 0.44 hash searches/s, 3.92 non-hash searches/s --- LOG --- Log sequence number 5 3656343850 Log flushed up to 5 3656343850 Last checkpoint at 5 3656343850 0 pending log writes, 0 pending chkp writes 83440 log i/o's done, 0.36 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 458854006; in additional pool allocated 2259968 Buffer pool size 24576 Free buffers 2 Database pages 24472 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 2974610, created 1792, written 148676 0.16 reads/s, 0.00 creates/s, 2.60 writes/s Buffer pool hit rate 993 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no 17421, state: sleeping Number of rows inserted 69660, updated 13094, deleted 50050, read 223213315 0.12 inserts/s, 0.12 updates/s, 0.00 deletes/s, 6.64 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
