Mysql加鎖過程詳解（2）-關於mysql 幻讀理解

Mysql加鎖過程詳解（1）-基本知識

Mysql加鎖過程詳解（2）-關於mysql 幻讀理解

Mysql加鎖過程詳解（3）-關於mysql 幻讀理解

Mysql加鎖過程詳解（4）-select for update/lock in share mode 對事務併發性影響

Mysql加鎖過程詳解（5）-innodb 多版本併發控制原理詳解

Mysql加鎖過程詳解（6）-資料庫隔離級別（1）

Mysql加鎖過程詳解（6）-資料庫隔離級別（2）-通過例子理解事務的4種隔離級別

Mysql加鎖過程詳解（7）-初步理解MySQL的gap鎖

Mysql加鎖過程詳解（8）-理解innodb的鎖(record,gap,Next-Key lock)

首先，mysql 幻讀並非是”一個事務內進行兩次相同操作居然得到了不一樣的結果”，因為它根本不可能發生在使用了 read view / MVCC 的 RR 隔離級別下，這種幻讀的定義更適合給 Oracle，Oracle 的事務隔離只有兩級，RC 和 Serializable。然後還有很多人辯解說不可重複讀是針對某條記錄的，幻讀是針對記錄集合的，這是在自我安慰麼？

這裡給出 mysql 幻讀的比較形象的場景：

users： id 主鍵

1、T1：select * from users where id = 1;

2、T2：insert into `users`(`id`, `name`) values (1, 'big cat');

3、T1：insert into `users`(`id`, `name`) values (1, 'big cat');

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

T2 ：干擾事務，目的在於擾亂 T1 的正常的事務執行。

在 RR 隔離級別下，1、2 是會正常執行的，3 則會報錯主鍵衝突，對於 T1 的業務來說是執行失敗的，這裡 T1 就是發生了幻讀，因為T1讀取的資料狀態並不能支援他的下一步的業務，見鬼了一樣。

在 Serializable 隔離級別下，1 執行時是會隱式的新增 gap 共享鎖的，從而 2 會被阻塞，3 會正常執行，對於 T1 來說業務是正確的，成功的扼殺了擾亂業務的T2，對於T1來說他讀取的狀態是可以拿來支援業務的。

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

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

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

下面例子版本

SELECT VERSION();

例子1，讀提交

a	b
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
1.不可重複讀
begin	begin
INSERT test VALUES(1,1);
SELECT * FROM test;	SELECT * FROM test;
commit
	SELECT * FROM test;

	COMMIT
B在一個事務的查詢的結果變了，不可重複讀
2.鎖
begin	begin
INSERT test VALUES(2,2);
	SELECT * FROM test;
	INSERT test VALUES(2,2);
	Lock wait timeout exceeded; try restarting transaction
COMMIT	COMMIT

	begin
	INSERT test VALUES(3,3);
	INSERT test VALUES(4,4);
	COMMIT

BEGIN	BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

COMMIT
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

例子2：重複讀

a	b
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET AUTOCOMMIT=0;
1．可重複讀
begin	begin
INSERT test VALUES(1,1);
SELECT * FROM test;	SELECT * FROM test;
commit
	SELECT * FROM test;

	COMMIT
	BEGIN
	SELECT * FROM test;

	COMMIT
B在一個事務的查詢的沒變
2鎖
begin	begin
INSERT test VALUES(2,2);
	SELECT * FROM test;
	INSERT test VALUES(2,2);
	Lock wait timeout exceeded; try restarting transaction
COMMIT	COMMIT
3(幻讀)
BEGIN	BEGIN
INSERT test VALUES(3,3);
SELECT * FROM test;	SELECT * FROM test;
COMMIT
	SELECT * FROM test;
	INSERT test VALUES(3,3);
	Duplicate entry '3' for key 'PRIMARY'
	COMMIT
	BEGIN
	SELECT * FROM test;
	COMMIT
幻讀，b明明查到沒有，插入時候提示主鍵衝突，剛剛查詢沒有，出現幻覺？


	begin
	INSERT test VALUES(4,4);
	COMMIT
4．可重複讀
BEGIN	BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

COMMIT
BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

COMMIT	COMMIT

網上很多說範圍啊，count等等都是不對的，不用於幻讀

Mysql加鎖過程詳解（2）-關於mysql 幻讀理解

相關文章