Row locking in MySql InnoDb with foreign key constraints and indexes applied?

Question

Quick question.

Talking to my friend with 25 years db experience he was telling me if you use foreign key constraints in a db; when a table is being written to for example, a table for messages, it will lock out the relative row on the parent table for say, users.

Is this true?

Also he said that applying indexes to the Foreign Key Columns should overcome this locking out, is this true?

I am concerned as my website traffic is growing and I can imagine this being an issue!

Thanks!

Answer 1

I wrote a blog post on this - http://www.mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/ - the demo is just as your friend described. There is locking on parent rows!

Now for the part about explaining the difference between an "index" and a "key":

• A key is like some high level relational property. This matches that.
• An index is an implementation detail.

From the MySQL manual, "InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.". That is, if you don't add an index when you add your foreign key, InnoDB will automatically.

No amount of indexing prevents the parent locking described in my blog post.