Advisory locking for making changes

[samdark]

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.

[rustamwin]

Related to #270