Joe, ----- Original Message ----- From: "Joe Shear" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, August 28, 2002 12:15 AM Subject: Innodb deadlock printouts in .52
> 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. have transactions (1) and (2) been reading the place where they are going to insert? If yes, and you run in the SERIALIZABLE mode, then you will very easily get a deadlock because they hold next-key locks over the insertion spot. If they have NOT read, updated, or deleted there, then the deadlock is probably removed in MySQL-4.0.3 which should be out this week. In the printout above a locking read, update, or delete may have set next-key locks on the page 'supremum' (highest) record. The inserts had to wait. To wait they set 'gap' type X-locks on the supremum. A gap type lock does not grant the right to insert, it is above used as a technical trick to leave the insert waiting. That is why (2) ignores the X-lock it already has on the supremum record. In InnoDB-3.23.52 the insert lock check was coarse: any waiting insert lock on the same gap caused another insert to wait. In 4.0.3 this has been improved. In 4.0.3 we have 4 types of locks on records: 1) ordinary next-key locks which lock the record and the gap before it; 2) gap locks only lock the gap before a record, not the record itself; different transactions can have conflicting locks on the gap at the same time, because when purge removes records, we may have to merge different gaps; gap locks are purely inhibitive, they are used to force other users to wait; 3) insert intention locks; 4) 'implicit' locks: every inserted record is X-locked by the inserting transaction. An insert in 4.0.3 only waits for type 1 and 2 locks to be released. The details of next-key locking are complex. That is why they have not been documented in the manual. The manual will be improved in the future to help users to interpret the printout of SHOW INNODB STATUS. See also http://www.innodb.com/ibman.html#Cope_with_deadlocks. A relevant function from the source of 4.0.3: /************************************************************************* Checks if a lock request for a new lock has to wait for request lock2. */ UNIV_INLINE ibool lock_rec_has_to_wait( /*=================*/ /* out: TRUE if new lock has to wait for lock2 to be removed */ trx_t* trx, /* in: trx of new lock */ ulint mode, /* in: LOCK_S or LOCK_X */ ulint gap, /* in: LOCK_GAP or 0 */ ulint insert_intention, /* in: LOCK_INSERT_INTENTION or 0 */ lock_t* lock2) /* in: another record lock; NOTE that it is assumed that this has a lock bit set on the same record as in lock1 */ { ut_ad(trx && lock2); ut_ad(lock_get_type(lock2) == LOCK_REC); ut_ad(mode == LOCK_S || mode == LOCK_X); ut_ad(gap == LOCK_GAP || gap == 0); ut_ad(insert_intention == LOCK_INSERT_INTENTION || insert_intention == 0); if (trx != lock2->trx && !lock_mode_compatible(mode, lock_get_mode(lock2))) { /* We have somewhat complex rules when gap type record locks cause waits */ if (!gap && lock_rec_get_insert_intention(lock2)) { /* Request of a full next-key record does not need to wait for an insert intention lock to be removed. This is ok since our rules allow conflicting locks on gaps. This eliminates a spurious deadlock caused by a next-key lock waiting for an insert intention lock; when the insert intention lock was granted, the insert deadlocked on the waiting next-key lock. */ return(FALSE); } if (insert_intention && lock_rec_get_insert_intention(lock2)) { /* An insert intention is not disturbed by another insert intention; this removes a spurious deadlock caused by inserts which had to wait for a next-key lock to be removed */ return(FALSE); } return(TRUE); } return(FALSE); } > thanks for your help, > Joe Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- 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
