title: renaming a locked table
body: <p>When migrating a table to a new schema I want to make sure to have an atomic switch to the new table using the copy and rename procedure. Hence I am trying to rename a locked table like this:</p>
  
  <pre><code>CREATE TABLE foo_new (...)
  
  -- copy data to new table, might take very long
  INSERT INTO foo_new (id,created_at,modified_at)
    SELECT * FROM foo WHERE id &lt;= 3;
  
  LOCK TABLES foo WRITE, foo_new WRITE;
  
  -- quickly copy the tiny rest over
  INSERT INTO foo_new (id,created_at,modified_at)
    SELECT * FROM foo WHERE id &gt; 3;
  
  -- now switch to the new table
  RENAME TABLE foo TO foo_old, foo_new TO foo;
  
  UNLOCK TABLES;
  </code></pre>
  
  <p>Unfortunately that results in <code>ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction</code>.</p>
  
  <p>How should this be done differently?</p>
  
  <p>This is with <code>mariadb:10.1</code>.</p>