Currently, the migration lock is acquired before any transactions are started; this means it's impossible to use a database locking mechanism which is transaction-bound, but that's the natural way to manage locking in relational databases (e.g. SELECT FOR UPDATE, or the PostgreSQL LOCK TABLE mechanism).
This is also connected to the question of whether we wrap all applied migrations in a single migration (barring transaction-incompatible migrations), or each migration in its own transaction. It seems like we currently wrap each migration in a separate migration, though we ideally want everything to be wrapped in a single transaction instead (if we do continue to do transaction-per-migration, the lock would need to be re-acquired for each migration).
We also need to also make sure we don't read the state of the migration history table before acquiring the lock. Otherwise, in a race condition, by the time we acquire the lock, the history table may have changed (as someone else has applied migrations), and we'd attempt to re-apply migration that have already been applied.
Finally, the PG locking mechanism would be to lock the migration history table, which means it must exist first; this is a problem on the very first migration. We can either handle this in the provider, i.e. handle "table does not exist" errors when trying to lock the table, and creating the history table from within GetDatabaseLock; or we can switch the order around and have EF create the history table before acquiring the lock.
Basically, it seems like the ideal flow here is to start a transaction, create the history table if needed, acquire the lock, and then read the history table and apply everything, comitting only at the very end and then releasing the lock.
Currently, the migration lock is acquired before any transactions are started; this means it's impossible to use a database locking mechanism which is transaction-bound, but that's the natural way to manage locking in relational databases (e.g. SELECT FOR UPDATE, or the PostgreSQL LOCK TABLE mechanism).
This is also connected to the question of whether we wrap all applied migrations in a single migration (barring transaction-incompatible migrations), or each migration in its own transaction. It seems like we currently wrap each migration in a separate migration, though we ideally want everything to be wrapped in a single transaction instead (if we do continue to do transaction-per-migration, the lock would need to be re-acquired for each migration).
We also need to also make sure we don't read the state of the migration history table before acquiring the lock. Otherwise, in a race condition, by the time we acquire the lock, the history table may have changed (as someone else has applied migrations), and we'd attempt to re-apply migration that have already been applied.
Finally, the PG locking mechanism would be to lock the migration history table, which means it must exist first; this is a problem on the very first migration. We can either handle this in the provider, i.e. handle "table does not exist" errors when trying to lock the table, and creating the history table from within GetDatabaseLock; or we can switch the order around and have EF create the history table before acquiring the lock.
Basically, it seems like the ideal flow here is to start a transaction, create the history table if needed, acquire the lock, and then read the history table and apply everything, comitting only at the very end and then releasing the lock.