事务隔离:为什么你改了我还看不见
事务就是要保证一组数据库操作,要么全部成功,要么全部失败。在MySQL中,事务支持是在引擎层实现。并不是所有的引擎都支持事务,MySQL原生的MyISAM不支持事务。本文中都是以InnoDB为例来讲解的。
隔离性与隔离级别
- 事务的ACID(Atomicity,Consistency,Isolation,Durability 即原子性、一致性、隔离性、持久性)属性。
- 隔离级别的产生
- 多个事务同时执行时,可能会出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的问题,为了解决这些问题,就有隔离级别的概念
- 隔离级别越高,效率越低。SQL标准的隔离级别: 读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和 串行化(serializable)。
- 视图理解为数据副本,每次创建视图(innodb的一致性视图)时,将当前『已持久化的数据』创建副本,后续直接从副本读取,从而达到数据隔离效果
- 案例分析
mysql> create table user(id int) engine=InnoDB;
insert into user(id) values(1);
假设有A B 两个事务:
事务A | 事务B |
---|---|
启动事务,select得到值V1 | 启动事务 |
空 | select 得到 值V1 |
空 | update 值1 为 2 |
select 得到 值V1 | 空 |
空 | commit |
select 得到 值V2 | 空 |
commit | 空 |
select 得到 值V3 | 空 |
- 读未提交(read uncommitted)
不存在视图,直接返回记录上最新值。上图案例中:V1=1,V2=V3=2
- 读提交(read committed)
在每个SQL语句开始执行时创建。上图案例:V1=1,V2=V3=2
- 可重复读(repeatable read)
事务启动时创建,整个事务存在期间都用这个视图。上图案例:V1=V2=1,V3=2
- 串行化(serializable)
直接用加锁的方式来避免并行访问。上图案例:V1=V2=1,V3=2。事务B执行update时,会被锁住。直到事务A提交后,事务B才继续执行
- Oracle 数据库的默认隔离级别其实就是“读提交”,因此对于一些从 Oracle 迁移到 MySQL 的应用,为保证数据库隔离级别的一致,得将 MySQL 的隔离级别设置为“读提交”
//启动参数 transaction-isolation 的值设置成 READ-COMMITTED
mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
-
什么时候需要“可重复读”的场景呢
作者这里取的例子是银行月底数据校对业务,判断上个余额和当前余额差额,是否与本月账单明细一致。可重复读的事务启动后,是不受其他事务影响,故即使有用户发生了一笔新的交易,也不会影响校对结果
事务隔离的实现–以可重复读为例
-
在MySQL中,实际每条记录在更新时都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。
-
假设一个值从1按顺序改成2、3、4,在回滚日志里就会有类似的记录:
-
同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC).
-
回滚日志什么删除呢?
当没有事务再需要用到这些回滚日志时,回滚日志会被删除。
-
啥时候才不需要这些回滚日志呢?
当系统没有比这个回滚日志更早的read-view的时候
-
为什么建议尽量不要使用长事务?
长事务意味着系统里存在很老的事务视图。由于这些事务可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里它用到的回滚记录必须都保留,这就导致大量占用存储空间。
长事务还占用锁资源,也可能拖垮整个库。
MySQL5.5及更低版本,回滚日志和数据字典一起放在ibdata文件里,即使长事务最终提交,回滚段被清理,文件也不会变小。
事务的启动方式
- 显示启动事务语句,begin或start transaction。提交是commit,回滚是rollback.
- set autocommit = 0 ,这个命令会将线程的自动提交关掉。这个事务会存续到你主动commit或者rollback或者断开连接
- 建议是使用 set autocommit = 1,避免意外的长事务。
- 使用commit work and chain,则提交事务并自动启动下一个事物,也省去了再次执行begin语句的开销。
- 查找长事务
//查找持续时间超过60s的事务
select * from information_schema.innodb_trx where TIME TO SEC(timediff(now(),trx_started))>60;
- 避免长事务的方案
- 从应用开发来看:
使用set autocommit = 1,开启MySQL的general_log
代码评审,去掉不必要的只读事务
评估业务本身,通过SET MAX_EXECUTION_TIME 命令来控制执行的最长时间 - 从数据库端来看:
监控innodb_trx这张表,出现超长时间则预警
在功能测试阶段开启general_log,分析日志行为
使用的版本是5.6及以上的,把innodb_undo_tablespaces设置成2或者更大值。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。
本文是通过学习极客时间“MySQL实战45讲”,做的学习笔记,有错误的地方,请网友提出,大家共同学习,后续陆续更新!扫描下方二维码,可以共同学习。