Unlocking Migrations in Rails + MySQL
NB: This was written as a personal reminder but I thought it would be helpful to a for anyone else looking for an answer for a similar problem.
- MySQL v5.6+
- Rails 5.1+
- The existing table has a few million records.
Adding a column on an existing large table with an index on the newly added column without a default value without locking that table. This is oddly specific but that’s what I needed. Out of the box a typical Rails migration would look like this:
class AddOddlySpecificColAndIndex < ActiveRecord::Migration[5.2] def up add_column :table_name, :column_name, :data_type, index: true end def down remove_column :table_name, :column_name, :data_type end end
In the default behavior, this migration will trigger a table lock that blocks writes until the index has been built. Depending on large the table is, that could be a significant length of time.
ActiveRecord doesn’t support this out of the box but we can easily pass an option to the SQL statement that allows us to leverage a MySQL feature in order to resolve this very issue.
class AddOddlySpecificColAndIndex < ActiveRecord::Migration[5.2] def up execute <<~SQL ALTER TABLE table_name ADD COLUMN column_name data_type, ADD INDEX index_table_name_on_column_name (column_name), ALGORITHM=INPLACE, LOCK=NONE; SQL end def down remove_column :table_name, :column_name, :data_type end end
This statement is essentially declaring the same thing as we had previous but with the added
From the docs:
To avoid accidentally making the table unavailable for reads, writes, or both, specify a clause on the ALTER TABLE statement such as
LOCK=NONE(permit reads and writes) or
LOCK=SHARED(permit reads). The operation halts immediately if the requested level of concurrency is not available.
ALGORITHM=INPLACE insures that we’re not running the operation that would copy the table during this altar which often will take significantly more resources.
With the a bit of added effort, we’ve accomplished adding a new column with an index without locking the table. There are a few caveats and with any important task, so be sure to do your own research/testing and check the versions of your dependencies.
If you have an even larger table, I would also look into solutions like the Large Hadron Migrator which aims to also do the same thing but with a few more safeguards in place.