Consistent Nonlocking Reads,Locking Reads 和Phantom Rows

作者：手辨

实为吾之愚见,望诸君酌之!闻过则喜，与君共勉

初衷是想用简单说下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