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.
> 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 220.127.116.11 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
> > 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.