MySQL事务隔离及原理
一、 事务(Transaction)及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性
- 原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
- 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(包括B树索引或双向链表)也都必须是正确的。
- 隔离性(Isolation):数据库系统提供了一定的隔离机制,保证事务在不受外部并发影响的“独立”环境执行,这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
- 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,及时出现系统故障也能够保持。
二、 并发事务处理带来的问题
-
脏读(Dirty Reads):读取了其他并发事务未提交的数据
-
不可重复读(Non-Repeatable Reads):读取了其他并发事务提交的数据,针对update和delete
-
幻读(Phanton Reads):读取了其他并发事务提交的数据,针对insert
三、事务隔离级别
MySQL的事务隔离级别为可重复读,Oracle为读已提交
隔离级别越好,并发性越差
相关示例
对于不同的隔离级别,V1,V2,V3的值为多少
读未提交,V1 = 20 , V2 = 20 , V3 = 20
读已提交,V1 = 18 , V2 = 20 , V3 = 20
可重复读,V1 = 18 , V2 = 18 , V3 = 20
可序列化,V1 = 18 , V2 = 18 , V3 = 20
四、MySQL数据库如何实现事务隔离级别(RR)
-
LBCC (lock-Based Concurrent Control)
-
MVCC (Multi-Version Concurrent Control)(针对LBCC的延伸)
MySQL两者公用
4.1 InnoDB Locking
下面的锁是针对一个数据不同纬度的划分,有重叠部分
- Shared and Exclusive Locks 乐观锁、互斥锁
- Intention Locks 意向锁
- Record Locks 行锁
- Gap Locks 间隙锁
- Next -Key Locks Record Locks + Gap Locks
- Insert Intention Locks 插入意向锁
- AUTO-INC Locks自增锁
- Predicate Locks for Spatial Indexes 空间索引预测锁
MyISAM 支持表锁
Innodb 支持表锁、行锁
4.2 MySQL InnoDB 基于锁的隔离级别方式
4.2.1 Lock Mode锁的模式
4.2.1.1 共享锁(行锁)-- S
hared Locks
又名读锁,对某一资源加共享锁,自身可以读该资源,其他人也可以读该资源(也可以在继续加共享锁,即共享锁可多个共存),但无法修改。要想修改就必须等所有共享锁都释放完之后才能进行。
加锁 lock in share mode
select * from table lock in share mode;
释放锁:
commit;
rollback;
加读锁的意义是防止并发事务去修改该数据
4.2.1.2 排他锁(行锁)-- Ex
clusive Locks
对某一资源加排它锁,自身可以进行增删改查,其他人无法进行任何操作
注意:排它锁不能与其他锁并存
加锁:
-
自动:DML语句默认会加排它锁
-
手动:
select * from user where id = 1 for update;
释放锁:
commit;
rollback;
4.2.1.3 意向锁 --Intention Locks
意向共享锁 --Intention Shared Locks
表示事务准备给数据行加共享锁之前 ——数据行加共享锁的前提是获取此表的IS锁
意向排它锁 --Intention Exclusive Locks
表示事务准备给数据行加排他锁之前 ——数据行加排他锁的前提是获取此表的IX锁
注意:均为表锁,无法手动创建
意向锁不是用来锁定数据的,而是用来查看有没有已经锁定的数据,类似一个信号灯
为什么要使用意向锁
用来帮我们快速的判断当前表里面有没有已经锁定的数据,帮助我们在锁表操作的时候,能够进行快速的判断。
举个例子:比如你想给每个员工都涨薪资,你首先得先对这张表进行锁定,再去修改数据。由于MySQL是行锁,你要给表加锁首先得确定该表中的每一行都没有锁,与其这样一行一行遍历判断该表有没有锁,不如再加行锁之前就加一个意向锁,需要对表加锁的时候直接判断该表是否有意向锁即可。
4.2.2 锁的算法
锁的区间
注意:锁的区间是根据数据库里的记录来进行划分的
测试数据
DROP TABLE IF EXISTS `test_transaction`;
CREATE TABLE `test_transaction` (
`id` int NOT NULL,
`num` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test_transaction` VALUES (1, 1);
INSERT INTO `test_transaction` VALUES (5, 5);
INSERT INTO `test_transaction` VALUES (9, 9);
INSERT INTO `test_transaction` VALUES (11, 11);
4.2.2.1 Record Locks 记录锁
select * from table where id = 1 for update;
该语句锁定本条id = 1的记录
测试
开启两个事务,一个事务对id=1行的数据加锁,另一个事务更新改行数据
/*事务1*/
begin;
select * from test_transaction where id = 1 for update;
/*事务2*/
begin;
update test_transaction set num = 11 where id = 1;
可以看到另一个事务无法更新改行数据,被阻塞
4.2.2.2 Gap Locks 间隙锁
注意Gap Locks 只存在于RR隔离级别
select * from table where id > 5 and id < 9 for update;
会锁定(5,9)的开区间
测试
开启两个事务,一个事务对id > 5 and id < 9的数据加锁,另一个事务在该范围内插入数据
/*事务1*/
begin;
select * from test_transaction where id > 5 and id < 9 for update;
/*事务2*/
begin;
insert into test_transaction values(6,6);
可以看到事务2无法插入数据,被阻塞
思考下面语句会锁定哪里
select * from table where id > 15 for update;
测试
/*事务1*/
begin;
select * from test_transaction where id > 15 for update;
/*事务2*/
begin;
insert into test_transaction values(20,20);
insert into test_transaction values(13,13);
update test_transaction set num = 111 where id = 11;
通过上述测试结果可知,id为20和13都无法插入,可修改11
从而可以验证一个结论锁区间是根据数据库里的记录来划分的,由于数据库中的数据为1,5,9,11,所以id>15实际上锁定的是id > 11
4.2.2.3 Next -Key Locks --临建锁
select * from table where id > 5 and id < 11 for update
会锁定(5,9],(9,11]
测试
/*事务1*/
begin;
select * from test_transaction where id > 15 for update;
/*事务2*/
begin;
insert into test_transaction values(6,6);
insert into test_transaction values(8,8);
通过上述测试结果可知,id为6和8都无法插入
4.2.2.4 其他锁
-
插入意向锁 Insert Intention Locks
-
自增锁 AUTO-INC Locks
-
空间锁 Predicate Locks for Spatial Indexes (基于R-Tree)
4.3 MVCC
在MySQL InnoDB存储引擎下RC,RR基于MVCC(多版本并发控制)进行并发事务控制。MVCC是基于“数据版本”对并发事务进行访问,指的是 “维持一个数据的多个版本,使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念。用来解决读-写冲突
的无锁并发控制。它实现有读写冲突时,做到不加锁,也能进行非阻塞并发读。MVCC是行锁的一个变种,但它在很多情况下,避免了加锁操作,降低了开销
实现原理
它的实现原理主要是依赖记录中的 3个隐式字段
,undo日志
,Read View
来实现的。
隐式字段
每行记录除了我们自定义的字段外,还有数据库隐式定义的DB_TRX_ID
,DB_ROLL_PTR
,DB_ROW_ID
等字段
DB_TRX_ID
6byte,最近修改(修改/插入
)事务ID:记录创建这条记录/最后一次修改该记录的事务IDDB_ROLL_PTR
7byte,回滚指针,指向这条记录的上一个版本(存储于rollback segment里)DB_ROW_ID
6byte,隐含的自增ID(隐藏主键),如果数据表没有主键,InnoDB会自动以DB_ROW_ID
产生一个聚簇索引- 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除,而是删除flag变了
undo日志
undo log主要分为两种:
- insert undo log
代表事务在insert
新记录时产生的undo log
, 只在事务回滚时需要,并且在事务提交后可以被立即丢弃 - update undo log
事务在进行update
或delete
时产生的undo log
; 不仅在事务回滚时需要,在快照读时也需要;所以不能随便删除,只有在快速读或事务回滚不涉及该日志时,对应的日志才会被purge
线程统一清除
purge
- 从前面的分析可以看出,为了实现InnoDB的MVCC机制,更新或者删除操作都只是设置一下老记录的deleted_bit,并不真正将过时的记录删除。
- 为了节省磁盘空间,InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作,purge线程自己也维护了一个read view(这个read view相当于系统中最老活跃事务的read view);如果某个记录的deleted_bit为true,并且DB_TRX_ID相对于purge线程的read view可见,那么这条记录一定是可以被安全清除的。
MVCC控制方案的前提是基于UNDO_LOG版本链
UNDO_LOG版本链不是立即删除,MySQL确保版本链数据不再被引用后再进行删除
ReadView:
“快照读”SQL执行时MVCC提取数据的依据,主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候,对该记录创建一个Read View
读视图,把它比作条件用来判断当前事务能够看到哪个版本的数据,既可能是当前最新的数据,也有可能是该行记录的undo log
里面的某个版本的数据。
快照读(使用MVCC)就是最普通的Select查询SQL语句,快照读的前提是隔离级别不是串行级别,串行级别下的快照读会退化成当前读,快照读的实现是基于多版本并发控制,即MVCC。既然是基于多版本,即快照读可能读到的并不一定是数据的最新版本,而有可能是之前的历史版本
当前读(Next Key locks:行锁+间隙锁)指代执行下列语句时进行数据读取的方式 insert
、update
、delete
、select…for update
、select …lock in share mode
,为什么叫当前读?就是它读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁
ReadView是一个数据结构,包含4个字段
字段 | 含义 |
---|---|
m_ids | 当前活跃的事务编号集合 |
min_trx_id(up_limit_id) | 最小活跃事务编号 |
max_trx_id(low_limit_id) | 预分配事务编号,当前最大事务编号+1 |
creator_trx_id | ReadView创建者的事务编号 |
版本链数据访问规则:
-
判断当前事务id(trx_id)等于creator_trx_id?成立说明数据就是自己这个事务更改的,可以访问,否则进行下面的操作
-
判断trx_id < min_trx_id ? 成立说明数据已经提交,可以访问。否则进行下面的操作
-
判断trx_id >= max_trx_id ? 成立说明该事务实在ReadView生成之后才开启,不允许访问
-
判断min_trx_id <= trx_id < max_trx_id ,成立在m_ids数据中对比,不存在则代表数据已提交,可以访问,否则进行下面的操作
-
读取UNDO_LOG版本链的之前版本数据进行比对,重复上述操作直到读取数据。
读已提交(RC):在每一次执行快照读时生成ReadView
可重复读(RR):仅在第一次执行快照读时生成ReadView,后续快照读复用。(可能有例外)
连续多次快照读,ReadView会产生复用,没有问题
特例:当两次快照读之间存在当前读,ReadView会重新生成,导致产生幻读
案例 RR级别下
/*事务1*/
Begin;
Select num from test_transaction where id = 1;
Select num from test_transaction where id = 1;
Commit;
/*事务2*/
Begin;
Select num from test_transaction where id = 1;
update test_transaction set num = Num + 1 where id = 1;
Select num from test_transaction where id = 1;
Commit;
/*事务3*/
update test_transaction set num = Num + 1 where id = 1;
操作1(事务A)
操作2(事务B)
操作3(事务C)
操作4(事务B)
操作5(事务A)
综上
我们可以看到Q1=3,Q2=1
解析
为什么Q1=3
首先进行update操作,应为当前读,读取最新版本数据也就是事务C新提交的数据,此时num = 2,加1之后变为3。之后select进行快照读,ReadView如下图所示,UNDO_LOG版本链为上图所示。首先读取最新版本1001的,发现等于creator_trx_id直接读取该数据为3.
为什么Q2=1
select进行快照读,ReadView如下图所示,UNDO_LOG版本链为上图所示。首先读取最新版本1001的,发现在up_limit_id和low_limit_id之间,同时m_ids存在该版本,所以该数据不能读。接着读取版本1002,发现大于等于low_limit_id,说明该事务实在ReadView生成之后才开启,不允许访问。然后读取版本999,该版本小于up_limit_id,可访问,所以读取该数据num = 1