title: Several app instances trying to migrate the same database at the same time
body: <p>I just started using flyway in a pretty standard spring 2.5 web application with a mysql databse. It has been in production for years and has a lot of data.</p>
  
  <p>I hooked it up to auto-migrate on startup with</p>
  
  <pre><code>&lt;bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate"&gt;
      &lt;property name="dataSource" ref="dataSource"/&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>and the made the Spring's persistence unit manager (using jpa) depend on it so the database would be migrated before JPA initializes.</p>
  
  <p>This works fine.</p>
  
  <p>In our production environment we have several nodes which we like to update concurrently.
  Thus each instance will try to execute the same migrations if they are long-running.
  Flyway's locking functionality should prevent that. </p>
  
  <p>I like to test these things before doing anything in production so I did; and what happened when two app instances were starting at the same time with a long running migration (6 minutes) is that after a while the second instance failed with:</p>
  
  <pre><code> Caused by: com.googlecode.flyway.core.exception.FlywayException: Unable to lock metadata table 'schema_version' in schema 'dbschema'
      at com.googlecode.flyway.core.metadatatable.MetaDataTable.lock(MetaDataTable.java:148)
      at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:116)
      at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:114)
      at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:54)
      at com.googlecode.flyway.core.migration.DbMigrator.migrate(DbMigrator.java:113)
      ...
  </code></pre>
  
  <p>Deeper down in the stack trace is the SQLException:</p>
  
  <pre><code>Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
      ...
  </code></pre>
  
  <p>Essentially it seems to timeout. </p>
  
  <p>We're using mysql and afaik it's trying to lock the metadata table with</p>
  
  <pre><code>select * from dbschema.schema_version for update
  </code></pre>
  
  <p>I checked the db it was trying to migrate and its lock wait timeout:</p>
  
  <pre><code>mysql&gt; show variables like 'innodb_lock_wait_timeout';
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | innodb_lock_wait_timeout | 120   |
  +--------------------------+-------+
  </code></pre>
  
  <p>Which is the obvious culprit. 
  However, I'm not sure I want to increase the amount since I might want it to timeout if such an occurrence were to happen for some other reason.</p>
  
  <p>I might try to set it to a higher value for the duration of the migration.
  Except that it seems to be a read only variable:</p>
  
  <pre><code>SET innodb_lock_wait_timeout = 240;
  ERROR 1238 (HY000): Variable 'innodb_lock_wait_timeout' is a read only variable
  </code></pre>
  
  <p>I could of course do deployment semi-manually if I know there are going to be long running migrations, by e.g. just updating one instance and wait til it passes migration and then update the other instances.</p>
  
  <p>Does anyone have any other suggestions?</p>