title: Transactions within MySql Stored Procedures
body: <p>I am trying to use a transaction within a MySQL Stored Procedure.</p>
  
  <p>Specifically, update a user table with amended data from a temporary record. from another table.
  then once transferred, delete the temporary record.</p>
  
  <p>I have created the code below, which when executed returns the string "transaction has succeeded".</p>
  
  <p>However, nothing is actually updated and the temporary record is Not deleted.</p>
  
  <p>Both SQL statements, when executed separately work Just fine,  the first one does the update, the second does the delete.</p>
  
  <p>Can anyone enlighten me as to what may be wrong?</p>
  
  <pre><code>BEGIN 
  
  -- set a default response
  DECLARE response varchar(48) DEFAULT "the transaction has failed.";
  
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
  
      ROLLBACK;
      -- set vars
  
      SET response = "the transaction has failed, you may have already updated the account.";
      select response;
  
      END;
  
  
  START TRANSACTION;
  
      -- we are inserting data, using information from another table
  
      update user, updateUserNamesAndNumbers
  
      SET user.firstName   = updateUserNamesAndNumbers.firstName,
          user.lastName    = updateUserNamesAndNumbers.lastName,
          user.landline    = updateUserNamesAndNumbers.landline,
          user.mobile      = updateUserNamesAndNumbers.mobile
  
      WHERE 
  
          updateUserNamesAndNumbers.uuid = transferCode
  
      AND
  
          updateUserNamesAndNumbers.userId= user.user_id
  
      ;
  
  
  -- finally delete the original tuple
  
  
  DELETE from updateUserNamesAndNumbers
  
  where uuid= transferCode ;
  
  
  SET response="The transaction has succeeded";
  
  COMMIT;
  </code></pre>
  
  <p>SELECT response;</p>
  
  <p>END</p>