Monday, June 18, 2007

InnoDB table locks

When I first learned that having an AUTO-INC column causes a table lock in InnoDB, I was surprised. Since that day, I have been warning just about everyone regarding this.

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 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. */


jaypipes said...


Interesting question, so I thought I'd have a gander at the InnoDB code in /storage/innobase/lock/lock0lock.c.

Heikki says, in the header comments:

"Auto-increment (AI) locks are needed because of statement-level MySQL binlog."

Interesting. Perhaps you can do some tests to see if the row-based replication in MySQL 5.1 leads to increased performance for A_I locking in InnoDB?



Anonymous said...

See bug #16979 for the latest action on improving auto-increment locking in MySQL 5.0 and later.

You may also find it useful to experiment with locks_unsafe_for_binlog in slaves where you don't care about binary log reliability. It's unsafe for replication masters and cases where you may need to do disaster recovery with the binary log. It frees InnoDB from assorted serialisability requirements that need locks.

James Day

John said...

I did not realize until recently about the initial tablelock request myself. It caught me by surprise.

I'm used to databases (DB2 for example) having lock ESCALATIONs not lock deescalations (is that even a word?)

One thing I liked about the DB2 world was how clear the lock escalation path was:

(this is from memory)
- row level (imcludes previous and subsequent row of updated record)
- page level
- table lock if certain percentage of total rows being modified is reached.

db2 said...

Nice info