title: MySQL: Truncate Table within Transaction?
body: <p>I have an InnoDB table that needs to be re-populated every ten minutes within anywhere from 60k to 200k records. Our approach up to this point has been as follows:</p>
  
  <ol>
  <li>Turn off Autocommit</li>
  <li>Truncate the table</li>
  <li>Perform Select Queries &amp; additional Calculations (using PHP)</li>
  <li>Insert new records</li>
  <li>Commit</li>
  </ol>
  
  <p>After the Truncate operation is performed though, the data is immediately deleted, and is no longer available from the User Interface. To our users, this has been pretty disconcerting, even though within about 30 seconds or so the script encounters the Commit operation and the table is repopulated.</p>
  
  <p>I thought that perhaps I could wrap the whole operation, <em>including</em> the <code>Truncate</code>, in a transaction, and that this might cut down on the length of time during which the table appears empty to users. So I changed <code>SET AUTOCOMMIT=0</code> to <code>START TRANSCATION</code>.</p>
  
  <p><strong>Yikes!</strong> This had the opposite of the desired effect! Now the <code>TRUNCATE</code> operation still occurs at the beginning of the script, but it takes <strong>much longer</strong> to actually execute the <code>INSERT</code> operations within the transaction, so that by the time the <code>COMMIT</code> operation takes place and the data in the table is available again, it has been nearly <strong><em>ten minutes!</em></strong></p>
  
  <p>What could possibly cause this? Truthfully, I wasn't expecting any change at all, because I was under the impression that initiating a transaction basically just turns off <code>Autocommit</code> anyway??</p>