Running Migrations Simulataneously

[Amit Saha]

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.

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? (And hence instance #2 never will need to run the migration since when it reads alembic_version, the DB already has the migrated schema).

Thanks for your insights.

Best Wishes,

Amit.

[Mike Bayer]

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.

(And hence instance #2
> never will need to run the migration since when it reads
> alembic_version, the DB already has the migrated schema).
>
> Thanks for your insights.
>
> Best Wishes,
> Amit.
>
>
>
>
>

> --
> 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-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Amit Saha]

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).

[Mike Bayer]

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.




>

[Amit Saha]

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-alem...@googlegroups.com.

> For more options, visit https://groups.google.com/d/optout.

--
http://echorand.me

[Mike Bayer]

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. 

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-alem...@googlegroups.com.

[Amit Saha]

On Wed, Apr 27, 2016 at 11:31 PM, Mike Bayer <cla...@zzzcomputing.com> 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.

[Mike Bayer]

On Wednesday, April 27, 2016, Amit Saha <amits...@gmail.com> wrote:

On Wed, Apr 27, 2016 at 11:31 PM, Mike Bayer <cla...@zzzcomputing.com> 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.