Non-blocking ALTER TABLE for mySQL

non_blocking_alter_table.sql

ALTER TABLE `my_awesome_database`.`user_transaction` 
  ADD FOREIGN KEY (`userId`) REFERENCES `my_awesome_database`.`user` (`id`), 
  ALGORITHM=INPLACE, LOCK=NONE;

Problem:

...ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

Solution:
In order to avoid blocking as much as possible, innoDB tables could be altered with 'inplace' algorithm and lock=none flag

In case you get ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF

Run following query before altering your table (it will disable foreign checks only for your current db-connection)

SET SESSION foreign_key_checks=OFF;