Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter.
Interesting enough it looks like most people do not think about foreign keys overhead in terms of locking. The overhead about checking referenced table is usually considered but not locking which also might be important.
So lets talk how locks seems to work with foreign keys in Innodb. I’ve only done a quick check so could be missing some details.
All Innodb Foreign Key related operations happen on data modification. So for example if you do SELECT FOR UPDATE on CHILD table it will not lock associated rows in PARENT table and so you can run into lock waits if you do updates to CHILD table which change parents because this is when row in PARENT table will be locked to perform update.
The checks are performed “originating” from the table which you modify - if you update row in CHILD table and parent information is changed lookup will be performed in PARENT table and row will be locked. Same applies to PARENT table - if you will try to delete row in PARENT table lockup in CHILD table will be performed with row lock performed.
Innodb is smart enough to detect which updates are affecting foreign key relationships. So if you will update any column which is not part of FOREIGN KEY constraint - no foreign key originated locks will happen. However if you update PRIMARY KEY value in the child table the lock will happen as it is internally implemented as special form of DELETE+INSERT. But this is something you should not be doing anyway.
So how can you suffer from locks originating from Foreign Keys ?
Imagine you have users table and messages table. If you would use bulk inserts (for performance) to messages table you will have significant number of user ids locked in the user table which can stall queries working with users table or other tables which have foreign keys to this table. The statements which even work with different tables and traditionally would not affect each other with locks now can because of foreign keys.
So implementing foreign keys do not forget to think about locking overhead among other things.


Innodb locking and Foreign Keys - Read More...