On Wed, Apr 27, 2016 at 12:37 AM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > On 04/26/2016 01:22 AM, Amit Saha wrote: >> >> On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer <mike...@zzzcomputing.com> >> wrote: >>> >>> >>> >>> On 04/25/2016 08:30 PM, Amit Saha wrote: >>>> >>>> >>>> Hi all, >>>> >>>> In my scenario, DB migrations (via alembic) will be run as part of the >>>> app deployment and multiple app deployments will happen near >>>> simultaneously (multiple EC2 instances talking to the same MySQL DB). >>>> Let's, for simplicity's sake assume two instances: >>>> >>>> Instance #1: Alembic sees alembic_version table and finds that we do >>>> need the migrations to happen, and starts the migration. >>>> >>>> Instance #2: Alembic sees alembic_version table and finds that we do >>>> need the migrations to happen, and starts the migration. This doesn't >>>> obviously happen since MySQL will lock the table during the migration. >>> >>> >>> >>> this scenario is entirely unsupported. MySQL does not support >>> transactional DDL and migration #1 will be hitting some tables and >>> migration >>> #2 another. The version table is not updated until after a particular >>> migration takes place so concurrent processes will both be doing the same >>> migration at the same time. >>> >>>> >>>> Now, let's say instance #1 finishes the migration first and updates the >>>> alembic_version table. Then, the migration triggered by instance #2 >>>> starts to run, and errors out because the schema changes are already in >>>> place. >>>> >>>> Is this what will happen or does alembic get a read/write lock on >>>> alembic_version table for the entire migration? >>> >>> >>> >>> there are no "read/write locks" on the alembic_version table. What >>> you're >>> doing will not work at all. Only one process should be running >>> migrations >>> against a single target database at a time. >> >> >> Thanks for the reply, Mike. I am wondering what are my options - >> should I just have to ensure that I don't run migrations in more than >> one process? Which would mean, I cannot really use alembic for my DB >> migrations (I wonder how people would usually do it). > > > How this is usually done is that the decision to "migrate" is initiated > manually. I'm guessing this is a web application that wants to just > automigrate when it starts. Usually what apps like that do is check that > the DB is not up to date, and present a web form with a button to "upgrade" > - so the user pushes the button only once. Or, if this is some kind of > service that spawns multiple processes, the lead process does the check > against the table and does the upgrade. > > The thing about schema upgrades is that they require code changes to have > any upgrades to apply. So therefore, you're starting *something* to first > run that new code; that's where the migration step should happen, before > everything else starts up.
Thanks again Mike. So, I think the manual separate step to change the schema has to be in there. For my curiosity's sake (and please bear with my noobishness), what if alembic had a lock (provided the RDMS allows) for the entire time: 1. Get RW lock on alembic_version 2. Check if we need a migration 3. Yes, next step, no- step 5 4. Run migrations 5. Update alembic_version 5. Release lock on alembic_version That would at least prevent multiple migrations stepping on each other's feet? Whichever gets to alembic_version first performs the entire migration and when the other process gets to alembic_version, it finds no migrations are needed. > > > > > >> > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy-alembic" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy-alembic/I2AAEUdF2dQ/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy-alembic+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- http://echorand.me -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
