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]
