Running phinx or artisan migrate during a deployment is great, but how can you ensure they don't clobber each other in a multi-machine deployment?

I wish some migration tool had concurrency handling built in, but they don't. I think rails does, but in the PHP world it usually goes ignored. As with the concurrent session problem, concurrent DB migrations can mess up your data.

You should be able to simply lock the migrations table so that other sessions wanting to read from it must wait until the lock / transaction is freed / committed. The problem with MySQL is that you cannot have nested transactions, so the first open connection that obtains a read lock with select ... for update or select ... lock in share mode will explicitly release that hold on the migrations table if any migration script run in it contains a commit or rollback. This is further complicated with artisan's migrations as each migration's has a $withinTransaction = true property.

A separate session / connection could be obtained specifically for supervisory control of the migrations table.

Another solution would be named locks - or application locks or advisory locks as they might be called. Mysql, Pgsql, Oracle, and Mssql all provide some kind of lock obtaining feature. (sqlite3 shouldn't have any concurrency problems, right?)

Unfortunately, most SQL abstraction doesn't expose all these different ways of obtaining locks. Does doctrine do this? We can't simply write something like \DB::obtainLock('artisan-migrate').

There are a number of ways to get named locks your migration tool.

  1. Your migration tool could hard code lock syntax for a few popular DB engines and perform the locks during a migration.
  2. Your DB layer could expose functionality for obtaining locks. But this still requires integration into the migration tool itself. (you can't do this inside your migration code)
  3. Write your own wrapper around your migration tool, obtain the lock with your DB syntax, call your migration tool, and release the lock (or not).

Named locks are not as nice a solution as table or row locks, but I think the last solution is the easiest until the tool ecosystem embed solutions internally.

An artisan migrate solution

Here's a quick way to do this in Laravel with artisan migrate. You can just add this code into your routes/console.php if you Laravel version is high enough (5.6 I think). 


// add to routes/console.php
//signature same as artisan migrate
$signature = 'lockingmigrate {--database= : The database connection to use}
                {--force : Force the operation to run when in production}
                {--path=* : The path(s) to the migrations files to be executed}
                {--realpath : Indicate any provided migration file paths are pre-resolved absolute paths}
                {--pretend : Dump the SQL queries that would be run}
                {--seed : Indicates if the seed task should be re-run}
                {--step : Force the migrations to be run so they can be rolled back individually}';

Artisan::command($signature, function ($database=false, $seed=false, $step=false, $pretend=false, $force=false) {

		//here's the magic. (mysql specific)
		//this will block for 120 seconds before proceeding.
		// you can check the result and skip migration if you don't
		// obtain a lock (returns true, false or null)
		$results = \DB::select('SELECT GET_LOCK("artisan-migrate", 120) as migrate');
		if (!$results[0]->migrate) { return 0; }

		//you can fill out all the params you want
        $params = [
                '--pretend' => $pretend,
                '--force'   => $force,
                '--step'    => $step,
                '--seed'    => $seed,
        ];
        $retval = Artisan::call('migrate', $params);
        $outputLines = explode("\n", \Artisan::output());
        dump($outputLines);
        return $retval;
})->describe('Concurrent-safe migrate');

Test this solution with: 

php artisan lockingmigrate & php artisan lockingmigrate --pretend

You shouldn't get any errors. Try rolling back once and running it again. From time to time, the pretending migration will execute first and you will see the SQL printed to the console, but it won't run and trample the next migration.