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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
