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]
