Java执行批处理更新时可能潜在的风险

为了减少数据库连接的次数，以及加快数据更新的时间，我们习惯采用批处理的方式来向数据库插入数据。当然这没有错，但是如果在写代码的时候你没有注意或者不了解其中的一些原理或者细节，那么可能就无法得到你想要的结果，甚至适得其反。

下面说下使用Java向MySQL数据库执行【批处理更新】时可能会存在的一个风险，其他编程语言也可能存在这种风险。

先看如下Java代码：

classes表定义：

CREATE TABLE `classes` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(20) NOT NULL,
  `class_num` varchar(10) NOT NULL,
  PRIMARY KEY (`class_num`),
  KEY `sid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

这段代码很简单，就是向表classes插入两条数据，采用批处理的方式。从表面上看这段代码并没有什么问题，而且就算执行后你也看不出什么，因为数据成功插入到了表classes中，没有出现任何错误。

那么上面代码究竟有什么问题呢？我们执行上面的代码，然后在MySQL端查看：

从图中可以看到数据已被成功插入表中。这时候我们再查看后台的MySQL二进制日志文件（需要提前开启，关于如何开启可参考我之前写的文章《MySQL的日志文件》）：

从info列可以看出，后台在处理这个批处理时，开启了两个事务（每个BEGIN和COMMIT构成一个事务），每个事务只执行一条插入：后台压根就没有执行批处理操作！！！为什么会这样？我们客户端代码不是执行了stmt.addBatch(sql)和stmt.executeBatch()命令吗？

这时候打开通用日志，就可以发现原因：

原来客户端连接上MySQL服务后，MySQL后台默认执行了一些命令，这些命令中包括了SET autocommit=1，这个命令告知MySQL开启事务自动提交，即是把每条命令（增删改查）当做一个事务处理，并且执行后立即提交，这是MySQL的默认处理方式。

所以就出现了上面二进制日志记录所描述的情况：每个Insert操作都被单独作为一个事务执行。

那这样有什么坏处呢？首先每个语句都启动一个事务来处理，会给服务器增加额外的开销，数据量大时响应可能会变得很慢；再一个是在执行的过程中，如果出错，那么前面的插入将无法回滚。

比如我们将代码改改，如：

由代码可见，第2条语句由于在后面加入了“error“，可以知道它会执行错误，这时候我们当然希望它会全部回滚，回到之前的状态（不要插入任何数据），因为我们调用了批处理，要么全部成功，要么全部失败。

但我们执行后却是：

后台二进制日志：

由图可见，虽然存在错误，但还是成功插入了第一条数据，这是我们不愿看到的，我们希望退回到未执行该操作时的状态。导致出现这种情况的原因，如前所述，是因为客户端连接上MySQL服务后，MySQL服务默认执行了SET autocommit=1这个命令。

那我们如何避免这种情况呢？很简单，既然是由于SET autocommit=1导致的，那么我们就让SET autocommit=0。代码如下：

在后台先把5这条数据删掉：

然后执行上面的Java代码，这时候查看后台通用日志：

 
 可见连接成功后后台已将autocomit设置为0。

此时再查看classes表：

并未插入任何数据。

再查看二进制日志：

里面只有删除操作，并未执行任何插入操作。

我们将代码再改为：

执行该代码，然后查看表classes：

 数据已被成功插入。此时再查看二进制日志：

可以看到只有一个BEGIN … COMMIT，两个插入语句都被包含在里面了，说明MySQL服务器将其作为同一个事务来处理，所以就很好解释上面二进制日志未记录任何插入操作的原因，因为只有一个事务，所以当其中有一条语句出错时，事务会全部回滚（这是事务的原子性隔离级别决定的，关于事务的隔离级别，可参考我之前写的文章《MySQL的事务隔离级别》）。

这就是使用Java做批处理时可能存在的一个风险，而且当你处理的是涉及具体金额的数据的时候尤为重要。我以前也这样写代码，或者经常在网上看到conn.setAutoCommit(false)这样的建议，但那时候并未知晓其中的道理，然后最近也是由于一个很偶然的机会才从MySQL的日志文件中发现了具体的原因，所以整理出来与大家分享。