Consistent Nonlocking Reads,Locking Reads 和Phantom Rows

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 初衷是想用简单说下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 modeS lock)会阻塞住updateX 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 modeS lock)会阻塞住updateX lock)操作(同一个主键id)

 

4.3 Phantom Rows测试

4.3.1 REPEATABLE READ

Session1:

597f6a4dc54d5e89a8140918cccdad081de1797c

Session2:

982cb1e28843a78d721045a09262f06dfeb97cb3

Session3执行show engine innodb status\G:

2207f8e8cf889481983428ab5f574209de9bfffc

如上可见,在REPEATABLE READ的隔离级别下, 一些情况下select…lock in shared modegap lock+S lock)会阻塞住insertX lock)操作,防止幻读

 

4.3.2 READ COMMITTED

Session1:

081e69f041a6025de77287d28880783ea64d7f57

Session2:

cd24129585f36e3e09e8b05b3797c0299c7a8a46

Session1:

6cb83e939107c38db11ba0f9653c273fe07dd21c

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

 

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
数据库管理 Ruby
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored环境:RAC 4节点、oracle 11.2.0.4、redhat 5.9 64bit 问题描述: 1.
1843 0
2.4 Scattering Reads
2.4 Scattering Reads
48 0
2.5 Gathering Writes
2.5 Gathering Writes
66 0
|
存储 缓存 分布式计算
译|Eventually Consistent
译|Eventually Consistent
55 0
|
关系型数据库 MySQL
Consistent Nonlocking Reads,Locking Reads 和Phantom Rows
以Consistent Nonlocking Reads,Locking Reads为突破点,用简单的例子来说明mysql常用的事务隔离级别(READ COMMITTED, REPEATABLE READ)和lock(record lock,gap lock,next-key lock, Insert Intention Lock)的关系
Consistent Nonlocking Reads,Locking Reads 和Phantom Rows
|
存储 关系型数据库 数据库
MVCC and VACUUM
这是Robert Haas写的一片关于mvcc,vacuum的文章,我觉得写的比较透彻而且通俗易懂,从一个比较高的角度阐述了什么是mvcc,为什么我们需要mvcc。
1759 0