Alembic: Is multiple alembic upgrades ok to run concurrent?

[stelund]

Looking at alembic upgrade head --sql shows updating version to be:

UPDATE alembic_version SET version_num='e1975dedd534' WHERE alembic_version.version_num = '35c5d5057736';

This is not blocking two transactions to upgrade at the same time. Is there conditional statements in the transaction performing the upgrade that blocks concurrent upgrades to the same version?

[zzzeek]

hi there -

there is no built-in isolation configuration that would guarantee to "block" transactions unless you configure that yourself in your env.py using isolation level, noting that different database backends will interpret various isolation levels differently or not at all. the SERIALIZABLE isolation level would be the one most likely to block a write from occurring on the table concurrently against another transaction that has also written a row to the table, while most Alembic migrations send just an UPDATE, if you are dealing with branchpoints and merges, these will correspond to INSERT and DELETE statements also.

within the realm of UPDATE, if you are on at least a working REPEATABLE READ, and two transactions attempt to SELECT and then UPDATE against this table, the second one should detect that the update failed to match no rows and will raise an error, that is, the UPDATE is performed using a standard compare and swap technique.

overall, it's not that simple to "block" things in a database generically because this requires something like "LOCK TABLE" that is not universally available. however, if you are using a backend where this is available, I would recommend modifying your env.py so that before it uses the DB connection, you LOCK and then when the operations are done you can release the lock. example:

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context._ensure_version_table()
            connection.execute(
                "LOCK TABLE alembic_version IN ACCESS EXCLUSIVE MODE"
            )
            context.run_migrations()
        # lock is released when transaction ends

[stelund]

Ok, thanks.

Another approach would be to use one row for each applied migrations. Then before running the migration insert into the table and use an unique constraint to ensure it's only run once. Downgrading would could deleted rows to be 1 before applying the migration.

[stelund]

For future reference - it's context.get_context()._ensure_version_table()

[zzzeek]

IIUC what that means, while the "alembic_version" table is very much about a view of the current heads, the "use one row for each migration" thing seems a little bit like a history table which was proposed in #309 , but getting it to also act as a unique constraint while also keeping history might be a little awkward.

We can add the "ensure version table" thing as public API and then do a recipe based on the "lock table" thing if you've observed that general idea is working for you.

[stelund]

Good idea!

I think the idea will work for me. My current implementation will run migrations every time an docker container starts. Using an table lock feels safe for me.

[jesusfcr]

Locking the table doesn't work for me, the case where the table doesn't exists can make the blocked instance to be waiting just to create the table (that already created the first one).

   try:
        with context.begin_transaction():
        connection.execute("SELECT pg_advisory_xact_lock(10000);")
            context.run_migrations()
    finally:     

This solution works doesn't depend on the existence on the table, neither on the name of the table, and it's simpler.