set autocommit=0为何不起作用及对于事务隔离性的理解
set autocommit=0为何不起作用及对于事务隔离性的理解
set autocommit
指事务非自动提交,此句执行以后,每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。
小实验
做了一个小实验,打开一个窗口,开了一个连接,set autocommit = 0开冲。
发现怎么不起作用???
经过同事指点,又开了另外的一个窗口,建立了数据库连接。
发现这个新建立的窗口是看不到ppp这一行的,也就是看不到之前那个窗口的那次insert结果。
直到之前的那个窗口提交后(commit后),才能在这个窗口看得到。
思考
其实一个窗口对应着一个数据库连接,那么在同一个窗口里 = 在同一个连接里 = 在同一个事务里。
那么问题来了,事务的隔离性隔离的到底是什么。从上面看的话,隔离的是这个数据库的多个连接。也就是说事务和连接是一对一的关系。
tips
set autocommit 和 START TRANSACTION
- 不管autocommit 是1还是0 ,START TRANSACTION 后,只有当commit数据才会生效,ROLLBACK后就会回滚。
- 当autocommit 为 0 时,不管有没有START TRANSACTION。
只有当commit数据才会生效,ROLLBACK后就会回滚。
为什么不推荐set autocommit=0
如果使用set autocommit=0,如果数据库是长连接,这就导致接下来的查询都在事务中,出现了长事务,长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库。
如何避免长事务对业务的影响?
出自林晓斌老师的mysql45讲。
首先,从应用开发端来看:
-
确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展,把MySQL的general_log开起来,然后随便跑一个业务逻辑,通过general_log的日志来确认。一般框架如果会设置这个值,也就会提供参数来控制行为,你的目标就是把它改成1。
-
确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要,但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
-
业务连接数据库的时候,根据业务本身的预估,通过SET MAX_EXECUTION_TIME命令,来控制每个语句执行的最长时间,避免单个语句意外执行太长时间。(为什么会意外?在后续的文章中会提到这类案例)
其次,从数据库端来看:
- 监控 information_schema.Innodb_trx表,设置长事务阈值,超过就报警/或者kill;
- Percona的pt-kill这个工具不错,推荐使用;
- 在业务功能测试阶段要求输出所有的general_log,分析日志行为提前发现问题;
- 如果使用的是MySQL 5.6或者更新版本,把innodb_undo_tablespaces设置成2(或更大的值)。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。