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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
