1.在循环中提交的问题
很多开发人员非常喜欢在循环中进行事务提交，下面演示一个他们经常写的一个存储过程示例，如下所示：
drop procedure if exists load1; create procedure load1(count int unsigned)begin declare s int unsigned default 1; declare c char(80) default repeat('a',80); while s <= count do insert into t1 select null,c; commit; set s=s+1; end while; end;
在上面的例子中，是否加上commit命令并不是关键。由于mysql innodb的存储引擎默认为自动提交，因此去掉存储过程中的commit结果是一样的。如下所示，下面也是另一个容易被开发人员忽视的问题：
drop procedure if exists load2; create procedure load2(count int unsigned)begin declare s int unsigned default 1; declare c char(80) default repeat('a',80); while s <= count do insert into t1 select null,c; set s=s+1; end while; end;
不论上面哪个存储过程，当发生错误时，数据库会停留在一个未知的位置。例如我们要插入10000条数据，但是在插入5000条时发生了错误，然而这5000条已经存放在了数据库中，我们如何处理？另外一个是性能问题，上面的两个存储过程都不会比下面的这个存储过程快，因为下面这个是将insert放在了一个事务中：
drop procedure if exists load3; create procedure load3(count int unsigned)begin declare s int unsigned default 1; declare c char(80) default repeat('a',80); start transaction; while s <= count do insert into t1 select null,c; set s=s+1; end while; commit; end;
对于上面三个存储过程，我们分别插入100万数据来比较执行时间，如下所示，显然可以看到第三种方法要快很多，这是因为每次提及都要写一次重做日志，所以load1和load2实际写了100万次重做日志。对于存储过程load3,我们只写了1次重做日志。
先准备一个测试表
create table `t1` (`id` int not null auto_increment ,`name` varchar(500) null ,primary key (`id`)) ;
执行测试
09:50:44 test> call load1(1000000); query ok, 0 rows affected (1 min 4.90 sec)09:54:23 test> truncate table t1; query ok, 0 rows affected (0.05 sec)09:54:25 test> call load2(1000000); query ok, 1 row affected (1 min 3.38 sec)09:55:32 test> truncate table t1; query ok, 0 rows affected (0.20 sec)09:55:58 test> call load3(1000000); query ok, 0 rows affected (33.90 sec)
对于第二个存储过程load2,我们也可以人为的开启下事务，同样可以达到存储过程load3的效果，执行时间如下所示：
09:57:42 test> begin; query ok, 0 rows affected (0.00 sec)09:57:46 test> call load2(1000000); query ok, 1 row affected (34.08 sec)09:58:26 test> commit; query ok, 0 rows affected (0.76 sec)
2.关于使用自动提交
在一些特殊场景下，有时候自动提交不一定是个好的事情， 如我们上面讲到的循环提交的问题，mysql数据库默认是自动提交（autocommit)。可以通过如下方式来改变mysql的提交方式：
10:35:34 test> set autocommit=0; query ok, 0 rows affected (0.00 sec)
也可以使用start transation或者begin显示的开启一个事务。mysql会自动执行
set autocommit=0，并在commit或rollback结束一个事务后执行set autocommit=1 。
3.使用自动回滚处理异常
当存储过程发生异常的时候怎么办，innodb存储引擎支持通过一个handler来进行事务的自动回滚操作。如在存储过程中发生错误会自动进行回滚操作。如下面一个示例：
create procedure sp_auto_rollback_demo() begin declare exit handler for sqlexception rollback; start transaction; insert into b select 1; insert into b select 2; insert into b select 1; insert into b select 3; commit; end;
测试表如下
create table `b` ( `a` int(11) not null default '0', primary key (`a`) ) engine=innodb default charset=utf8;
执行上面的存储过程，因此会在插入第二个记录1时发生错误，但是因为启用了自动回滚操作，这个存储过程执行结果如下：
10:09:46 test> call sp_auto_rollback_demo; query ok, 0 rows affected (0.01 sec) 10:10:04 test> select * from b;empty set (0.00 sec)
看起来没有问题，运行比较正常，但是在执行sp_auto_rollback_demo的时候是执行成功了还是失败了？对此，我们可以进行如下处理，示例如下：
drop procedure if exists sp_auto_rollback_demo; create procedure sp_auto_rollback_demo()begin declare exit handler for sqlexception begin rollback; select -1; end; start transaction; insert into b select 1; insert into b select 2; insert into b select 1; insert into b select 3; commit; select 1; end;
当发生错误时，先回滚然后返回-1,表示运行发生了错误。返回1表示运行正常。运行结果如下：
10:16:19 test> call sp_auto_rollback_demo\g*************************** 1. row ***************************-1: -1 1 row in set (0.00 sec) query ok, 0 rows affected (0.01 sec) 10:16:35 test> select * from b; empty set (0.00 sec)
以上就是mysql事务编程性能和问题分析 [开发必看]的内容。