In addition to the AUTO-INC table lock, there seem to be some other conditions where table lock is requested. I am saying this because on some of my InnoDB tables, I am seeing "tables in use 1, locked 1."
Do you know of any other conditions that can cause InnoDB to request a table lock? I have heard that using REPLACE or UNIQUE indexes on MySQL 4.1.X can cause this behavior.
If you know of any other situation where InnoDB will request a table lock, would you please leave a comment or email me (softwareengineer99 at Yahoo).
As I find information regarding this, I plan to document it here.
MySQL Internals: Concurrent inserts causing table locks in InnoDB that go away when innodb_table_locks=0 is added to my.ini on Windows. The question raised, but not answered: "Do some queries implicitly add LOCK_TABLE?"
MySQL Internals: What is the intention lock check in InnoDB needed?
Heikki: "the intention lock is required: always when InnoDB sets an S-lock on a
record, there must be an IS-lock on the table."
- "InnoDB need read lock because binlog is enabled"
MySQL Bug 16229: InnoDB uses full explicit table locks in trigger processing: Fixed in 5.0.19.
Jeremy Cole encouraged Heikki and MySQL to change the way table and row locking is handled by MySQL and InnoDB, something that I totally support. I have heard Jeremy pitch this at nearly every ocassion but sadly I haven't seen any action. Jeremy says,
It seems like it would make more sense to reverse the locking procedures
in MySQL and their handling by the storage engines. As I understand it,
currently MySQL asks for a table lock in all cases and the storage
engine (InnoDB, in this case) may downgrade that to a row lock.
Wouldn't it make far more sense for MySQL to ask for a row lock, and the
storage engine (MyISAM, in this case) to upgrade that to a page lock,
table lock, etc. as support is available? Basically MySQL should ask
the storage engine for the most granular lock possible, and the storage
engine should upgrade that lock if it is unable to lock with that
MySQL Internals: Heikki pushed a change into 4.1.9 (just a comment). The changes were primarily made in ha_innodb.cc:
...no InnoDB table lock is now acquired in LOCK TABLES if
AUTOCOMMIT=1. This helps to avoid deadlocks when porting old MyISAM applications to
+ /* Starting from 4.1.9, no InnoDB table lock is taken in LOCK
+ TABLES if AUTOCOMMIT=1. It does not make much sense to acquire
+ an InnoDB table lock if it is released immediately at the end
+ of LOCK TABLES, and InnoDB's table locks in that case cause
+ VERY easily deadlocks. */