mysql set commit_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 ;

回滚成功