Rework migration for migrating award emoji
There's a time frame where after migrating notes to the award emoji table new notes may be inserted that won't be migrated. Once the is_award
column is removed these notes will probably be displayed as regular notes.
To work around this we should rework these migrations to use the following steps:
- Lock the
notes
table forINSERT
andUPDATE
but allowSELECT
- Create the new award emoji table/indexes
- Migrate the existing notes
- Remove
notes.is_award
- Unlock the
notes
table
This still allows for errors to pop up whenever old code tries to create a note, but at least no bogus data ends up in the notes
table.
Locking
Both PostgreSQL and MySQL use different ways of locking tables. PostgreSQL does locking on transaction level, meaning locks are released whenever the transaction finishes. MySQL on the other hand uses global locking and as far as I believe doesn't even support locking in transactions. Both also use a different syntax for locking.
- PostgreSQL docs: https://www.postgresql.org/docs/current/static/explicit-locking.html
- MySQL docs: http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
Note that we can not use SELECT ... FOR UPDATE
and the likes as we only want to release the lock once notes.is_award
has been removed.
Because of the difference between PostgreSQL and MySQL probably the best way is to disable transactions for the migration, then have a method (called by up
) for migrating PostgreSQL and one for MySQL. Both of these will re-use the same query (e.g. a separate method) but each method will take care of acquiring the locks, starting any transactions, etc. Something like:
disable_ddl_transaction!
def up
if Gitlab::Database.postgresql?
migrate_postgresql
else
migrate_mysql
end
end
def migrate_postgresql
ActiveRecord::Base.connection.transaction do
acquire lock
migrate
release lock (if needed at all)
end
end
def migrate_mysql
acquire lock
migrate
ensure
release lock
end