List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:August 27 2002 10:45pm
Subject:Re: Innodb deadlock printouts in .52
View as plain text  
Joe,

----- Original Message -----
From: "Joe Shear" <joe@stripped>
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


Thread
Innodb deadlock printouts in .52Joe Shear28 Aug
Re: Innodb deadlock printouts in .52Heikki Tuuri28 Aug