作者：手辨
初衷是想用简单说下innodb的Transaction Isolation Levels和InnoDB Locking之间的关系，因为只写的话不容易理解，后来想以Consistent Nonlocking Reads,Locking Reads为突破点，用简单的例子来说明mysql常用的事务隔离级别(READ COMMITTED, REPEATABLE READ)和lock（record lock,gap lock,next-key lock, Insert Intention Lock）的关系，所以看起来更像是围绕mysql的三种select的测试，下面的测试多基于自建mysql进行
第一节 测试数据
create table MOCK_DATA (
id INT auto_increment,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
ram_num INT,
ip_address VARCHAR(20),
primary key(id)
);
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (1, 'Emelen', 'Jayme', 'ejayme0@adobe.com', 16, '212.117.129.58');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (2, 'Gaston', 'Rosenwald', 'grosenwald1@woothemes.com', 1, '177.160.142.3');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (3, 'Onida', 'Beckey', 'obeckey2@bluehost.com', 100, '246.91.205.135');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (4, 'Pen', 'Schwant', 'pschwant3@wikimedia.org', 70, '173.23.34.192');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (5, 'Amelina', 'Yousef', 'ayousef4@zimbio.com', 36, '236.121.250.36');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (6, 'Sallie', 'Gentner', 'sgentner5@bluehost.com', 21, '195.38.73.120');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (7, 'Julienne', 'Dobrovolski', 'jdobrovolski6@hostgator.com', 74, '123.70.179.160');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (8, 'Idelle', 'O''Shiel', 'ioshiel7@naver.com', 62, '16.85.248.74');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (9, 'Earle', 'Giacomazzo', 'egiacomazzo8@hibu.com', 49, '108.191.110.142');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (10, 'Celestyn', 'Wyrill', 'cwyrill9@prnewswire.com', 4, '82.232.65.146');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (11, 'Bryanty', 'Broadbridge', 'bbroadbridgea@nih.gov', 7, '117.70.48.113');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (12, 'Tybi', 'Pegden', 'tpegdenb@home.pl', 96, '138.137.28.35');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (13, 'Wash', 'Leed', 'wleedc@cnn.com', 64, '21.106.123.29');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (14, 'Rog', 'Muncer', 'rmuncerd@blogger.com', 49, '161.38.63.134');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (15, 'Alec', 'Borleace', 'aborleacee@ustream.tv', 71, '152.192.32.148');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (16, 'Roberto', 'Seer', 'rseerf@ocn.ne.jp', 94, '238.104.254.189');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (17, 'Brittani', 'Ivers', 'biversg@hubpages.com', 62, '219.48.22.242');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (18, 'Jenna', 'Weekly', 'jweeklyh@google.co.uk', 33, '0.94.96.82');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (19, 'Rog', 'Wabersich', 'rwabersichi@house.gov', 53, '151.65.81.105');
insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (20, 'Elihu', 'Trowsdall', 'etrowsdallj@123-reg.co.uk', 72, '181.39.19.158');
第二节 现象对比
Session1：
开启session1，执行一次select：
Session2：
开启session2，插入一条数据
Session 3：
再次执行select，同时再执行select…lock in share mode和select…for update
第三节 Consistent Nonlocking Reads:Select
3.1 加锁测试
3.1.1 REPEATABLE READ
Session1：
Session2执行show engine innodb statusG:
如上可见，在REPEATABLE READ的隔离级别下mysql执行select这类操作的时候，是不加锁的，并且会生成一个read view来判断可见性
3.1.2 READ COMMITTED
Session1：
Session2执行show engine innodb statusG:
如上可见，在READ COMMITTED的隔离级别下mysql执行select这类操作的时候，是不加锁的，不会生成一个read view来判断可见性
3.2 REPEATABLE READ测试
3.2.1 REPEATABLE READ
Session1：
Session2:
Session1:
如上可见，在REPEATABLE READ的隔离级别下，可以实现REPEATABLE READ
3.2.2 READ COMMITTED
Session1:
Session2:
Session1:
如上可见，在READ COMMITTED的隔离级别下，不能保证REPEATABLE READ
3.3 Phantom Rows测试
3.3.1 REPEATABLE READ
Session1:
Session2:
Session1:
如上可见，在REPEATABLE READ的隔离级别下，可以避免出现简单的Phantom Rows
例外如下:
3.3.2 READ COMMITTED
Session1：
Session2:
Session1:
如上可见，在READ COMMITTED的隔离级别下无法避免Phantom Rows
第四节 Locking Reads :SELECT ... LOCK IN SHARE MODE/FOR UPDATA
此处以测试lock in share mode为例
4.1加锁测试
4.1.1 REPEATABLE READ
Session1：
Session2执行show engine innodb statusG:
如上可见，在REPEATABLE READ的隔离级别下mysql执行select…lock in share mode这类操作的时候，是加锁的,没有read view
4.1.2 READ COMMITTED
Session1：
Session2执行show engine innodb statusG:
如上可见，在READ COMMITTED的隔离级别下mysql执行select…lock in share mode这类操作的时候，是加锁的，不会生成一个read view来判断可见性
4.2 REPEATABLE READ测试
4.2.1 REPEATABLE READ
Session1：
Session2:
Session3执行show engine innodb statusG:
如上可见，在REPEATABLE READ的隔离级别下，一些情况下select…lock in shared mode（S lock）会阻塞住update（X lock）操作(同一个主键id)，可以REPEATABLE READ
4.2.2 READ COMMITTED
Session1：
Session2:
Session3执行show engine innodb statusG:
如上可见，在READ COMMITTED的隔离级别下，一些情况下select…lock in shared mode（S lock）会阻塞住update（X lock）操作(同一个主键id)
4.3 Phantom Rows测试
4.3.1 REPEATABLE READ
Session1：
Session2:
Session3执行show engine innodb statusG:
如上可见，在REPEATABLE READ的隔离级别下, 一些情况下select…lock in shared mode（gap lock+S lock）会阻塞住insert（X lock）操作，防止幻读
4.3.2 READ COMMITTED
Session1：
Session2:
Session1:
如上可见，在READ COMMITTED的隔离级别下无法避免Phantom Rows
