how to use commit and rollback in my store procedure

[jobyjames85]

i have a store procedure inside that store procedure it call another store procedure .
how can i set rollback. when the store procedure start, the user stop the application then all data will be rollback.

i am sending my 2 store procedure.

1.

CREATE PROCEDURE dpms.`DataBaseBackup`(IN PLotid varchar(3))
BEGIN

     Declare l_loop_end INT default 0;
     DECLARE Poperationid varchar(10);
     DECLARE PStoneid varchar(12);
     Declare PoldOpertionid varchar(10);
     Declare LotCount varchar(8);
     Declare stoneIncrement varchar(12) default 0;
     Declare StoneCount varchar(15);
     
     
     DECLARE curProcess CURSOR
            FOR select STONE_ID from d0020 where  LOT_ID =PLotid;
     
     DECLARE curStone CURSOR
            FOR select OPERATION_ID from d0100 where STONE_ID =PStoneid ;
            
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_loop_end = 1;
      select max(LOT_ID) into LotCount from dpmsbk.d0010;
 
      IF (LotCount is NULL ) THEN
          SET LotCount=1;
       ELSE
          SET LotCount=LotCount+1;
      END IF;
            OPEN curProcess;
            REPEAT
            fetch curProcess into PStoneid;
       if NOT l_loop_end THEN
        
        set stoneIncrement=stoneIncrement+1;
           
           set StoneCount = CONCAT(LotCount, '-', stoneIncrement);

         OPEN curStone;
          REPEAT
            fetch curStone into POperationId;
            if NOT l_loop_end then                     
              
              CALL BackUpOneOperation(POperationId,StoneCount);   
            
            end if;
            
            UNTIL l_loop_end END REPEAT; 
            
          INSERT INTO dpmsbk.d0020(STONE_ID,LOT_ID,SEQUENCE_NO,WEIGHT,SUBLOT_CD,PARENT_STONE_ID,STATUS,REMARKS,
                        BARCODE_DATE,REGIST_USER_NO,REGIST_DT,UPDATE_USER_NO,UPDATE_DT,DELFLG,REFERENCE_STONE_ID)SELECT StoneCount,
                        LotCount,d0020.SEQUENCE_NO,d0020.WEIGHT,d0020.SUBLOT_CD,d0020.PARENT_STONE_ID,d0020.STATUS,
                        d0020.REMARKS,d0020.BARCODE_DATE,d0020.REGIST_USER_NO,d0020.REGIST_DT,d0020.UPDATE_USER_NO,
                        d0020.UPDATE_DT,d0020.DELFLG,d0020.STONE_ID FROM d0020 WHERE STONE_ID=PStoneid;
                        
         delete from d0020 where STONE_ID=PStoneid;
          
          close curStone; 
            
            SET l_loop_end = 0; 
            END IF; 
            
            UNTIL l_loop_end END REPEAT;
            
            
          INSERT INTO dpmsbk.d0010(LOT_ID,LOT_DATE,SIZE_CD,COLOUR_CD,LOT_TYPE_CD,CLARITY_CD,GROUP_CD,WEIGHT,
                        RECEIVED_FROM,INVOICE_NO,TOTAL_PIECES,RATE_D,RATE_RS,AMOUNT_D,AMOUNT_RS,
                        CONVERSTION_RATE,REMARKS,REGIST_USER_NO,REGIST_DT,UPDATE_USER_NO,UPDATE_DT,STATUS,DELFLG,
                        LOT_ID_PREFIX,LABOUR,REFERENCE_LOT_ID)SELECT LotCount,d0010.LOT_DATE,d0010.SIZE_CD,d0010.COLOUR_CD,d0010.LOT_TYPE_CD,d0010.CLARITY_CD,
                        d0010.GROUP_CD,d0010.WEIGHT,d0010.RECEIVED_FROM,d0010.INVOICE_NO,d0010.TOTAL_PIECES,d0010.RATE_D,
                        d0010.RATE_RS,d0010.AMOUNT_D,d0010.AMOUNT_RS,d0010.CONVERSTION_RATE,d0010.REMARKS,
                        d0010.REGIST_USER_NO,d0010.REGIST_DT,d0010.UPDATE_USER_NO,d0010.UPDATE_DT,d0010.STATUS,
                        d0010.DELFLG,d0010.LOT_ID_PREFIX,d0010.LABOUR,d0010.LOT_ID FROM d0010 WHERE d0010.LOT_ID=PLotid;
           
        delete from  d0010 where LOT_ID =PLotid;
            
          CLOSE curProcess;
          END;

2.

CREATE PROCEDURE dpms.`BackUpOneOperation`(IN ParamOperationID varchar(10),IN StoneReferenceID text)
BEGIN 

     Declare l_loop_end INT default 0;
     DECLARE PStoneid varchar(12);
     Declare PoldOpertionid varchar(10);
       
     DECLARE ACT_AFT_150DATA_ID_BIG varchar(10);
     DECLARE ACT_AFT_150DATA_ID_SMALL varchar(10);
     DECLARE ACT_EXP_150DATA_ID varchar(10);

     Declare  ACT_EXP_210DATA_ID    varchar(10);
     Declare  ACT_AFT_210DATA_ID    varchar(10);

     Declare ACT_EXP_240DATA_ID    varchar(10);
     Declare ACT_POL_240DATA_ID    varchar(10);     
            
          Select ACT_AFT_DATA_ID_BIG, ACT_AFT_DATA_ID_SMALL,ACT_AFT_DATA_ID
                INTO ACT_AFT_150DATA_ID_BIG, ACT_AFT_150DATA_ID_SMALL,ACT_EXP_150DATA_ID
                from D0150 where OPERATION_ID = ParamOperationID;
            
          Select ACT_EXP_DATA_ID,ACT_AFT_DATA_ID
                INTO ACT_EXP_210DATA_ID, ACT_AFT_210DATA_ID
                from D0210 where OPERATION_ID = ParamOperationID;
                
          Select ACT_EXP_DATA_ID,ACT_POL_DATA_ID
                INTO ACT_EXP_240DATA_ID, ACT_POL_240DATA_ID
                from D0240 where OPERATION_ID = ParamOperationID;
              
          INSERT INTO dpmsbk.d0030(select * from d0030 where ACT_AFT_DATA_ID=ACT_AFT_150DATA_ID_BIG or ACT_AFT_DATA_ID=ACT_AFT_150DATA_ID_SMALL or ACT_AFT_DATA_ID=ACT_EXP_150DATA_ID or ACT_AFT_DATA_ID=ACT_AFT_210DATA_ID );
            
          INSERT INTO dpmsbk.d0040(select * from d0040 where ACT_EXP_DATA_ID=ACT_EXP_210DATA_ID or ACT_EXP_DATA_ID=ACT_EXP_240DATA_ID or ACT_EXP_DATA_ID=ACT_POL_240DATA_ID) ;
            
          INSERT INTO dpmsbk.d0150(select * from d0150 where OPERATION_ID =ParamOperationID);
            
          INSERT INTO dpmsbk.d0210(select * from d0210 where OPERATION_ID =ParamOperationID);
            
          INSERT INTO dpmsbk.d0240(select * from d0240 where OPERATION_ID =ParamOperationID);
            
          INSERT INTO dpmsbk.d0100(OPERATION_ID,STONE_ID,SEQUENCE_NO,OPERATION_TYPE,ISSUE_DATE,
                 ISSUE_BY,RECEIVE_DATE,RECEIVE_BY,SUB_MANAGER,KARIGAR,ISSUE_REMARKS,REGIST_USER_NO,REGIST_DT,
                 UPDATE_USER_NO,UPDATE_DT,MISTAKE,DELFLG,IS_RECIEVED,ISSUE_STATUS,MAKE_ST_AS,CONTRACTOR)SELECT d0100.OPERATION_ID,
                 StoneReferenceID,d0100.SEQUENCE_NO,d0100.OPERATION_TYPE,d0100.ISSUE_DATE,d0100.ISSUE_BY,d0100.RECEIVE_DATE,
                 d0100.RECEIVE_BY,d0100.SUB_MANAGER,d0100.KARIGAR,d0100.ISSUE_REMARKS,d0100.REGIST_USER_NO,
                 d0100.REGIST_DT,d0100.UPDATE_USER_NO,d0100.UPDATE_DT,d0100.MISTAKE,d0100.DELFLG,d0100.IS_RECIEVED,
                 d0100.ISSUE_STATUS,d0100.MAKE_ST_AS,d0100.CONTRACTOR FROM d0100 where OPERATION_ID =ParamOperationID;

          delete from d0040 where (ACT_EXP_DATA_ID = ACT_EXP_210DATA_ID) or (ACT_EXP_DATA_ID = ACT_EXP_240DATA_ID) OR (ACT_EXP_DATA_ID=ACT_POL_240DATA_ID);

          delete from d0030 where (ACT_AFT_DATA_ID = ACT_AFT_150DATA_ID_BIG) or (ACT_AFT_DATA_ID = ACT_AFT_150DATA_ID_SMALL) or (ACT_AFT_DATA_ID = ACT_EXP_150DATA_ID) or(ACT_AFT_DATA_ID = ACT_AFT_210DATA_ID);

          Delete from d0240 where OPERATION_ID=ParamOperationID;

          Delete from d0210 where  OPERATION_ID = ParamOperationID;

          Delete From d0150 where OPERATION_ID = ParamOperationID;

          Delete From d0100 where OPERATION_ID = ParamOperationID;   
          
          set ACT_AFT_150DATA_ID_BIG =NULL;
          set ACT_AFT_150DATA_ID_SMALL=NULL;
          set ACT_EXP_150DATA_ID=NULL;
          set ACT_EXP_210DATA_ID=NULL;
          set ACT_AFT_210DATA_ID=NULL;
          set ACT_EXP_240DATA_ID =NULL;
          set ACT_POL_240DATA_ID=NULL;
            
END;