Why would innoDB lock tables

Question

I'm using innoDB on AWS. I keep getting a ton of the following messages in my logs:

Waiting for table level lock

I thought innoDB only did row-level locks. I don't have any lock tables in my code. Does anyone have any idea why this would happen?

Answer 1

There are certain reasons when InnoDB table locks comes into picture:

1) InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above InnoDB knows about row-level locks.

You should set innodb_table_locks=0 and restart MySQL server.

2) While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. While accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Other clients cannot insert into the table while the AUTO-INC table lock is held.

You can refer this blog post as well: http://www.mysqlperformanceblog.com/2012/07/31/innodb-table-locks/