mysql中set autocommit=0与start transaction区别

 

set autocommit=0指事务非自动提交,自此句执行以后,每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

 

 

1、不管autocommit 是1还是0 
     START TRANSACTION 后,只有当commit数据才会生效,ROLLBACK后就会回滚。

 

2、当autocommit 为 0 时
    不管有没有START TRANSACTION。
    只有当commit数据才会生效,ROLLBACK后就会回滚。

 

3、如果autocommit 为1 ,并且没有START TRANSACTION 。
    调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

 

 

回滚成功

set autocommit =1;

DELIMITER $$
DROP PROCEDURE IF EXISTS  mmmm $$  
CREATE DEFINER=`tms_admin`@`%` PROCEDURE `mmmm`()
BEGIN


      
    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET t_error=1;
    


    START TRANSACTION;
    SAVEPOINT p1;  

            update t_user set instance_seq_id = 'tt00006';
            update t_user set instance_seq_id000 = 'tt00007';


            IF t_error = 1 THEN 
                ROLLBACK to p1;
            ELSE 
                COMMIT; 
            END IF;

END$$
DELIMITER ;

CALL `tms_inst_tt00003`.`mmmm`();

 

set autocommit =0;

DELIMITER $$
DROP PROCEDURE IF EXISTS  mmmm $$  
CREATE DEFINER=`tms_admin`@`%` PROCEDURE `mmmm`()
BEGIN


      
    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET t_error=1;
    


    START TRANSACTION;
    SAVEPOINT p1;  

            update t_user set instance_seq_id = 'tt00006';
            update t_user set instance_seq_id000 = 'tt00007';


            IF t_error = 1 THEN 
                ROLLBACK to p1;
            ELSE 
                COMMIT; 
            END IF;

END$$
DELIMITER ;

CALL `tms_inst_tt00003`.`mmmm`();

回滚成功

 

 

set autocommit =0;

DELIMITER $$
DROP PROCEDURE IF EXISTS  mmmm $$  
CREATE DEFINER=`tms_admin`@`%` PROCEDURE `mmmm`()
BEGIN


      
    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET t_error=1;

    SAVEPOINT p1;  

            update t_user set instance_seq_id = 'tt00006';
            update t_user set instance_seq_id000 = 'tt00007';


            IF t_error = 1 THEN 
                ROLLBACK to p1;
            ELSE 
                COMMIT; 
            END IF;

END$$
DELIMITER ;

回滚成功

 

posted on 2016-03-24 16:03  袜子破了  阅读(14981)  评论(1编辑  收藏  举报