MySQL create index with LOCK=NONE still locks a table I have the following MySQL RoR Migrations:

class ReindexRpushNotification &lt; ActiveRecord::Migration
  def up
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed, processing, deliver_after), ALGORITHM=INPLACE, LOCK=NONE;")
  end

  def down
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed), ALGORITHM=INPLACE, LOCK=NONE;")
  end
end


during this migration I'm trying to perform some requests (GET, COUNT, DELETE, UPDATE) but nothing work, all these requests just wait

I found an info about index creation in background here

https://stackoverflow.com/a/36064200

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

but it doesn't work for us

Did anybody try LOCK=NONE?

We use MySQL 5.6.23 on AWS RDS
Looks like I have a clue why it doesn't work

Our table have the following constraint:

CONSTRAINT `rpush_notifications_event_id_fk` 
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE


Next, I found here one interesting thing:
https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql


  Online operation (LOCK=NONE) is not allowed in the following cases:
  
  
  when adding an AUTO_INCREMENT column,
  when the table contains FULLTEXT indexes or a hidden FTS_DOC_ID column, or
  when there are FOREIGN KEY constraints referring to the table, with ON…CASCADE or ON…SET NULL option.
  


so looks like we have a 3rd case