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