Advisory locking for making changes

Proposed new feature or change

It is typical to deploy PHP apps into k8s or Swarm clusters and run migrations on container start. In case of scaling, there will be multiple containers started at the same time so we need to ensure that migrations are applied once. Could be something like:

if (!$this->acquireLock()) {
    $output->writeln(sprintf('%s is already running.', $this->getName()));
    return 0;
}

try {
    // do the job
} finally {
    if (!$this->releaseLock()) {
        throw new RuntimeException('Failed to release lock.');
    }
}

Implementation for PostgreSQL could be the following (pseudo-code with real SQL):

private function acquireLock(): bool
{
    return executeSQL(
        'SELECT pg_try_advisory_lock(hashtext(?));',
        ['migrations']
    )->boolean();
}

private function releaseLock(): bool
{
    return executeSQL(
        'SELECT pg_advisory_unlock(hashtext(?));',
        ['migrations']
    )->fetchOne();
}

That is simple for MySQL as well:

SELECT GET_LOCK('migrations', 0);
SELECT RELEASE_LOCK('migrations');

For the rest of databases, that's a bit complicated so it might be a better idea to use https://github.com/yiisoft/mutex but this way we need to hint that using same database in this case is preferred.

Start with the migration execution flow and the proposed acquireLock/releaseLock pattern. Compare PostgreSQL advisory-lock SQL and MySQL GET_LOCK/RELEASE_LOCK behavior, then assess whether yiisoft/mutex is needed for other databases. Done means concurrent migration runs are serialized and locks are released safely across the supported database options.