MySQL 解決幻讀的方法

MySQL 幻讀的詳解、執行個體及解決辦法

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(25) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

InnoDB 支援事務, MyISAM不支援事務;

事務T1

mysql> select @@global.tx_isolation, @@tx_isolation;

+-----------------------+-----------------+

| @@global.tx_isolation | @@tx_isolation |

+-----------------------+-----------------+

| REPEATABLE-READ | REPEATABLE-READ |

+-----------------------+-----------------+

1 row in set, 2 warnings (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id = 1;

Empty set (0.00 sec)

mysql> insert into users values(1, 'big_cat');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

這裡就出現了幻讀

mysql> select * from users where id = 1;

Empty set (0.00 sec)

解決方法: 使用" for update "行鎖解決

mysql> select * from users where id = 1 for update;

+----+---------+

| id | name |

+----+---------+

| 1 | big_cat |

+----+---------+

1 row in set (0.00 sec)

事務T2

mysql> select @@global.tx_isolation, @@tx_isolation;

+-----------------------+-----------------+

| @@global.tx_isolation | @@tx_isolation |

+-----------------------+-----------------+

| REPEATABLE-READ | REPEATABLE-READ |

+-----------------------+-----------------+

1 row in set, 2 warnings (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into users values(1, 'big_cat');

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

step1 T1: SELECT * FROM `users` WHERE `id` = 1;

step2 T2: INSERT INTO `users` VALUES (1, 'big cat');

step3 T1: INSERT INTO `users` VALUES (1, 'big cat');

step4 T1: SELECT * FROM `users` WHERE `id` = 1;

T1 ：主事務, 檢測表中是否有 id 為 1 的記錄, 沒有則插入, 這是我們期望的正常業務邏輯。

T2 ：幹擾事務, 目的在于擾亂 T1 的正常的事務執行。

在 RR 隔離級别下, step1、step2 是會正常執行的, step3 則會報錯主鍵沖突, 對于 T1 的業務來說是執行失敗的, 這裡 T1 就是發生了幻讀,

因為 T1 在 step1 中讀取的資料狀态并不能支撐後續的業務操作, T1："見鬼了，我剛才讀到的結果應該可以支援我這樣操作才對啊，為什麼現在不可以"。

T1 不敢相信的又執行了 step4, 發現和 setp1 讀取的結果是一樣的(RR下的 MMVC機制)。此時, 幻讀無疑已經發生, T1 無論讀取多少次, 都查不到 id = 1 的記錄, 但它的确無法插入這條他通過讀取來認定不存在的記錄

(此資料已被T2插入), 對于 T1 來說, 它幻讀了。

其實 RR 也是可以避免幻讀的, 通過對 select 操作手動加 行X鎖(SELECT ... FOR UPDATE 這也正是 SERIALIZABLE 隔離級别下會隐式為你做的事情), 同時還需要知道, 即便目前記錄不存在,

比如 id = 1 是不存在的, 目前事務也會獲得一把記錄鎖(因為InnoDB的行鎖鎖定的是索引, 故記錄實體存在與否沒關系, 存在就加 行X鎖, 不存在就加 next-key lock間隙X鎖), 其他事務則無法插入此索引的記錄, 故杜絕了幻讀。

在 SERIALIZABLE 隔離級别下, step1 執行時是會隐式的添加 行(X)鎖 / gap(X)鎖的, 進而 step2 會被阻塞, step3 會正常執行, 待 T1 送出後, T2 才能繼續執行(主鍵沖突執行失敗), 對于 T1 來說業務是正确的,

成功的阻塞扼殺了擾亂業務的T2, 對于T1來說他前期讀取的結果是可以支撐其後續業務的。

是以 mysql 的幻讀并非什麼讀取兩次傳回結果集不同, 而是事務在插入事先檢測不存在的記錄時, 驚奇的發現這些資料已經存在了, 之前的檢測讀擷取到的資料如同鬼影一般。

這裡要靈活的了解讀取的意思, 第一次select是讀取, 第二次的 insert 其實也屬于隐式的讀取, 隻不過是在 mysql 的機制中讀取的, 插入資料也是要先讀取一下有沒有主鍵沖突才能決定是否執行插入。

不可重複讀側重表達 讀-讀, 幻讀則是說 讀-寫, 用寫來證明讀的是鬼影。

方法一: RR級别下防止幻讀

RR級别下隻要對 SELECT 操作也手動加行(X)鎖即可類似 SERIALIZABLE 級别(它會對 SELECT 隐式加鎖), 即大家熟知的：

# 這裡需要用 X鎖， 用 LOCK IN SHARE MODE 拿到 S鎖 後我們沒辦法做 寫操作

SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的記錄存在則會被加行(X)鎖, 如果不存在, 則會加 next-lock key / gap 鎖(範圍行鎖), 即記錄存在與否, mysql 都會對記錄應該對應的索引加鎖, 其他事務是無法再獲得做操作的。

這裡我們就展示下 id = 1 的記錄不存在的場景, FOR UPDATE 也會對此 "記錄" 加鎖, 要明白, InnoDB 的行鎖(gap鎖是範圍行鎖，一樣的)鎖定的是記錄所對應的索引, 且聚簇索引同記錄是直接關系在一起的。

T1事務

set @@session.tx_isolation=2;

select @@tx_isolation;

select * from users;

begin;

//這裡使用行鎖解決

select id from users where id = 1 for update;

insert into users values(1, 'big_cat');

commit;

T2事務

set @@session.tx_isolation=2;

select @@tx_isolation;

begin;

inset into users values (1, 'big_cat');

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

commit;

id = 1 的記錄不存在, 開始執行事務：

step1: T1 查詢 id = 1 的記錄并對其加 X鎖

step2: T2 插入 id = 1 的記錄, 被阻塞

step3: T1 插入 id = 1 的記錄, 成功執行(T2 依然被阻塞中), T1 送出(T2 喚醒但主鍵沖突執行錯誤)

T1事務符合業務需求成功執行, T2幹擾T1失敗。

方法二: SERIALIZABLE級别杜絕幻讀

在此級别下, 我們便不需要對 SELECT 操作顯式加鎖, InnoDB會自動加鎖, 事務安全, 但性能很低

T1事務

set @@session.tx_isolation=3;

select @@tx_isolation;

begin;

select * from users where id = 2;

insert into users values(2, 'big_cat');

commit;

T2事務

set @@session.tx_isolation=3;

select @@tx_isolation;

begin;

inset into users values (2, 'big_cat');

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

commit;

step1: T1 查詢 id = 2 的記錄, InnoDB 會隐式的對齊加 X鎖

step2: T2 插入 id = 2 的記錄, 被阻塞

step3: T1 插入 id = 2 的記錄, 成功執行(T2 依然被阻塞中)

step4: T1 成功送出(T2 此時喚醒但主鍵沖突執行錯誤)

T1事務符合業務需求成功執行, T2幹擾T1失敗。

總結:

RR 級别作為 mysql 事務預設隔離級别, 是事務安全與性能的折中, 可能也符合二八定律(20%的事務存在幻讀的可能, 80%的事務沒有幻讀的風險), 我們在正确認識幻讀後, 便可以根據場景靈活的防止幻讀的發生。

SERIALIZABLE 級别則是悲觀的認為幻讀時刻都會發生, 故會自動的隐式的對事務所需資源加排它鎖, 其他事務通路此資源會被阻塞等待, 故事務是安全的, 但需要認真考慮性能。

InnoDB的行鎖鎖定的是索引, 而不是記錄本身, 這一點也需要有清晰的認識, 故某索引相同的記錄都會被加鎖, 會造成索引競争, 這就需要我們嚴格設計業務sql，盡可能的使用主鍵或唯一索引對記錄加鎖。

索引映射的記錄如果存在, 加行鎖, 如果不存在, 則會加 next-key lock / gap 鎖 / 間隙鎖, 故InnoDB可以實作事務對某記錄的預先占用, 如果記錄存在, 它就是本事務的,

如果記錄不存在, 那它也将是本是無的, 隻要本是無還在, 其他事務就别想占有它。