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）操作，防止幻讀