Joe, I think I see why this can happen. Even though the search condition is unique, the search may end up on the B-tree leaf node which immediately precedes the right node. That is why Transaction 1 is waiting for a lock on a 'supremum' record. It is the supremum of the previous leaf.
Hmm... I may improve the B-tree search algorithm to avoid this. Or relax lock wait conditions on 'supremums'. Thank you for the bug report, Heikki ----- Original Message ----- From: "Joe Shear" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, September 12, 2003 2:43 AM Subject: Re: innodb deadlock issue > user_id is the primary key.. > > relevant parts of show create table: > | plx_user |CREATE TABLE `plx_user` ( > `user_id` int(11) NOT NULL auto_increment, > `is_deleted` int(11) NOT NULL default '0', > `edit_counter` int(11) NOT NULL default '0', > `sendlist_edit_counter` int(11) NOT NULL default '0', > PRIMARY KEY (`user_id`), > ) TYPE=InnoDB | > > and from the explains: > mysql> EXPLAIN SELECT * from plx_user where user_id = 190864 and > is_deleted = 0; > +----------+-------+---------------+---------+---------+-------+------+----- --+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +----------+-------+---------------+---------+---------+-------+------+----- --+ > | plx_user | const | PRIMARY | PRIMARY | 4 | const | 1 > | | > +----------+-------+---------------+---------+---------+-------+------+----- --+ > EXPLAIN SELECT * from plx_user where user_id = 190864; > +----------+-------+---------------+---------+---------+-------+------+----- --+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +----------+-------+---------------+---------+---------+-------+------+----- --+ > | plx_user | const | PRIMARY | PRIMARY | 4 | const | 1 > | | > +----------+-------+---------------+---------+---------+-------+------+----- --+ > 1 row in set (0.05 sec) > > > > > On Thu, 2003-09-11 at 16:36, Heikki Tuuri wrote: > > Joe, > > > > what does SHOW CREATE TABLE give as the table definition? > > > > What do > > > > EXPLAIN SELECT ... where user_id = 190864 and is_deleted = 0; > > > > and > > > > EXPLAIN SELECT ... where user_id = 190864; > > > > say? If user_id is not the full primary key, then an index scan will happen > > and deadlocks of the type below are possible. > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > http://www.innodb.com > > Foreign keys, transactions, and row level locking for MySQL > > InnoDB Hot Backup - a hot backup tool for MySQL > > > > Order MySQL technical support from https://order.mysql.com/ > > > > ----- Original Message ----- > > From: "Joe Shear" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.myodbc > > Sent: Friday, September 12, 2003 1:53 AM > > Subject: innodb deadlock issue > > > > > > > I noticed this deadlock in show innodb status on a mysql 4.0.14 box > > > today. Both transactions seem to be trying to execute the a query on > > > the same record, which I don't think should cause a deadlock. What am I > > > missing? > > > > > > 030911 10:12:45 > > > *** (1) TRANSACTION: > > > TRANSACTION 0 153127600, ACTIVE 53 sec, process no 18814, OS thread id > > > 484323467 starting index read, thread declared inside InnoDB 500 > > > LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 4 > > > MySQL thread id 1166811, query id 35192298 plaxo.com Updating > > > update plx_user set sendlist_edit_counter = 53, modified = now() > > > where user_id = 190864 and is_deleted = 0 > > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: > > > RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user > > > index PRIMARY trx id 0 153127600 lock_mode X waiting > > > Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex > > > 73757072656d756d00; asc supremum.;; > > > *** (2) TRANSACTION: > > > TRANSACTION 0 153131209, ACTIVE 8 sec, process no 17896, OS thread id > > > 480604175 starting index read, thread declared inside InnoDB 0 > > > 3 lock struct(s), heap size 320 > > > MySQL thread id 1165903, query id 35188878 plaxo.com Updating > > > update plx_user set edit_counter = edit_counter + 1, modified = > > > now() where user_id = 190864 > > > *** (2) HOLDS THE LOCK(S): > > > RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user > > > index PRIMARY trx id 0 153131209 lock_mode X > > > 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 6829214 n bits 96 table user/plx_user > > > index PRIMARY trx id 0 153131209 lock_mode X locks rec but not gap > > > waiting > > > Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 8002e990; asc > > > ....;; 1: len 6; hex 000009208ab0; asc ... ..;; > > > *** WE ROLL BACK TRANSACTION (2) > > > > > > -- > > > Joe Shear <[EMAIL PROTECTED]> > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > Joe Shear <[EMAIL PROTECTED]> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
