On Wednesday, July 5, 2017 at 12:35:22 PM UTC-7, Mike Bayer wrote: > > On Wed, Jul 5, 2017 at 12:45 PM, kristian kvilekval > <kkvil...@gmail.com <javascript:>> wrote: > > I have some services that automatically try to run all outstanding > > migrations when started using alembic upgrade. > > > > I have noticed an issue when I start multiple services in parallel (a > > typical service deploy), that each one is actually trying to run the > > migration script. > > > > I would really like that one and only one service attempts the upgrade > while > > the others wait for the migration to complete or simply just go ahead. > > > > I would think the right place would be to store some state in the DB > that a > > migration is being attempted and that the parallel scripts would be able > to > > read this before doing their own migration. > > > > Is this right or is there a better way to handle this situation. If > this is > > right then does alembic itself store the state or is their a recipe for > what > > I am asking ? > > Well you need to lock on something. If all your processes are on one > server, use a lockfile. If not, perhaps use something like redis or > memcached to produce a distributed lock, if you have such a service > handy already (might be overkill if not). If you are using MySQL and > can handle running "LOCK TABLES", then perhaps add a "LOCK TABLES / > UNLOCK TABLES" (https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html) > block to your env.py within the transaction, using the alembic_version > table itself as the target that you're locking (assuming it exists, > which you'd have to check first). Postgresql offers LOCK > (https://www.postgresql.org/docs/9.4/static/sql-lock.html), which also > should work but you have to be more careful that the LOCK is scoped to > the transaction started/committed in env.py. > > As far as not using LOCK and instead just using a row of some kind, > you just have to make sure the row truly provides locking behavior; > some form of SELECT FOR SHARE or similar in conjunction with proper > transaction isolation would probably achieve this. > > I would prefer to keep this in alembic and not rely on outside resources that may or may not be available. It is multi hosted with no shared filesystem so I again, I had hoped to use the database to provide the locking behaviour. This does seems like a common case for scalable services.
Locking the migration table seems like the way to go. We are using postgres in production and sqlite for dev so I am wondering if there is a database agnostic way to specify the locks? Thanks, Kris -- 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.
