Hi, I'm running mysql 3.23.52 w/ innodb tables, and I started getting some deadlocks since upgrading from .51. When I do a show innodb status in prints out the following:
020826 19:22:15 LATEST DETECTED DEADLOCK: *** (1) TRANSACTION: TRANSACTION 0 16655549, ACTIVE 1 sec, OS thread id 87339022 inserting LOCK WAIT 6 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 21314, query id 7772397 10.1.0.1 10.1.0.2 pas update INSERT plx_contact_field (user_id, entry_id, field_id, value) values (8, 0, 0, "name107788") *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540698 n bits 272 table user/plx_contact_field index PRIMARY trx id 0 16655549 lock_mode X waiting Record lock, heap no 1 *** (2) TRANSACTION: TRANSACTION 0 16655527, ACTIVE 2 sec, OS thread id 87326732 inserting 9 lock struct(s), heap size 1024, undo log entries 5 MySQL thread id 21311, query id 7772401 10.1.0.1 10.1.0.2 pas update INSERT plx_contact_field (user_id, entry_id, field_id, value) values (7, 1983, 2, "pubemail1-1958") *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540698 n bits 272 table user/plx_contact_field index PRIMARY trx id 0 16655527 lock_mode X Record lock, heap no 1 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540698 n bits 272 table user/plx_contact_field index PRIMARY trx id 0 16655527 lock_mode X waiting Record lock, heap no 1 *** WE ROLL BACK TRANSACTION (2) It seems from the printout, that's what happening is that transaction 2 obtains a lock on a row, then tries to obtain it again, while transaction 1 is waiting for that lock, and this causes a deadlock to be detected. Is this the expected behavior? We are running in serializable. I would think that transaction 2 should first check to see if it already has the lock before it runs through and detects a deadlock. thanks for your help, Joe --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
