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

参考链接：
[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