From: Rick James
Date: April 6 2012 9:14pm
Subject: Re: Innodb Table Gets Locked while Drop
List-Archive: http://lists.mysql.com/mysql/227141
Message-Id: <4F7F5CA8.8040400@yahoo-inc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

There is almost no reason to use LOCK TABLES on InnoDB tables.

I prefer to have auto_commit=1, then use explicit BEGIN and COMMIT for 
clumps of statements that need ACID.  I never use auto_commit=0.

Following those two rules, your issue with innodb_table_locks being on 
goes away.

On 4/5/12 11:58 PM, Adarsh Sharma wrote:
> Dear All,
>
> Today i faced an issue while issuing commands on my innodb tables.
> When I issued any drop table or alter table command my query gets into
> waiting state for more than 15-20 minutes.
>
> Mysql Version :-5.5.4 community
>
> I researched on the issue and find that every command try to get lock on
> the table before executing query on it.
> After some R n D , i find and innodb_table_lock parameter in my.cnf.
>
> After turning it off, my commands are executed with no issue.
> What are the pros&  cons of this modification&  why it tries to lock
> tables, can anyons have some idea about the issue.
>
>
> Thanks
>

-- 
Rick James - MySQL Geek