edx/edx-ora2 — pull request #475 by wedaly (2014-07-06 14:51:33+00:00)
Title: Update migration to add criterion_id to AssessmentPart

Add locks and a default value for the migration that adds criterion_id to assessmentpart

The full migration takes 26.478 seconds in a test database (RDS m3.medium) with 250,382 assessment part records (about 100,000 more than in prod). These are the queries it runs:
```
(ora2)Wills-MacBook-Pro-2:edx-ora2 will$ time ./manage.py migrate assessment 0018 -v 3
- Soft matched migration 0018 to 0018_auto__add_field_assessmentpart_criterion.
Running migrations for assessment:
- Migrating forwards to 0018_auto__add_field_assessmentpart_criterion.
> assessment:0018_auto__add_field_assessmentpart_criterion
= SET FOREIGN_KEY_CHECKS=0; []
= SET FOREIGN_KEY_CHECKS=0; []
= LOCK TABLE assessment_assessmentpart WRITE []
= ALTER TABLE `assessment_assessmentpart` ADD COLUMN `criterion_id` integer NULL; []
= ALTER TABLE `assessment_assessmentpart` ADD CONSTRAINT `criterion_id_refs_id_507d3ff2eeb3dc44` FOREIGN KEY (`criterion_id`) REFERENCES `assessment_criterion` (`id`); []
= CREATE INDEX `assessment_assessmentpart_a36470e4` ON `assessment_assessmentpart` (`criterion_id`); []
= UNLOCK TABLES []
= SET FOREIGN_KEY_CHECKS=0; []
= LOCK TABLE assessment_assessmentpart WRITE []
= ALTER TABLE `assessment_assessmentpart` ADD COLUMN `criterion_id` integer NULL; []
= ALTER TABLE `assessment_assessmentpart` ADD CONSTRAINT `criterion_id_refs_id_507d3ff2eeb3dc44` FOREIGN KEY (`criterion_id`) REFERENCES `assessment_criterion` (`id`); []
= CREATE INDEX `assessment_assessmentpart_a36470e4` ON `assessment_assessmentpart` (`criterion_id`); []
= UNLOCK TABLES []
= SET FOREIGN_KEY_CHECKS=0; []
```

I've verified that this prevents the deadlock I was able to reproduce in my test database. By acquiring the table lock, we ensure that the application isn't running queries while we're running the migration, which eliminates deadlocks. The migration basically locks the table anyway when it adds the column (all currently running transactions are blocked until that operation is finished).

This is one possible solution, but I definitely want feedback from ops and a full-scale test before moving forward.

@stephensanchez @feanil