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_stages
, before 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.