介绍了事务的ACID特性,MySQL各个隔离级别下的实现方式。
数据库中事务
事务的ACID
原子性(Atomicity)
定义:事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行
一致性(Consistency)
定义:事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束
隔离性(Isolation)
定义:多个事务并发执行时,一个事务的执行不应影响其他事务的执行
持久性(Durability)
定义:已被提交的事务对数据库的修改应该永久保存在数据库中
MySQL数据库InnoDB引擎中的实现
基于两阶段锁的多版本并发控制(Lock-Based MVCC)
快照读
select * from xxx where
当前读
update delete操作在MySQL内部实现上就是根据where条件读取记录,每条记录依次加锁更新。
insert简单理解有可能触发一次unique key检查,也存在一次读。
update ;
update table_a set column_a='a' where id=1; ---> select column_a from table_a where id=1
;
delete;
delete from table_a where id=1; --->. select column_a from table_a where id=1;
insert/replace;
insert into table_a values (…);
select … for update;
select * from table_a where id=1 for update
select … in share mode;
select * from table_a where id=1 in share mode
MVCC对每条记录都会记录版本,包括最新版本和之前修改的一些版本;对于快照读,读取的是本事务可见的版本,可能是最新版本,也可能是某个历史版本;对于当前读,读取的是最新版本,并且同时会对记录加锁,保证记录不会被其他事务修改。
两阶段锁
两阶段:加锁阶段和解锁阶段不重合
时间上:
事务A | 加锁/解锁 |
---|---|
start transaction; | |
update table_a set column_a=‘a’ where id=1; | 加锁 |
update table_a set column_a=‘a’ where id=2; | 加锁 |
select id from table_a where code = “A”; | |
commit; | 同时释放加过的锁 |
有例外,在read-committed隔离级别下会讲到
锁的类型
1.记录锁(Record Lock)
InnoDB里的行锁,锁的是索引
InnoDB一般是B+Tree索引:
**主键索引(Primary Key)/聚簇索引(Cluster Index)**叶节点记录了完整的行记录;通过主键索引,就可以获取记录所有的列。
select * from t_table where id=1
**辅助索引/二级索引(Secondary Index)**叶节点记录里主键的位置;通过辅助索引找到记录的列需要先通过辅助索引找到主键索引,再通过主键索引找到记录的列。
锁辅助索引的同时会锁住相关的主键
2.间隙锁(Gap Lock)
间隙:索引结构中能够插入新索引的空隙
间隙锁就是在索引前后加锁,但不包括索引本身。
作用:防止幻读
table_a
列 | 索引 | |
---|---|---|
id | PK | |
name | ||
status | Normal(非唯一索引) |
Id | name | status |
---|---|---|
1 | Name1 | 1 |
2 | Name2 | 2 |
3 | Name3 | 3 |
4 | Name4 | 4 |
update table_a set name=‘a’ where status>3;
(3,+∞)加入GAP锁,防止新纪录插入
3.Next Key Lock
记录锁+间隙锁
锁的模式
共享锁(S)锁,排他锁(X)锁
**共享锁:**共享锁的作用通常用于在事务中读取一条行记录后,不希望它被别的事务锁修改,但所有的读请求产生的LOCK_S锁是不冲突的。
常见场景:
1.select * … in share mode
2.外键检查
3.insert into 检查duplicate key时
**排他锁:**排他锁的作用主要是避免对同一条记录的并发修改。通常对于UPDATE或者DELETE操作,或者类似SELECT … FOR UPDATE操作,都会对记录加排他锁。
未提交读 Read-Uncommitted
实际不会使用,忽略
提交读 Read-Committed
t_test_01表结构
1 | CREATE TABLE `t_test_01` ( |
数据400W行,idx_status区分度也很高。
为了模拟两个并发的transaction,在naveicat之类的客户端中打开两个页面(执行的查询是两个session);同时为了模拟语句执行的先后次序,语句中有select sleep(10),表示当前执行语句的线程暂停10s。
不可重复读
实际语句:
1 | set tx_isolation='read-committed'; |
1 | set tx_isolation='read-committed'; |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start transaction; | |
2 | select * from t_test_01 where status=8; | |
3 | start transaction; | |
4 | update t_test_01 set name=“newName” where status=8; | |
5 | commit; | |
6 | select * from t_test_01 where status=8; | |
7 | commit; |
结果:
语句2和语句6得到的结果不一致,语句6读到的name是被transactionB修改的;同一个transaction相同语句读到的结果不一致。
RC级别下update … where 加锁后释放锁
t_test_01表结构
1 | CREATE TABLE `t_test_01` ( |
数据400W行,idx_status区分度也很高
code为唯一索引,存在code=‘9’,不存在code=‘9’ and version=1的情况
实际语句:
1 | #transactionA |
1 | #transactionB |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | start TRANSACTION; |
2 | select * from t_test_version where code=‘9’ and version=1 for update; | |
3 | select * from t_test_version where code=‘9’ for update; | |
4 | commit; | |
5 | commit; |
结果:
语句3中transactionB获取锁阻塞,说明select * from t_test_version where code='9' and version=1 for update;
未找到记录但是对code='9’的记录加锁了。
status为非唯一索引,存在status=‘9’,不存在status=‘9’ and version=1的情况
实际语句:
1 | set tx_isolation='read-committed'; |
1 | set tx_isolation='read-committed'; |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | start TRANSACTION; |
2 | select * from t_test_version where code=‘9’ and version=1 for update; | |
3 | select * from t_test_version where code=‘9’ for update; | |
4 | commit; | |
5 | commit; |
结果:
语句3中transactionB获取锁未阻塞,感觉上select * from t_test_version where status=9 and version=1 for update;
未对status=9的记录加锁一样,实际上MySQL对这种非唯一索引加锁后,如果发现不符合where条件会立即释放锁,这也是稍微违背两阶段加锁原则的地方。
可重复读 Repeatable-Read
无不可重复读
实际语句:
1 | set tx_isolation='repeatable-read'; |
1 | set tx_isolation='repeatable-read'; |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | |
2 | select * from t_test_01 where status=8; | |
3 | start TRANSACTION; | |
4 | update t_test_01 set name=“newName” where status=8; | |
5 | commit; | |
6 | select * from t_test_01 where status=8; |
结果:
语句2和语句6的执行结果一样,虽然TransactionB在中间修改了name。
快照读不存在幻读
实际语句:
1 | start TRANSACTION; |
1 | start transaction; |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | |
2 | select * from t_test_01 where status>=8 and status<=10; | |
3 | start TRANSACTION; | |
4 | insert into t_test_01(name, code, status) values(“name20000002”, 200000002, 9); | |
5 | commit; | |
6 | select * from t_test_01 where status>=8 and status<=10; | |
7 | commit; |
结果:
语句2和语句6读取的结果行数一样;MVCC读取快照,第一次读取之后,后面读取的版本都和第一次读的版本一致,只有在数据被本事务更新后会读取最新版本,所以快照读一致,不存在幻读。
当前读不存在幻读
实际语句:
1 | start TRANSACTION; |
1 | start transaction; |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | |
2 | select * from t_test_01 where status>=8 and status<=10 for update; | |
3 | start TRANSACTION; | |
4 | insert into t_test_01(name, code, status) values(“name20000003”, 200000003, 9); | |
5 | select * from t_test_01 where status>=8 and status<=10 for update; | |
6 | commit; | |
7 | commit; |
结果:
TransactionB中的语句4执行被阻塞了,TransactionA commit之后4才开始执行;通过加gap lock间隙锁保证当前读之间一致,不存在幻读。
RR级别中存在的幻读
场景1
实际语句:
1 | start TRANSACTION; |
1 | start transaction; |
执行顺序:
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | |
2 | select * from t_test_01 where status=9;#读到name=’“name9” | |
3 | start TRANSACTION; | |
4 | update t_test_01 set name=“newName9” where status=9; | |
5 | commit; | |
6 | select * from t_test_01 where status=9;#读到name=’“name9” | |
7 | select * from t_test_01 where status=9 for update;#读到name=’“name9” | |
8 | select * from t_test_01 where status=9;#读到name=’“name9” | |
9 | commit; |
结果:
TransactionA每次读到的数据都是name9,并没有受TransactionB影响。
场景2
实际语句:
1 | start TRANSACTION; |
1 | start transaction; |
执行顺序
No | TransactionA | TransactionB |
---|---|---|
1 | start TRANSACTION; | |
2 | select * from t_test_01 where status=9;#读到name=’“name9” | |
3 | start TRANSACTION; | |
4 | update t_test_01 set name=“newName9” where status=9; | |
5 | commit; | |
6 | select * from t_test_01 where status=9;#读到name=’“name9” | |
7 | update t_test_01 set code=concat(code,‘0’) where status=9; | |
8 | select * from t_test_01 where status=9;#读到name=’“newName90” | |
9 | commit; |
结果:
和场景1仅语句7有差别:场景1中select ** for update,场景2中 update **;两种场景中,结果不一样。场景2中语句8的结果受到了TransactionB的影响。
可以说RR级别中的幻读存在于快照读和当前读之间不一致,即:读到的数据和更新时数据不一致。
可序列化
不存在幻读
实际中也不会使用,类似于RR级别基础上对所有快照读加上 in share mode,读写会冲突
总结
MySQL实现的隔离级别和SQL规范中的略微不同,主要体现在RR级别下的幻读