MySQL foreign key constraints cause ActiveRecord :: StatementInvalid (timeout to wait for a lock) in a Rails 3.1 application

Question

**Software Versions:** Rails 3.1.3 MySQL 5.5.21 OS: MacOS 10.7.3 

Today I added a lot of foreign key restrictions with a gem of a foreigner to my MySQL database. But now I get the “Block waiting expectations” exceptions on simple inserts:

 ActiveRecord::StatementInvalid (Mysql2::Error: Lock wait timeout exceeded; try restarting transaction 

If I delete foreign keys from my database, the problem has disappeared.

The problem occurs when I try to add an object with data for the association of "has_one" with "accepts_nested_attributes_for":

 class MyApp::PrimaryData < ActiveRecord::Base has_one :sub_data, :dependent => :destroy accepts_nested_attributes_for :sub_data [...] end class MyApp::SubData < ActiveRecord::Base belongs_to :primary_data attr_accessible :field1, :field2 [...] end 

---

 table: primary_data ------------------- - id (integer) - field1 (string) [...] table: sub_data --------------- - id (integer) - primary_data_id (integer) - field1 (string) - field2 (string) [...] -> foreign_key_contraint on primary_data_id --> table primary_datas (id) 

If I just create "PrimaryData" without "SubData" or "PrimaryData" and "SubData" separately, then I don't get MySQL errors, only when I try to create "PrimaryData" with some "SubData" "over the Rails" accepts_nested_attributes_for ".

Can anyone help me with this problem? Thanks in advance.

Answer 1

This is a dead end.

When you add a row to a table with a foreign key constraint, MySQL will receive a lock for both this table (or row in the case of InnoDB), and for the specified table (or row in the case of InnoDB).

In this particular case, when you create a PrimaryData with SubData, MySQL first obtains a lock on primary_data, then on sub_data, and then deadlocks upon acquiring another lock on primary_data.

In general, using foreign keys with rails is not a good idea. Use active validators for these purposes.