mysql可重复读实验_Mysql可重复读测试

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锁理解一致，安心了)