title: How can I lock an InnoDB table to prevent updates while that table is being copied?
body: <p>I would like to temporarily lock a table to prevent other concurrent processes from making changes to it.  The reason for this is that this table is going to be copied to a temp table, altered, and then copied back (well original is actually dropped and new table is renamed).  Then after all this is complete, I want to unlock the table and hopefully have anything that was attempted during the lock resume.</p>
  
  <p>I also need to be able to read from the table that has been locked to build the new table.</p>
  
  <p>Here is what I tried, but it doesn't appear to work.</p>
  
  <pre><code>$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT);
  
  $mysqli-&gt;autocommit(false)
  
  // create a table to hold parts to keep
  $q1 = "CREATE TABLE new_table LIKE my_table;";
  $res1 = $mysqli -&gt;query($q1);       
  
  // Lock table to avoid concurrent update issues
  $q2 = "LOCK TABLE my_table WRITE;";
  $res2 = $mysqli -&gt;query($q2);           
  
  // Insert data to keep into new table   
  $q3 = "INSERT INTO new_table SELECT * FROM my_table WHERE some_id IN (SELECT ID FROM table2)";
  $res3 = $mysqli -&gt;query($q3);
  
  // drop original table
  $q4 = "DROP TABLE my_table;";
  $res4 = $mysqli -&gt;query($q4);
  
  // rename new table
  $q5 = "RENAME TABLE new_table TO my_table;";
  $res5 = $mysqli -&gt;query($q5);
  
  $mysqli -&gt;commit(); // commit changes and re-enable autocommit
  
  $q = "UNLOCK TABLES;";
  $res = $mysqli -&gt;query($q); 
  </code></pre>
  
  <p>To test, using PHPmyadmin, I've issued the "SET AUTOCOMMIT=0; LOCK TABLE my_table WRITE;" query and then tried to delete something from my_table and I was able to do so.  I want to block this.  Also, after issuing the lock statement, the rest of the procedure fails and nothing is altered.</p>