Improve migration that adds a foreign key to `ci_builds.stage_id`
We have created a migration that adds a foreign key and index to
stage_id column in
ci_builds table. This migration fails on gitlab.com due to a huge number of entries in
ci_builds table (~ 17 milion rows).
The migration failed on a deadlock. See discussion on Slack (not public).
- FK -> locks ci_builds
- pipeline create -> locks ci_stages
- FK -> attempts to lock ci_stages, blocks waiting on pipeline create
- pipeline create -> attempts to lock ci_builds, blocks waiting on FK
It appears that adding a foreign key using
add_concurrent_foreign_key helper can cause a deadlock, since it locks both tables. The solution in this case might be to acquire an exclusive lock on
ci_builds table gets locked.
More details in Slack.
Another concern is - how to prevent problems like this in a future, but we should discuss it in a separate issue.