On Wednesday, April 27, 2016, Amit Saha <amitsaha...@gmail.com> wrote:
> On Wed, Apr 27, 2016 at 11:31 PM, Mike Bayer <clas...@zzzcomputing.com > <javascript:;>> wrote: > > 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. > > Have you thought about doing it in alembic and not done it because > that may not be the best idea? Just trying to understand. It's too complicated, error prone, and hard to maintain and support across any number of database backends. Also it's not what most people should be doing though it may be appropriate for your case. > > > > > > > > > > On Tuesday, April 26, 2016, Amit Saha <amitsaha...@gmail.com > <javascript:;>> 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 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.
