List:General Discussion« Previous MessageNext Message »
From:Paul C. Power Date:May 1 2007 3:49pm
Subject:RE: InnoDB table lock on INSERT
View as plain text  
Baron-

Thank you for the InnoDB Lock Monitor pointer.  I now have a greate deal
of informaiton to digest.  I will try innotop when I have a chance.

:)
-Paul

> 
> Hi Paul,
> 
> Power, Paul C. wrote:
> > I have an INSERT waiting for a table lock, and i do not 
> understand why.
> >  
> > ---TRANSACTION 0 308691, ACTIVE 5 sec, process no 8876, OS thread id
> > 1296547864 inserting
> > mysql tables in use 1, locked 1
> > LOCK WAIT 1 lock struct(s), heap size 320 MySQL thread id 
> 79126, query 
> > id 1113322 bil.oneeighty.com 216.187.166.2 voxcall update 
> INSERT INTO 
> > voxinternal.Entity ( Entity_ID, Entity_Name, Entity_Type, Who, 
> > Ins_Date ) VALUES ( 'OLEO', 'Ole Matadors', 'Organization', 'PYTH',
> > now() )
> > ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
> > TABLE LOCK table `voxinternal/Entity` trx id 0 308691 lock mode IX 
> > waiting
> > ------------------
> >  
> > The MySQL version is 5.1.7-beta-max-log
> >  
> > Can any one illuminate me?
> > I have read all documentation that i can find that appears 
> pertinent.
> > Does anyone know how I may find out who already has a lock 
> in/on the 
> > table?
> > Can one find out what locks are going to be issued when some 
> > particular piece of SQL is executed?
> 
> I don't know any way to find out what locks are going to be 
> acquired, other than
>   if you are deleting or updating specific records, you can 
> generally be sure they will be locked.  In this case you have 
> a TABLE LOCK, not a record lock. 
> I'm not sure what level that happens at (MySQL server, or 
> InnoDB storage engine).
> 
> If it's an InnoDB lock, you can use the InnoDB Lock Monitor 
> as described in the manual (short version: issue CREATE TABLE 
> innodb_lock_monitor(a int) ENGINE=InnoDB).  It prints to your 
> mysql.log file.  It looks very similar to SHOW INNODB STATUS 
> output, except it prints all locks held, not just the locks 
> waited for.
> 
> I wrote a tool called innotop that can help with general monitoring
> (http://sourceforge.net/projects/innotop) and some insight 
> into locking.  But there's only so much data available to it 
> :-)  I plan to make it capable of reading the lock monitor 
> file I just mentioned, so you don't have to wade through it 
> by hand.  But that's future functionality.
> 
> You can also try SHOW OPEN TABLES and see if that table is 
> locked with a table lock.  That might give some insight.
> 
> My guess is there is a lock from LOCK TABLES on the table, 
> otherwise it would just be waiting to lock the gap between 
> records (so it can insert into the gap).
> 
> Visibility into locking is my favorite gripe with MySQL -- it 
> is very hard to find answers to these sorts of questions.
> 
> Baron
> 
Thread
InnoDB table lock on INSERTPaul C. Power30 Apr
  • Re: InnoDB table lock on INSERTBaron Schwartz30 Apr
    • RE: InnoDB table lock on INSERTPaul C. Power1 May