MySQL transaction within a stored procedure

Question

The basic structure of my stored procedure is   BEGIN         Declare statements         START TRANSACTION              Query 1               Query 2               Query 3         COMMIT   END      MySQL version  5 1 61-0ubuntu0 11 10 1-log   Currently  if  query 2  fails  result of  query 1  is committed    How can I rollback the transaction if any of the query fails

User · Answer

Just an alternative to the code by  rkosegi   BEGIN         Declare statements         DECLARE EXIT HANDLER FOR SQLEXCEPTION      BEGIN              set any flags etc  eg  SET  flag   0               ROLLBACK      END       START TRANSACTION              Query 1               Query 2               Query 3         COMMIT         eg  SET  flag   1      END

User · Answer

Here s an example of a transaction that will rollback on error and return the error code   DELIMITER    CREATE DEFINER  root   localhost  PROCEDURE  SP CREATE SERVER USER       IN P server id VARCHAR 100       IN P db user pw creds VARCHAR 32       IN p premium status name VARCHAR 100       IN P premium status limit INT      IN P user tag VARCHAR 255       IN P first name VARCHAR 50       IN P last name VARCHAR 50    BEGIN      DECLARE errno INT      DECLARE EXIT HANDLER FOR SQLEXCEPTION     BEGIN     GET CURRENT DIAGNOSTICS CONDITION 1 errno   MYSQL ERRNO      SELECT errno AS MYSQL ERROR      ROLLBACK      END       START TRANSACTION       INSERT INTO server users server id  db user pw creds  premium status name  premium status limit      VALUES P server id  P db user pw creds  P premium status name  P premium status limit        INSERT INTO client users user id  server id  user tag  first name  last name  lat  lng      VALUES P server id  P server id  P user tag  P first name  P last name  0  0        COMMIT WORK   END   DELIMITER     This is assuming that autocommit is set to 0  Hope this helps

User · Answer

Take a look at http   dev mysql com doc refman 5 0 en declare-handler html  Basically you declare error handler which will call rollback  START TRANSACTION   DECLARE EXIT HANDLER FOR SQLEXCEPTION      BEGIN         ROLLBACK          EXIT PROCEDURE      END  COMMIT

User · Answer

This is just an explanation not addressed in other answers  At least in recent versions of Mysql  your first query is not committed   If you query it under the same session you will see the changes  but if you query it from a different session  the changes are not there  they are not committed   What s going on   When you open a transaction  and a query inside it fails  the transaction keeps open  it does not commit nor rollback the changes   So BE CAREFUL  any table row that was locked with a previous query likeSELECT     FOR SHARE UPDATE  UPDATE  INSERT or any other locking-query  keeps locked until that session is killed  and executes a rollback   or until a subsequent query commits it explicitly  COMMIT  or implicitly  thus making the partial changes permanent  which might happen hours later  while the transaction was in a waiting state    That s why the solution involves declaring handlers to immediately ROLLBACK when an error happens   Extra  Inside the handler you can also re-raise the error using RESIGNAL  otherwise the stored procedure executes  Successfully   BEGIN     DECLARE EXIT HANDLER FOR SQLEXCEPTION          BEGIN             ROLLBACK              RESIGNAL          END       START TRANSACTION              Query 1                Query 2                Query 3        COMMIT  END

[mysql] MySQL : transaction within a stored procedure

Examples related to mysql

Examples related to sql

Examples related to stored-procedures

Examples related to transactions