mysql autocommit_MySQL AutoCommit带来的问题

水深无声 2023-01-11 03:44 119阅读 0赞

原创内容，转载请注明出处

现象描述

测试中发现，服务A在得到了服务B的注册用户成功response以后，开始调用查询用户信息接口，却发现无法查询出任何结果。检查binlog发现，在查询请求之前，数据库确实已经完成了commit操作，并且可以在sqlyog等客户端工具中查询出正确的结果。

下面是这个流程的时序图：

![60a988105eab4943ca235849f03834c5.png][]

问题出现在Server A向数据库发起查询的时候，返回的结果总是空。

问题分析

这个问题显然是一个事务隔离的问题，最开始的思路是，服务A所在的机器，其事务开启时间应该是在服务B的机器commit操作之前开启的，但是通过DEBUG日志分析connection的获取和提交时间，发现两个服务器之间不存在这样的关系，服务B永远是在服务A返回了正确的response之后才会调用数据库接口，进行getConnection操作，进而进行查询操作。

显然这并不能支持刚才的设想，但是结论一定是正确的，就是因为事务隔离级别导致了Server A读到的永远是快照，发生了可重复读。

后来调整了一下思路，发现MySQL还有一个特性就是AutoCommit，即默认情况下，MySQL是开启事务的，下面表格能说明问题，表1：

![cae9e2d04317a0d19dca631339608309.png][]

但是，如果AutoCommit不是默认开启呢？结果就会变成下面的表格，表2：

![05d1dbda9062fc8b93a552e2b6be6377.png][]

在关闭AutoCommit的条件下，SessionA在T1和T2两个时间点执行的SQL语句其实在一个事务里，因此每次读到的其实只是一个快照。

那么在连接池条件下，情况如何？

设置一个极端条件，连接池只给一个连接，编写两个类，一个负责插入数据，一个负责循环读取数据，但是读取数据的类在执行读取方法之前，会执行一个空方法，这个方法只会做一件事情，就是获取连接，将其AutoCommit设置为FALSE，关闭连接。

两段代码如下：

写入线程：

public static void main( String\[\] args ) throws Exception

DBconfigEntity entity = new DBconfigEntity();

entity.setDbName("test");

entity.setDbPasswd("123456");

entity.setDbUser("root");

entity.setIp("127.0.0.1");

entity.setPort(3306);

MysqlClient.init(entity);

MysqlClient instance = MysqlClient.getInstance();

Connection conn = instance.getConnection();

conn.setAutoCommit(false);

String sql = "insert into test1(uname) values (?)";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "PPP");

statement.executeUpdate();

conn.commit();

statement.close();

conn.close();

//永远休眠，但是永远持有连接池

Thread.sleep(Long.MAX\_VALUE);

读取类：

public class GetClient \{

private void query() throws SQLException

System.out.println("start");

MysqlClient instance = MysqlClient.getInstance();

Connection conn = instance.getConnection();

String sql = "select uname from test1";

PreparedStatement statement = conn.prepareStatement(sql);

ResultSet rs = statement.executeQuery();

while (rs.next()) \{

System.out.println(rs.getString("uname"));

statement.close();

rs.close();

conn.close();

private void nothing() throws SQLException

MysqlClient instance = MysqlClient.getInstance();

Connection conn = instance.getConnection();

conn.setAutoCommit(false);

conn.close();

public static void main(String\[\] args) throws SQLException, InterruptedException, ClassNotFoundException \{

DBconfigEntity entity = new DBconfigEntity();

entity.setDbName("test");

entity.setDbPasswd("123456");

entity.setDbUser("root");

entity.setIp("127.0.0.1");

entity.setPort(3306);

MysqlClient.init(entity);

GetClient client = new GetClient();

client.nothing();

while (true) \{

client.query();

Thread.sleep(5000);

表初始没有任何数据，首先运行读取类，此时读取类只会不停的打印“start”，此时启动写入类，观察发现，console并不会打印数据库test1表查询的结果，但是在数据库工具中查看，test1表确实已经有了数据。

这是因为在连接池条件下，如果这个连接之前被借出过，并且曾经被设置成了AutoCommit为FALSE，那么这个连接在其生存时间内，永远会默认开启事务，这是MySQL自身决定的，因为连接池只是持有连接，代码中的close操作只是将该连接还给连接池，但是并没有真的将连接销毁，因此连接的属性仍然保持上次设置的样子。当另一个方法开始，重新执行getConnection获取链接时，是有可能获取到之前被设置为AutoCommit为FALSE的连接的，这个时候就相当于上面的表2中Session A在T3时间点的情况，无论如何查询，都会查不出任何数据来。

如下图：

![660c0b7c4be4eea42b9b8e53fc5191a6.png][]

无论如何commit，都无法改变这个连接的autocommit属性。

因为测试时采用的是一个连接这种极端条件，因此该现象非常容易复现，且是100%的复现，但是在测试条件下，并非100%复现，而是在重启之后会好一段时间，一段时间以后就会重新出现这个情况。

如果将读取类的代码稍加修改：

public class GetClient \{

private void query() throws SQLException

System.out.println("start");

MysqlClient instance = MysqlClient.getInstance();

Connection conn = instance.getConnection();

conn.setAutoCommit(true);

String sql = "select uname from test1";

PreparedStatement statement = conn.prepareStatement(sql);

ResultSet rs = statement.executeQuery();

while (rs.next()) \{

System.out.println(rs.getString("uname"));

statement.close();

rs.close();

conn.close();

private void nothing() throws SQLException

MysqlClient instance = MysqlClient.getInstance();

Connection conn = instance.getConnection();

conn.setAutoCommit(false);

conn.close();

public static void main(String\[\] args) throws SQLException, InterruptedException, ClassNotFoundException \{

DBconfigEntity entity = new DBconfigEntity();

entity.setDbName("test");

entity.setDbPasswd("123456");

entity.setDbUser("root");

entity.setIp("127.0.0.1");

entity.setPort(3306);

MysqlClient.init(entity);

GetClient client = new GetClient();

client.nothing();

while (true) \{

client.query();

Thread.sleep(5000);

注意我在query方法中加入这一句：conn.setAutoCommit(true);

此时这个问题不再出现。

源码分析

jdbc驱动源码分析

Connection是Java提供的一个标准接口：java.sql.Connection，其具体实现是：com.mysql.jdbc.ConnectionImpl。

分析jdbc驱动代码可知，jdbc默认的AutoCommit状态是TRUE：

![f604f918d78c4d141fbd6fa66206dad5.png][]

这实际上和MySQL的默认值是一样的。

tomcat-jdbc源码分析

tomcat-jdbc的close方法由拦截器实现，具体的逻辑代码：

if (compare(CLOSE\_VAL,method)) \{

if (connection==null) return null; //noop for already closed.

PooledConnection poolc = this.connection;

this.connection = null;

pool.returnConnection(poolc);

return null;

实际上此处只是将连接还给了连接池，没有对连接进行任何处理。

tomcat-jdbc维护了两个Queue：busy和idle，用于存放空闲和已借出连接，连接还给连接池的过程简单的说就是将该连接从busy队列中移除，并放在idle队列中的过程。

boneCP源码分析

根据实际使用的经验看，boneCP连接池在使用的过程中并没有出现这个问题，分析boneCP的Connection具体实现，发现在close方法的具体实现中，有这样的一段代码逻辑：

if (!getAutoCommit()) \{

setAutoCommit(true);

这段逻辑会判断该连接的AutoCommit属性是否为FALSE，如果是，就自动将其置为TRUE。

因此，在这个连接被交还回连接池时，AutoCommit属性总是TRUE。

结论

任何查询接口都应该在获取连接以后进行AutoCommit的设置，将其设置为true。

原创内容，转载请注明出处

[60a988105eab4943ca235849f03834c5.png]: /images/20221119/99d0aeb35f6f4cb0ab6def62f5b8e941.png
[cae9e2d04317a0d19dca631339608309.png]: /images/20221119/813c20b718c74686b275a259350c4c0d.png
[05d1dbda9062fc8b93a552e2b6be6377.png]: /images/20221119/a17882dde9ec4e5ebcb28076b6b6d665.png
[660c0b7c4be4eea42b9b8e53fc5191a6.png]: /images/20221119/6386c092ecca45309d1070a104bd67c2.png
[f604f918d78c4d141fbd6fa66206dad5.png]: /images/20221119/ec0a700a2bdd49f49669907828f49a2f.png