Hi! It is not a deadlock. These two transactions have been dangling uncommitted for 14 hours:
> ---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 Remember to commit your transactions. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "¶óÀϱ¸" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 11, 2003 4:42 PM Subject: deadlock problem? > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
