Yes, you can implement that inside your env.py if you wanted. All the other processes would wait and once inside the lock they all need to re-check the version. Not something alembic would include, though.
On Tuesday, April 26, 2016, Amit Saha <amitsaha...@gmail.com> wrote: > On Wed, Apr 27, 2016 at 12:37 AM, Mike Bayer <mike...@zzzcomputing.com > <javascript:;>> 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 > <javascript:;>> > >> 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 <javascript:;>. > > 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 <javascript:;>. > For more options, visit https://groups.google.com/d/optout. > -- 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.
