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]
