mysql的InnoDB索引存储引擎在RR(REPEATABLE READ)隔离级别下读取的情况
测试建表
create table test_transaction
(
id
bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
int_f
int not null default 0 comment ‘字段1’,
char_f
varchar(50) not null default ” comment ‘字段2’,
time_f
TIMESTAMP not null default CURRENT_TIMESTAMP comment ‘字段3’,
PRIMARY KEY (id
),
unique key uqe_int
(int_f
),
KEY idx_char_time
(char_f
,time_f
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’测试建表’;
初始准备数据
insert into test_transaction
(int_f
, char_f
) value(520, ‘Charlotte’);
select * from test_transaction
;
Part1) 起因:昨晚和光同事的一次讨论
– 获取隔离级别,确定当前是可重复度级别
SELECT @@global.tx_isolation;
– 能不能插入?插入会不会挂起,事务A能不能读取到 –
事务A | 事务B |
---|---|
START TRANSACTION; | START TRANSACTION; |
select * from test_transaction ; | select * from test_transaction ; |
insert into test_transaction (int_f , char_f ) value(521, ‘xyb’); | |
select * from test_transaction ; | select * from test_transaction ; |
commit; | commit; |
事务A没能读到事务B新插入的数据,事务B也没有被挂起
– 能不能删除?删除会不会挂起,事务A能不能读取到 –
事务A | 事务B |
---|---|
START TRANSACTION; | START TRANSACTION; |
select * from test_transaction ; | select * from test_transaction ; |
delete from test_transaction where int_f=521; | |
select * from test_transaction ; | select * from test_transaction ; |
commit; | commit; |
事务A也还能读到事务B新删除的数据,事务B也没有被挂起
– 能不能修改?修改会不会挂起,事务A能不能读取到 –
事务A | 事务B |
---|---|
START TRANSACTION; | START TRANSACTION; |
select * from test_transaction ; | select * from test_transaction ; |
update test_transaction set char_f =’xyb0’ where id =1; | |
select * from test_transaction ; | select * from test_transaction ; |
commit; | commit; |
事务A没能读到事务B的更新,事务B也没有被挂起
现象:
不管事务A查什么范围,事务B不挂起,都能插入,能修改,能删除。(乍看很震惊!有悖于S锁和X锁的理解呀!?)
Part2)
在Mysql中的InnoDB引擎,采用MVCC(Multi-Version Concurrency Control)[2],读取时候有两种来源:
- 快照读 (snapshot read),读取的是记录的可见版本 (有可能是历史版本),不用加锁,例如 select * from table where ?;
- 当前读 (current read),读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录
- select * from table where ? lock in share mode; – 简单理解,相当于加了S锁 [3]
- select * from table where ? for update; – 简单理解,相当于加了X锁 [3]
- insert into table values (…);
- update table set ? where ?;
- delete from table where ?;
所以以前S锁和X锁的认知是在当前读所情况下,而快照读不存在任何锁。换select lock in share mode方式将刚刚的实验再来一次
– 能不能插入?插入会不会挂起,事务A能不能读取到 –
事务A | 事务B |
---|---|
START TRANSACTION; | START TRANSACTION; |
select * from test_transaction lock in share mode; | select * from test_transaction lock in share mode; |
insert into test_transaction (int_f , char_f ) value(521, ‘xyb’); | |
select * from test_transaction lock in share mode; | select * from test_transaction lock in share mode; |
commit; | commit; |
– 能不能删除?删除会不会挂起,事务A能不能读取到 –
事务A | 事务B |
---|---|
START TRANSACTION; | START TRANSACTION; |
select * from test_transaction lock in share mode; | select * from test_transaction lock in share mode; |
delete from test_transaction where int_f=521; | |
select * from test_transaction lock in share mode; | select * from test_transaction lock in share mode; |
commit; | commit; |
– 能不能修改?修改会不会挂起,事务A能不能读取到 –
事务A | 事务B |
---|---|
START TRANSACTION; | START TRANSACTION; |
select * from test_transaction lock in share mode; | select * from test_transaction lock in share mode; |
update test_transaction set char_f =’xyb0’ where id =1; | |
select * from test_transaction lock in share mode; | select * from test_transaction lock in share mode; |
commit; | commit; |
现象:
1、事务A没有commit时,事务B的查询语句能执行,但插入,修改,删除语句都会挂起
2、如果事务B先查询,然后先于事务A查询操作执行行插入,修改,删除,那么事务A读取时被挂起(和以前的S锁X锁理解一致,安心了)
参考链接:
[1]http://hedengcheng.com/?p=771#_Toc374698322
[2]http://www.phpddt.com/db/mysql-innodb-mvcc.html
[3]http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html