前提准备
- 首先创建一个表:
CREATE TABLE hero (
number INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8;
INSERT INTO hero VALUES(1, '刘备', '蜀');
事务隔离级别
- mysql 是一个 客户端 和 服务器架构的软件,对于同一个服务器来说,可以有许多的客户端与之链接,每个客户端与服务器连接之后,就可以称之为一个会话,每个客户端都可以在自己的会话中向服务器发出请求语句,每个语句可能就是事务的一部分,假设事务的隔离性特别高,则事务需要排队请求某一个数据,这个时候对于性能的影响就太大了,所以mysql 会有不同的隔离级别,每个隔离级别舍弃了一部分隔离性来维持性能
事务并发执行遇到的问题
- 我们先假设事务没有隔离性,不是串行执行,而是并发执行
脏写
- 如果一个事务修改了另一个事务修改过的数据,就意味着产生了脏写,具体例子如下:
- Session A 都已经提交了,但是 Session B 却最后rollback了,所以 Session A 会发现自己根本没有做修改
脏读
- 如果一个事务读取另一个事务修改过的数据,就意味着产生了脏读,具体例子如下:
- Session A 和 Session B 各自开启了一个事务,然后Session A 的事务中 使用select 语句查询出了 Session B中 修改的数据,相当于 Session A 读取了一个不存在的数据
不可重复读
- 如果一个事务只能读到另一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值,那就意味着发生了不可重复读,例子如下:
幻读
- 如果一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来,那就意味着发生了幻读,例子如下:
SQL标准四种隔离级别
- 上面说的这四种问题,根据严重程度排序为:脏写 > 脏读 > 不可重复读 > 幻读
- 隔离等级从小到大,就是舍弃一些,允许一些问题发生
- sql 主要有以下四种隔离级别:
- READ UNCOMMITTED:未提交读。
- 可能发生的问题
- 脏读
- 不可重复读
- 幻读
- 可能发生的问题
- READ COMMITTED:已提交读。
- 可能发生的问题
- 不可重复读
- 幻读
- 可能发生的问题
- REPEATABLE READ:可重复读。
- 可能发生的问题 (mysql这个级别不会发生这个问题)
- 幻读
- 可能发生的问题 (mysql这个级别不会发生这个问题)
- SERIALIZABLE:可串行化。不发生问题
- READ UNCOMMITTED:未提交读。
MVCC
版本链
- 对于innodb 来说 每个记录会包含必要的两个字段:
- trx_id :事务ID
- roll_pointer :每次对某条聚簇索引进行改动时,都会把旧的版本写入到undo 日志 中,然后这个隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息。
- 还记得我们之前创建的那张表和那条记录吗,假设那条记录的事务是80,然后我们又开启两个事务100,200,并进行了一些操作:
- 然后对应这条记录的版本链如下:
- 对该记录每次更新后,都会将旧值放到一条undo日志中,就算是该记录的一个旧版本,随着更新次数的增多,所有的版本都会被roll_pointer属性连接成一个链表,我们把这个链表称之为版本链,版本链的头节点就是当前记录最新的值。另外,每个版本中还包含生成该版本时对应的事务id
ReadView
- 对于READ UNCOMMITTED隔离级别的事务来说,可以读到未提交的事务,所以直接获取记录最新的版本就好了,但是对于SERIALIZABLE 级别的事务来说,就要使用加锁的方式来访问
- 但是对于 READ COMMITTED和REPEATABLE READ隔离级别的事务来说,必须保证读到已经提交了的事务修改过的记录,也就是说假如另一个事务已经修改了记录但是尚未提交,是不能直接读取最新版本的记录的
- 但是这里有个核心的的问题,你怎么知道这个记录版本链中那个版本是当前事务可见的
- 为此提出了一个ReadView 的概念,这个数据结构包含了四个很重要的参数:
- m_ids:表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
- min_trx_id:表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id,也就是m_ids中的最小值。
- max_trx_id:表示生成ReadView时系统中应该分配给下一个事务的id值。
- creator_trx_id:表示生成该ReadView的事务的事务id
- 有了这个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时生成该版本的事务已经被提交,该版本可以被访问。
- 根据这个规则我们就知道为啥可以这两个隔离事务可以避开 脏写,但是为什么READ COMMITTED 有不可重复读的问题,而REPEATABLE READ 没有呢?
- 这个就跟他们的生成 readview 的时机不同导致了,下面我们就来根据两个事务 100,200 的操作来了解整个过程
READ COMMITTED —— 每次读取数据前都生成一个ReadView
- 在事务 100 执行:
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;
- 在事务200 执行:
更新别的表记录
-
当前这条记录的版本号:
-
此时我们开启一个新的事务,整个事务是READ COMMITTED级别,我们进行查询:
# SELECT1:Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'
-
过程如下:
- 先 生成一个 readview ,m_ids:[100,200],min_trx_id:100,max_trx_id:201,creator_trx_id:0
- 然后跟最新列进行比对,发现 此纪录的事务100 在 m_ids 列表内,就跳过,接着访问下一条
- 如此就访问到 name = ‘刘备’ ,发现这个事务ID 符合要求,就返回这条记录
-
执行完这条sql 之后,事务id 为 100已经提交了,版本链就如下:
-
当前事务我们我们继续执行查询操作:
# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1:Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'
# SELECT2:Transaction 100提交,Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞'
- select 2 的执行过程如下:
- 又单独 生成一个 readview ,m_ids:[200],min_trx_id:200,max_trx_id:201,creator_trx_id:0
- 然后跟最新列进行比对,发现 此纪录 name 的内容是 ‘诸葛亮’ 的事务200 在 m_ids 列表内,就跳过,接着访问下一条
- 如此就访问到 name = ‘张飞’ ,发现这个事务ID 100 符合要求,就返回这条记录
- 所以我们两次查询会有不同的结果,因为使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。
REPEATABLE READ —— 在第一次读取数据时生成一个ReadView
- 接下来我们看如果我们的事务隔离级别是REPEATABLE READ,会是怎么样的
# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1:Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'
# SELECT2:Transaction 100提交,Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'
- select 1 过程如下:
- 先 生成一个 readview ,m_ids:[100,200],min_trx_id:100,max_trx_id:201,creator_trx_id:0
- 然后跟最新列进行比对,发现 此纪录的事务100 在 m_ids 列表内,就跳过,接着访问下一条
- 如此就访问到 name = ‘刘备’ ,发现这个事务ID 符合要求,就返回这条记录
- select 2 过程如下:
- 因为当前事务的隔离级别为REPEATABLE READ,而之前在执行SELECT1时已经生成过ReadView了,所以此时直接复用之前的ReadView ,m_ids:[100,200],min_trx_id:100,max_trx_id:201,creator_trx_id:0
- 然后跟最新列进行比对,发现 此纪录 name 的内容是 ‘诸葛亮’ 的事务200 在 m_ids 列表内,就跳过,接着访问下一条
- 如此就访问到 name = ‘张飞’ ,发现这个事务ID 100 符合要求,在 m_ids 列表内,就跳过,接着访问下一条
- 如此就访问到 name = ‘刘备’ ,发现这个事务ID 符合要求,就返回这条记录
- 结果就是 select 1 和 select 2 访问得到的结果都是一样的
小结:
- 所谓的MVCC(Multi-Version Concurrency Control ,多版本并发控制)指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程,这样子可以使不同事务的读-写、写-读操作并发执行,从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是:生成ReadView的时机不同,READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView,而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView,之后的查询操作都重复使用这个ReadView就好了。