title: MySql stored procedures, transactions and rollbacks
body: <p>I can't find an optimal way to use transactions in a MySql Stored Procedure. I want to <code>ROLLBACK</code> if anything fails:</p>
  
  <pre><code>BEGIN
  
      SET autocommit=0;
      START TRANSACTION;
  
      DELETE FROM customers;
      INSERT INTO customers VALUES(100);
      INSERT INTO customers VALUES('wrong type');
  
      COMMIT;
  END
  </code></pre>
  
  <p>1) Is <code>autocommit=0</code> required?</p>
  
  <p>2) If the second <code>INSERT</code> breaks (and it does of course) the first <code>INSERT</code> is not rolled back. The procedure simply continues down to the <code>COMMIT</code>. How can I prevent this?</p>
  
  <p>3) I've found I can <code>DECLARE HANDLER</code>, should I use this instruction or is there a simpler way to say that if any command fails, the stored procedure should <code>ROLLBACK</code> and fail too?</p>
  
  <p><code>DECLARE HANDLER</code> works fine, but since I have MySql version 5.1 I can't use <code>RESIGNAL</code>. So if an error occurs, the caller won't be notified:</p>
  
  <pre><code>DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN
      ROLLBACK; 
      -- RESIGNAL; not in my version :(
  END;
  
  START TRANSACTION;
  </code></pre>