Hello List, I have this table that has a single row in it:
CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select * from quicktable; +---+----------+ | x | quick_id | +---+----------+ | 1 | 0 | +---+----------+ 1 row in set (0.00 sec) I have a large number of connections executing these queries: BEGIN: SELECT quick_id FROM quicktable FOR UPDATE; COMMIT; This works well until I hit a large number of concurrent connections (around 200), when I start getting deadlocks. Despite the fact, that I'm only selecting a single table. Here is the deadlock section from SHOW INNODB STATUS; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 071015 20:22:35 *** (1) TRANSACTION: TRANSACTION 0 79790779, ACTIVE 2 sec, process no 7658, OS thread id 1185077584 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 368 MySQL thread id 3961, query id 2102790 10.1.10.122 bnewton statistics SELECT x, quick_id FROM quicktable WHERE x=1 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790779 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000004c14f74; asc Ot;; 2: len 7; hex 0000098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) TRANSACTION: TRANSACTION 0 79790775, ACTIVE 2 sec, process no 7658, OS thread id 1191733584 2 lock struct(s), heap size 368 MySQL thread id 4094, query id 2102743 10.1.10.122 bnewton *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790775 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000004c14f74; asc Ot;; 2: len 7; hex 0000098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79791014 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000004c14f74; asc Ot;; 2: len 7; hex 0000098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH *** WE ROLL BACK TRANSACTION (2) Can anyone explain whats going on? Is there a limit for the number of concurrent transactions, before looking at the lock graph becomes too expensive? Is that documented somewhere? Thanks, William Newton ____________________________________________________________________________________ Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/