Christophe, ----- Original Message ----- From: "Christophe Lombart" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, November 04, 2003 10:45 PM Subject: Deadlock
> Hello, > > I'm using MySQL 4.0.15 standard - InnoDB and I got a deadlock. I don't > see why ! > How Can I find the reason ? It seems to occurs on delete statments. this transaction wants to get a lock: > DELETE FROM SLIDE_REVISION_PREDECESSOR WHERE REVISION_ID = 101 but this trx: > update SLIDE_URI set PARENT_URI_ID = 1 where URI_ID = 101 holds a shared lock on the page supremum: > test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 120070 lock mode S > Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex > 73757072656d756d00; asc supremum.;; Why does the second trx have an S-lock? Have you used SELECT ... LOCK IN SHARE MODE; or are there FOREIGN KEY constraints which reference SLIDE_REVISION_PREDECESSOR? Your transactions set quite many locks: >27 lock struct(s), > 43 lock struct(s), Can you cut your transactions into smaller pieces, so that they would keep less locks? Do you have good indexes for the WHERE conditions? This bug fix in 4.0.16 may have relevance: Fixed a bug: contrary to what was said in the manual, in a locking read InnoDB set two record locks if a unique exact match search condition was used on a multi-column unique key. For a single column unique key it worked right. > Regards, > Christophe Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ > Here is my InnoDB status : > > ===================================== > Per second averages calculated from the last 51 seconds > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 539, signal count 189 > Mutex spin waits 800, rounds 10240, OS waits 328 > RW-shared spins 370, OS waits 188; RW-excl spins 35, OS waits 23 > ------------------------ > LATEST DETECTED DEADLOCK > ------------------------ > 031104 21:39:32 > *** (1) TRANSACTION: > TRANSACTION 0 119492, ACTIVE 7 sec, process no 3109, OS thread id 188446 > starting index read > mysql tables in use 1, locked 1 > LOCK WAIT 27 lock struct(s), heap size 2496, undo log entries 42 > MySQL thread id 38, query id 12022 localhost 127.0.0.1 root updating > DELETE FROM SLIDE_REVISION_PREDECESSOR WHERE REVISION_ID = 101 > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 0 page no 192 n bits 72 table > test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 119492 lock_mode > X waiting > Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex > 73757072656d756d00; asc supremum.;; > *** (2) TRANSACTION: > TRANSACTION 0 120070, ACTIVE 4 sec, process no 3095, OS thread id 131089 > starting index read, thread declared inside InnoDB 500 > mysql tables in use 1, locked 1 > 43 lock struct(s), heap size 5504, undo log entries 21 > MySQL thread id 24, query id 12050 localhost 127.0.0.1 root Updating > update SLIDE_URI set PARENT_URI_ID = 1 where URI_ID = 101 > *** (2) HOLDS THE LOCK(S): > RECORD LOCKS space id 0 page no 192 n bits 72 table > test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 120070 lock mode S > Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex > 73757072656d756d00; asc supremum.;; > *** (2) WAITING FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 0 page no 45 n bits 312 table test/SLIDE_URI index > PRIMARY trx id 0 120070 lock_mode X locks rec but not gap waiting > Record lock, heap no 245 RECORD: info bits 0 0: len 4; hex 80000065; asc > ...e;; 1: len 6; hex 00000001d2c4; asc ......;; > *** WE ROLL BACK TRANSACTION (2) > ------------ > TRANSACTIONS > ------------ > Trx id counter 0 120984 > Purge done for trx's n:o < 0 120974 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 120878, not started, process no 3112, OS thread id 200720 > MySQL thread id 41, query id 16537 localhost 127.0.0.1 root > ---TRANSACTION 0 120764, not started, process no 3111, OS thread id 196640 > MySQL thread id 40, query id 16625 localhost 127.0.0.1 root > ---TRANSACTION 0 120916, not started, process no 3110, OS thread id 192543 > MySQL thread id 39, query id 17002 localhost 127.0.0.1 root > ---TRANSACTION 0 120809, not started, process no 3108, OS thread id 184349 > MySQL thread id 37, query id 16538 localhost 127.0.0.1 root > ---TRANSACTION 0 120898, not started, process no 3107, OS thread id 180252 > MySQL thread id 36, query id 17001 localhost 127.0.0.1 root > ---TRANSACTION 0 120920, not started, process no 3106, OS thread id 176155 > MySQL thread id 35, query id 17004 localhost 127.0.0.1 root > ---TRANSACTION 0 120052, not started, process no 3105, OS thread id 172058 > MySQL thread id 34, query id 11808 localhost 127.0.0.1 root > ---TRANSACTION 0 120070, not started, process no 3095, OS thread id 131089 > MySQL thread id 24, query id 12050 localhost 127.0.0.1 root > ---TRANSACTION 0 119640, not started, process no 3087, OS thread id 98329 > MySQL thread id 16, query id 10305 localhost 127.0.0.1 root > ---TRANSACTION 0 119645, not started, process no 3086, OS thread id 94232 > MySQL thread id 15, query id 10616 localhost 127.0.0.1 root > ---TRANSACTION 0 120810, not started, process no 3085, OS thread id 90135 > MySQL thread id 14, query id 16539 localhost 127.0.0.1 root > ---TRANSACTION 0 120918, not started, process no 3084, OS thread id 86038 > MySQL thread id 13, query id 17003 localhost 127.0.0.1 root > ---TRANSACTION 0 119987, not started, process no 3082, OS thread id 77844 > MySQL thread id 11, query id 11686 localhost 127.0.0.1 root > ---TRANSACTION 0 119810, not started, process no 3081, OS thread id 73747 > MySQL thread id 10, query id 11306 localhost 127.0.0.1 root > ---TRANSACTION 0 119808, not started, process no 3080, OS thread id 69650 > MySQL thread id 9, query id 11625 localhost 127.0.0.1 root > ---TRANSACTION 0 120065, not started, process no 3077, OS thread id 57359 > MySQL thread id 6, query id 11946 localhost 127.0.0.1 root > ---TRANSACTION 0 120965, not started, process no 3075, OS thread id 49165 > MySQL thread id 4, query id 16559 localhost 127.0.0.1 root > ---TRANSACTION 0 120983, not started, process no 2967, OS thread id 45068 > MySQL thread id 3, query id 17043 localhost root > ---TRANSACTION 0 0, not started, process no 2960, OS thread id 40971 > MySQL thread id 2, query id 17046 localhost root > SHOW INNODB STATUS > ---TRANSACTION 0 118021, not started, process no 2955, OS thread id 36874 > MySQL thread id 1, query id 14 localhost root > -------- > 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 > 219 OS file reads, 233 OS file writes, 135 OS fsyncs > 0.02 reads/s, 16384 avg bytes/read, 4.41 writes/s, 2.49 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf for space 0: size 1, free list len 0, seg size 2, > 0 inserts, 0 merged recs, 0 merges > Hash table size 34679, used cells 1111, node heap has 2 buffer(s) > 854.10 hash searches/s, 1745.46 non-hash searches/s > --- > LOG > --- > Log sequence number 0 121662756 > Log flushed up to 0 121662756 > Last checkpoint at 0 121662756 > 0 pending log writes, 0 pending chkp writes > 127 log i/o's done, 2.27 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 17648088; in additional pool allocated 1028864 > Buffer pool size 512 > Free buffers 355 > Database pages 155 > Modified db pages 0 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 113, created 42, written 251 > 0.02 reads/s, 0.82 creates/s, 4.90 writes/s > Buffer pool hit rate 1000 / 1000 > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > Main thread process no. 2297, id 28680, state: waiting for server activity > Number of rows inserted 4898, updated 100, deleted 4728, read 54155 > 96.04 inserts/s, 1.96 updates/s, 92.70 deletes/s, 1061.74 reads/s > ---------------------------- > END OF INNODB MONITOR OUTPUT > ============================ > > > > > > > -- > 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]
