极客学院-《MySQL实战45讲》 学习笔记
03 | 事务隔离:为什么你改了我还看不见?
隔离性与隔离级别
ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)
事务隔离级别
当数据库上有多个事务同时执行的时候,就可能出现脏读(dirty read)、不可重复读(non-repeatable read)、**幻读(phantom read)**的问题,为了解决这些问题,就有了“隔离级别”的概念。
在谈隔离级别之前,你首先要知道,你隔离得越严实,效率就会越低。
SQL 标准的事务隔离级别包括:读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable )
。
- 读未提交: 一个事务还没提交时,它做的变更就能被别的事务看到。
- 读提交(RC): 别人改数据的事务已经提交,我在我的事务中才能读到。
- 可重复读(RR): 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。
我在我的事务更改后,也读不到更改后的值
- 串行化:
“读提交”和“可重复读”比较难理解
用一个例子说明这几种隔离级别。假设数据表 T 中只有一列,其中一行的值为 1,下面是按照时间顺序执行两个事务的行为。
mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
我们来看看在不同的隔离级别下,事务 A 会有哪些不同的返回结果:也就是图里面 V1、V2、V3 的返回值分别是什么。
- 若隔离级别是**“读未提交**:
- V1 的值就是 2。这时候事务 B 虽然还没有提交,但是结果已经被 A 看到了。
- 因此,V2、V3 也都是 2。
- 若隔离级别是**“读提交(RC)**:
- V1 是 1,
- 事务 B 的更新在提交后才能被 A 看到。所以,V2 的值是 2
- V3 的值也是 2。
- 若隔离级别是**“可重复读(RR)**:
- V1 是 1
- V2 也是1。 之所以 V2 还是 1,遵循的就是这个要求:
事务在执行期间看到的数据前后必须是一致
的。 - V3 的值是 2。
- 若隔离级别是**“串行化**: 事务 B 执行“将 1 改成 2”的时候,会被锁住,直到事务 A 提交后,事务 B 才可以继续执行。
- V1 是 1
- V2 也是1。
- V3 的值也是 2。
MVCC视图
在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。
- 在**“可重复读”隔离级别下,这个视图是在事务启动时创建的**,整个事务存在期间都用这个视图。
- 在**“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行**的时候创建的。
- 这里需要注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有视图概念;
- 而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
Oracle 数据库的默认隔离级别其实就是**“读提交”,因此对于一些从
Oracle 迁移到 MySQL 的应用
,为保证数据库隔离级别的一致,你一定要记得将 MySQL 的隔离级别设置为“读提交”**。
将启动参数transaction-isolation
的值设置成READ-COMMITTED
。
可以用show variables
来查看当前的值。show variables like 'transaction_isolation'; Variable_name Value transaction_isolation READ-COMMITTED
ReadView
设计InnoDB的提出了一个ReadView的概念,这个ReadView中主要包含4个比较重要的内容:
- m_ids:表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
- min_trx_id:表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id,也就是m_ids中的最小值。
- max_trx_id:表示生成ReadView时系统中应该分配给
下一个事务
的id值。注意max_trx_id并不是m_ids中的最大值,事务id是递增分配的。
比方说现在有id为1,2,3
这三个事务,之后id为3的事务提交了。那么一个新的读事务在生成ReadView时,m_ids
就包括1和2
,min_trx_id
的值就是1
,max_trx_id
的值就是4
。 - creator_trx_id:表示生成该ReadView的事务的
事务id
。我们前边说过,只有在对表中的记录做改动时(执行INSERT、DELETE、UPDATE这些语句时)才会为事务分配事务id,否则在一个只读事务中的事务id值都默认为0。
可见性
有了这个ReadView,这样在访问某条记录时,只需要按照下边的步骤判断记录的某个版本是否可见:
- 如果
被访问版本的trx_id
属性值与ReadView中的creator_trx_id
值相同,意味着当前事务在访问它自己修改过的记录,所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值小于ReadView中的
min_trx_id
值,表明生成该版本的事务在当前事务生成ReadView前已经提交
,所以该版本可以被当前事务访问。 - 如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值,表明生成该版本的事务在当前事务生成ReadView后才开启,所以该版本不可以被当前事务访问。
- 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间,那就需要判断一下trx_id属性值是不是
在m_ids列表
中,如果在,说明创建ReadView时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建ReadView时生成该版本的事务已经被提交,该版本可以被访问。
如果某个版本的数据对当前事务不可见
的话,那就顺着版本链找到下一个版本的数据,继续按照上边的步骤判断可见性,依此类推,直到版本链中的最后一个版本。如果最后一个版本也不可见的话,那么就意味着该条记录对该事务完全不可见,查询结果就不包含该记录。
在MySQL中,READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。
- READ COMMITTED —— 每次读取数据前都生成一个ReadView
- REPEATABLE READ —— 在第一次读取数据时生成一个ReadView
事务隔离的实现
在 MySQL 中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。
假设一个值从 1
被按顺序改成了2、3、4
,在回滚日志(undo log)里面就会有类似下面的记录:
当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的read-view
。
在视图 A、B、C 里面,这一个记录的值分别是 1、2、4,同一条记录在系统中可以存在多个版本
,就是数据库的多版本并发控制(MVCC)。
同时你会发现,即使现在有另外一个事务正在将 4 改成 5,这个事务跟 read-view A、B、C 对应的事务是
不会冲突
的。
undo log 删除时机
你一定会问,回滚日志总不能一直保留吧,什么时候删除呢?答案是,在不需要的时候才删除
。也就是说,系统会判断,当没有事务再需要用到这些回滚日志时,回滚日志会被删除
。
什么时候才不需要了呢?就是当系统里**没有**比这个回滚日志
更早的 read-view 的时候。
也就是说,该回滚日志的版本已经太老了,没有事务使用该版本数据了。
为什么建议你尽量不要使用长事务
长事务意味着系统里面会存在很老的事务视图
。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间
。
在 MySQL 5.5 及以前的版本,回滚日志是跟数据字典一起放在
ibdata 文件
里的,即使长事务最终提交,回滚段被清理,文件也不会变小。我见过数据只有 20GB,而回滚段有 200GB 的库。最终只好为了清理回滚段,重建整个库
。
除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库,这个我们会在后面讲锁的时候展开。
事务的启动方式
- 显式启动事务语句,
begin 或 start transaction
。配套的提交语句是commit
,回滚语句是rollback
。 set autocommit=0
,这个命令会将这个线程的自动提交关掉。select * from information_schema.innodb_trx ; set autocommit=0 ; select SLEEP(10), a.* from user a; select * from information_schema.innodb_trx ; //innodb_trx表中就多了一条RUNNING的事务
有些客户端连接框架会默认连接成功后先执行一个
set autocommit=0
的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务
。
我会建议你总是使用 set autocommit=1,自动开启和提交事务,不需要显示的使用begin、commit来开启和提交事务。
当我们需要对某些操作手动使用事务
的时候,仍然使用begin、commit来开启和提交事务 即可。
spring 事务管理,会将autocommit 自动设置为0,然后通过AOP,手动添加begin、commit关键字
查询长事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
小节
统如何避免长事务
首先,从应用开发端来看:
- 确保正确的设置
set autocommit=1
。 - 确认是否有不必要的
只读事务
。 有些框架直接将select 语句放到了事务中
。这种只读事务可以去掉。 例如:spring事务AOP切面,将所有的方法都拦截。 - 业务连接数据库的时候,根据业务本身的预估,通过
SET MAX_EXECUTION_TIME
命令,来控制每个语句执行的最长时间,避免单个语句意外执行太长时间。- 全局设置
SET GLOBAL MAX_EXECUTION_TIME=1000
; - 对某个session设置
SET SESSION MAX_EXECUTION_TIME=1000;
java程序可以通过:
jdbc:mysql://IP:PORT/DB?xxxxxx&sessionVariables=MAX_EXECUTION_TIME=2222
指定该参数。 - 全局设置
其次,从数据库端来看:
4. 监控 information_schema.Innodb_trx
表,设置长事务阈值,超过就报警 / 或者 kill
;
5. Percona 的pt-kill
这个工具不错,推荐使用;
6. 如果使用的是 MySQL 5.6 或者更新版本,把 innodb_undo_tablespaces 设置成 2(或更大的值)
。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。
innodb_undo_tablespaces
是控制undo log
是否开启独立的表空间的参数
为
0
表示:undo使用系统表空间
,即ibdata1不为0
表示:使用独立的表空间,一般名称为undo001 undo002
,存放地址的配置项为:innodb_undo_directory
.一般innodb_undo_tablespaces 默认配置为0,innodb_undo_directory默认配置为当前数据目录