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', '[email protected]', 16, '212.117.129.58');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (2, 'Gaston', 'Rosenwald', '[email protected]', 1, '177.160.142.3');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (3, 'Onida', 'Beckey', '[email protected]', 100, '246.91.205.135');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (4, 'Pen', 'Schwant', '[email protected]', 70, '173.23.34.192');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (5, 'Amelina', 'Yousef', '[email protected]', 36, '236.121.250.36');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (6, 'Sallie', 'Gentner', '[email protected]', 21, '195.38.73.120');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (7, 'Julienne', 'Dobrovolski', '[email protected]', 74, '123.70.179.160');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (8, 'Idelle', 'O''Shiel', '[email protected]', 62, '16.85.248.74');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (9, 'Earle', 'Giacomazzo', '[email protected]', 49, '108.191.110.142');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (10, 'Celestyn', 'Wyrill', '[email protected]', 4, '82.232.65.146');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (11, 'Bryanty', 'Broadbridge', '[email protected]', 7, '117.70.48.113');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (12, 'Tybi', 'Pegden', '[email protected]', 96, '138.137.28.35');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (13, 'Wash', 'Leed', '[email protected]', 64, '21.106.123.29');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (14, 'Rog', 'Muncer', '[email protected]', 49, '161.38.63.134');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (15, 'Alec', 'Borleace', '[email protected]', 71, '152.192.32.148');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (16, 'Roberto', 'Seer', '[email protected]', 94, '238.104.254.189');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (17, 'Brittani', 'Ivers', '[email protected]', 62, '219.48.22.242');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (18, 'Jenna', 'Weekly', '[email protected]', 33, '0.94.96.82');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (19, 'Rog', 'Wabersich', '[email protected]', 53, '151.65.81.105');

insert into MOCK_DATA (id, first_name, last_name, email, ram_num, ip_address) values (20, 'Elihu', 'Trowsdall', '[email protected]', 72, '181.39.19.158');

第二节现象对比

Session1：

开启session1，执行一次select：

Consistent Nonlocking Reads,Locking Reads 和Phantom Rows

Session2：

开启session2，插入一条数据

Session 3：

再次执行select，同时再执行select…lock in share mode和select…for update

第三节 Consistent Nonlocking Reads:Select

3.1 加锁测试

3.1.1 REPEATABLE READ

Session2执行show engine innodb statusG:

如上可见，在REPEATABLE READ的隔离级别下mysql执行select这类操作的时候，是不加锁的，并且会生成一个read view来判断可见性

3.1.2 READ COMMITTED

如上可见，在READ COMMITTED的隔离级别下mysql执行select这类操作的时候，是不加锁的，不会生成一个read view来判断可见性

3.2 REPEATABLE READ测试

3.2.1 REPEATABLE READ

Session2:

Session1:

如上可见，在REPEATABLE READ的隔离级别下，可以实现REPEATABLE READ

3.2.2 READ COMMITTED

如上可见，在READ COMMITTED的隔离级别下，不能保证REPEATABLE READ

3.3 Phantom Rows测试

3.3.1 REPEATABLE READ

如上可见，在REPEATABLE READ的隔离级别下，可以避免出现简单的Phantom Rows

例外如下:

3.3.2 READ COMMITTED

如上可见，在READ COMMITTED的隔离级别下无法避免Phantom Rows

第四节 Locking Reads :SELECT ... LOCK IN SHARE MODE/FOR UPDATA

此处以测试lock in share mode为例

4.1加锁测试

4.1.1 REPEATABLE READ

如上可见，在REPEATABLE READ的隔离级别下mysql执行select…lock in share mode这类操作的时候，是加锁的,没有read view

4.1.2 READ COMMITTED

如上可见，在READ COMMITTED的隔离级别下mysql执行select…lock in share mode这类操作的时候，是加锁的，不会生成一个read view来判断可见性

4.2 REPEATABLE READ测试

4.2.1 REPEATABLE READ

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

如上可见，在READ COMMITTED的隔离级别下，一些情况下select…lock in shared mode（S lock）会阻塞住update（X lock）操作(同一个主键id)

4.3 Phantom Rows测试

4.3.1 REPEATABLE READ

如上可见，在REPEATABLE READ的隔离级别下, 一些情况下select…lock in shared mode（gap lock+S lock）会阻塞住insert（X lock）操作，防止幻读