title: Why does dropping a foreign key cause metadata locks on InnoDB?
body: <p>I have a high-traffic database (InnoDB, Mysql 5.7.11) with a table, <code>parent_table</code>, which a few tables reference with foreign keys:</p>
  
  <p><code>my_db</code>.<code>child1</code> (approx. 531 rows)
    <code>my_db</code>.<code>child2</code> (approx. 470 rows)
    <code>my_db</code>.<code>child3</code> (approx. 37807375 rows)
    <code>my_db</code>.<code>child4</code> (approx. 10 rows)</p>
  
  <p>This has been causing me a ton of migration headaches with metadata locks holding up traffic even on very simple table alterations like adding a column to <code>parent_table</code>. Finally, I decided I'd just get rid of the foreign keys and move that logic to application logic to get rid of the headaches. But, for some reason completely beyond me, even that seems to hang and cause queries behind it to hang with <code>Waiting for table metadata lock</code>. I tested dropping the foreign keys on an exact replica database and each drop took 200ms, so these aren't even heavy operations. They only hang when there's high traffic.</p>
  
  <p>I've tried both:</p>
  
  <p><code>ALTER TABLE child3 DROP foreign key some_ibfk</code> as well as adding in <code>ALGORITHM=INPLACE, LOCK=NONE;</code>. Nothing helps.</p>
  
  <p>So, 2 questions:</p>
  
  <ol>
  <li>What could possibly be causing the metadata locking? We're just dropping a foreign key. Why does that even need a lock?</li>
  <li>What could possibly be causing the operation to take so long on a higher traffic database?</li>
  </ol>