Consistent Nonlocking Reads,Locking Reads 和Phantom Rows

2017-12-14 3130

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： 初衷是想用简单说下innodb的Transaction Isolation Levels和InnoDB Locking之间的关系，因为只写的话不容易理解，后来想以Consistent Nonlocking Reads,Locking Reads为突破点，用简单的例子来说明mysql常用的事务隔离级别.

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

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

68bba3a3de163299ebf37f9320b6a8c592da2172

Session2：

开启session2，插入一条数据

9cbdccdac5624793dbb9423520617d536db63ae4

Session 3：

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

721a96045d9039c9ea03b6481d09e9870addece7

第三节 Consistent Nonlocking Reads:Select

3.1 加锁测试

3.1.1 REPEATABLE READ

Session1：

a5e549e4b366622700c7fef704fe37573c590372

Session2执行show engine innodb status\G:

ac9a4281e3538582ce1fdb79901492e0f3018fda

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

3.1.2 READ COMMITTED

Session1：

236784955e220b87e165bbe7933fba756d8c97cc

Session2执行show engine innodb status\G:

c0c1db071df0e32dadd925915255ca64e973ae54

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

3.2 REPEATABLE READ测试

3.2.1 REPEATABLE READ

Session1：

e2663a17e46a0bf378e33e34da80d08ce0184b94

Session2:

f603259022ec993d287ec908f57abbcebefa028e

Session1:

6abd0ca5fa83d03f8e3e8dccece82721864c5b1e

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

3.2.2 READ COMMITTED

Session1:

3a98c1fe1e30aac45a1a6c7a01096b1a13014bae

Session2:

24926301eb10bf7974a958c8279e09f1ff256c87

Session1:

6c5ed45e8f7d9e8932ed1deec78c4913ec652a08

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

3.3 Phantom Rows测试

3.3.1 REPEATABLE READ

Session1:

2c9cb2eb2f0d74de08ccc3fca91933c660d9bd07

Session2:

3965840133e43deaf385bcc5f6e29b55e31757cd

Session1:

8031bfbfc60ec876efaf4a55d238f93c693db1e6

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

例外如下:

d1cef29fbc4b7bcc262eeedbd1742180fe068923

3.3.2 READ COMMITTED

Session1：

b87d589fc9a844fc304f78a8945479908e82345b

Session2:

cca5fbcdea0ec873335a7a22fdac37fa7fc0aae4

Session1:

8ac9f11c1fd0513e089c00b6dd1a761053123273

如上可见，在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：

f4466d7998add264834414d0a9905a52e7dec729

Session2执行show engine innodb status\G:

900841475ebef42e271feecbb9c4469a437a66f9

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

4.1.2 READ COMMITTED

Session1：

ca1270c5880f9f19e453cb251893f96d9da1cd0b

Session2执行show engine innodb status\G:

7cb3cf010993c34b46ae107c687f399b0cce4229

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

4.2 REPEATABLE READ测试

4.2.1 REPEATABLE READ

Session1：

b63057b4773a4b2d28224447edc5c93591c5b650

Session2:

aa7a8da6b3841b15600863a8df4d01bdc40cc570

Session3执行show engine innodb status\G:

1b561fc3fd03b8fca4603ff419ad83a842a64438

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

4.2.2 READ COMMITTED

Session1：

852a330ef4ea8e6e2c8f7783ac44fb4b04f333bf

Session2:

1b49def8118bc3e0254e44203657f33621b475c0

Session3执行show engine innodb status\G:

34fd940c6dc17101de5a4c6b758985a74f24a070

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

4.3 Phantom Rows测试

4.3.1 REPEATABLE READ

Session1：

597f6a4dc54d5e89a8140918cccdad081de1797c

Session2:

Session3执行show engine innodb status\G:

2207f8e8cf889481983428ab5f574209de9bfffc

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

4.3.2 READ COMMITTED

Session1：

081e69f041a6025de77287d28880783ea64d7f57

Session2:

cd24129585f36e3e09e8b05b3797c0299c7a8a46

Session1:

6cb83e939107c38db11ba0f9653c273fe07dd21c

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

Consistent Nonlocking Reads,Locking Reads 和Phantom Rows

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

第一节测试数据

第二节现象对比