title: Deadlock in mysql while adding foreign key
body: <p>There is a table named <code>company_competitors</code> in our database. there is a job which truncates and loads this table daily.
  The table has two columns <code>company_id</code> and <code>competitor_id</code> both references another table <code>companies</code>.</p>
  
  <pre><code>CREATE TABLE `company_competitors` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `company_id` int(11) DEFAULT NULL,
    `competitor_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_rails_company_id_c1ac450a` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
    CONSTRAINT `fk_rails_competitor_id_772a45c6` FOREIGN KEY (`competitor_id`) REFERENCES `companies` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=268477 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
  </code></pre>
  
  <p>The steps included in truncate and load are:</p>
  
  <ol>
  <li>create a new table <code>company_competitors_new</code></li>
  <li>add foreign key constraints on both the columns (<code>company_id</code>, <code>competitor_id</code>) one by one.</li>
  <li>load the data into the new table.</li>
  <li>swap the tables <code>company_competitors</code> and <code>company_competitors_new</code>.</li>
  </ol>
  
  <p>Queries:</p>
  
  <p><code>"CREATE TABLE company_competitors_new LIKE company_competitors;</code></p>
  
  <p><code>alter table company_competitors_new ADD CONSTRAINT fk_rails_company_id_53f8f57a foreign key (company_id) references companies(id);'</code></p>
  
  <p>The companies table might be accessed by some else while adding foreign key. So, When the second query is running the database gets stuck in a deadlock and no queries on companies table get executed.</p>
  
  <p><code>show full processlist</code> shows all queries saying <code>Waiting to acquire Metadata lock</code> and I have to kill all select queries so that adding of foreign key gets finished.</p>
  
  <p>I need help in understanding the reason for deadlock here and how can I deal with it. I would also like to hear if there is a better way to truncate and load with 0 downtime.</p>