One item I've been considering; under Downtime, a reason for flakes in migrations is "long running transactions".
I've seen this too, and wonder if the correct fix is actually to forbid long-running transactions. Typically if the naive long-running transaction does something like:
with transaction.atomic():
for user in User.objects.all():
user.do_expensive_thing_to_related_objects()
for user in User.objects.all():
with transaction.atomic():
user = User.objects.get(id=user.id) # Read the row to lock it; or do a SELECT FOR UPDATE
user.do_expensive_thing_to_related_objects()
I've considered simply configuring the DB connection with a very short connection timeout (something like 5 seconds) to prevent anyone from writing a query that performs badly enough to interfere with other tables' locks.
Anyone tried and failed/succeeded in making this approach work?
The other subject that's woefully underdeveloped is writing tests for migrations; ideally I want to (in staging) migrate the DB forwards, run all the e2es and smoke tests with the pre-migration application code, migrate back (to test the down-migration), run the e2es again, and then really migrate forwards again. That would cover the "subtly broken deleted field" migration problem.
But how do we test that our migrations behave correctly in the face of long-running transactions? I.e. what's the failing test case for that bug?