明輝手游網(wǎng)中心：是一個免費提供流行視頻軟件教程、在線學(xué)習(xí)分享的學(xué)習(xí)平臺！

文章發(fā)布

資訊首頁騰訊視頻愛奇藝暴風(fēng)影音酷狗音樂酷我音樂影音大全軟件下載軟件教程電腦系統(tǒng)下載

聊天工具辦公軟件殺毒教程系統(tǒng)工具圖形圖像電腦學(xué)習(xí)應(yīng)用軟件網(wǎng)絡(luò)軟件蘋果應(yīng)用注冊碼網(wǎng)站教程技術(shù)開發(fā)安卓教程其它教程

您當(dāng)前所在位置：網(wǎng)視手游站 -> 應(yīng)用軟件 -> MySQL InnoDB4個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是什么

MySQL InnoDB4個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是什么

發(fā)表時間：2023-07-19 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：

[摘要]1、MySQL InnoDB事務(wù)隔離級別臟讀、可重復(fù)讀、幻讀MySQL InnoDB事務(wù)的隔離級別有四級，默認(rèn)是“可重復(fù)讀”（REPEATABLE READ）。· 1）.未提...

1、MySQL InnoDB事務(wù)隔離級別臟讀、可重復(fù)讀、幻讀

MySQL InnoDB事務(wù)的隔離級別有四級，默認(rèn)是“可重復(fù)讀”（REPEATABLE READ）。

· 1）.未提交讀（READUNCOMMITTED）。另一個事務(wù)修改了數(shù)據(jù)，但尚未提交，而本事務(wù)中的SELECT會讀到這些未被提交的數(shù)據(jù)（臟讀）( 隔離級別最低，并發(fā)性能高 )。

· 2）.提交讀（READCOMMITTED）。本事務(wù)讀取到的是最新的數(shù)據(jù)（其他事務(wù)提交后的）。問題是，在同一個事務(wù)里，前后兩次相同的SELECT會讀到不同的結(jié)果（不重復(fù)讀）。會出現(xiàn)不可重復(fù)讀、幻讀問題（鎖定正在讀取的行）

· 3）.可重復(fù)讀（REPEATABLEREAD）。在同一個事務(wù)里，SELECT的結(jié)果是事務(wù)開始時時間點的狀態(tài)，因此，同樣的SELECT操作讀到的結(jié)果會是一致的。但是，會有幻讀現(xiàn)象（稍后解釋）。會出幻讀（鎖定所讀取的所有行）。

· 4）.串行化（SERIALIZABLE）。讀操作會隱式獲取共享鎖，可以保證不同事務(wù)間的互斥（鎖表）。

‘

四個級別逐漸增強(qiáng)，每個級別解決一個問題。

· 1）.臟讀。另一個事務(wù)修改了數(shù)據(jù)，但尚未提交，而本事務(wù)中的SELECT會讀到這些未被提交的數(shù)據(jù)。

· 2）.不重復(fù)讀。解決了臟讀后，會遇到，同一個事務(wù)執(zhí)行過程中，另外一個事務(wù)提交了新數(shù)據(jù)，因此本事務(wù)先后兩次讀到的數(shù)據(jù)結(jié)果會不一致。

· 3）.幻讀。解決了不重復(fù)讀，保證了同一個事務(wù)里，查詢的結(jié)果都是事務(wù)開始時的狀態(tài)（一致性）。但是，如果另一個事務(wù)同時提交了新數(shù)據(jù)，本事務(wù)再更新時，就會“驚奇的”發(fā)現(xiàn)了這些新數(shù)據(jù)，貌似之前讀到的數(shù)據(jù)是“鬼影”一樣的幻覺。

具體地：

1). 臟讀

首先區(qū)分臟頁和臟數(shù)據(jù)

臟頁是內(nèi)存的緩沖池中已經(jīng)修改的page，未及時flush到硬盤，但已經(jīng)寫到redo log中。讀取和修改緩沖池的page很正常，可以提高效率，flush即可同步。臟數(shù)據(jù)是指事務(wù)對緩沖池中的行記錄record進(jìn)行了修改，但是還沒提交�。�！，如果這時讀取緩沖池中未提交的行數(shù)據(jù)就叫臟讀，違反了事務(wù)的隔離性。臟讀就是指當(dāng)一個事務(wù)正在訪問數(shù)據(jù)，并且對數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時，另外一個事務(wù)也訪問這個數(shù)據(jù)，然后使用了這個數(shù)據(jù)。

2). 不可重復(fù)讀

是指在一個事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個事務(wù)還沒有結(jié)束時，另外一個事務(wù)也訪問該同一數(shù)據(jù)。那么，在第一個事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個事務(wù)的修改，第二個事務(wù)已經(jīng)提交。那么第一個事務(wù)兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。例如，一個編輯人員兩次讀取同一文檔，但在兩次讀取之間，作者重寫了該文檔。當(dāng)編輯人員第二次讀取文檔時，文檔已更改。原始讀取不可重復(fù)。如果只有在作者全部完成編寫后編輯人員才可以讀取文檔，則可以避免該問題

3). 幻讀 :

是指當(dāng)事務(wù)不是獨立執(zhí)行時發(fā)生的一種現(xiàn)象，例如第一個事務(wù)對一個表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時，第二個事務(wù)也修改這個表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，以后就會發(fā)生操作第一個事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。例如，一個編輯人員更改作者提交的文檔，但當(dāng)生產(chǎn)部門將其更改內(nèi)容合并到該文檔的主復(fù)本時，發(fā)現(xiàn)作者已將未編輯的新材料添加到該文檔中。如果在編輯人員和生產(chǎn)部門完成對原始文檔的處理之前，任何人都不能將新材料添加到文檔中，則可以避免該問題。

2、隔離級別實驗

以下實驗基于博主MySQL Server 5.6

首先創(chuàng)建一個表，如下：

USE test;  
CREATE TABLE `t` (  
  
  `a` int(11) NOT NULL PRIMARY KEY  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1、實驗一：解釋臟讀、可重復(fù)讀問題

	事務(wù)A READ-UNCOMMITTED	事務(wù)B READ-COMMITTED,	事務(wù)C-1 REPEATABLE-READ	事務(wù)C-2 REPEATABLE-READ	事務(wù)D SERIALIZABLE
set autocommit =0;
start transaction ;				start transaction;
insert into t(a)values(4);
	select * from t; 1,2,3,4(臟讀：讀取到了未提交的事務(wù)中的數(shù)據(jù))	select * from t; 1,2,3（解決臟讀）	select * from t; 1,2,3	select * from t; 1,2,3	select * from t; 1,2,3


commit;
	select * from t: 1,2,3,4	select * from t: 1,2,3,4	select * from t: 1,2,3,4 (與上面的不在一個事務(wù)中，所以讀到為事務(wù)提交后最新的，所以可讀到4)	select * from t: 1,2,3（重復(fù)讀：由于與上面的在一個事務(wù)中，所以只讀到事務(wù)開始事務(wù)的數(shù)據(jù)，也就是重復(fù)讀）	select * from t: 1,2,3,4
				commit（提交事務(wù)，下面的就是一個新的事務(wù)，所以可以讀到事務(wù)提交以后的最新數(shù)據(jù)）
				select * from t: 1,2,3,4
READ-UNCOMMITTED 會產(chǎn)生臟讀，基本很少適用于實際場景，所以基本不使用。

2.2、實驗二：測試READ-COMMITTED與REPEATABLE-READ

事務(wù)A	事務(wù)B READ-COMMITTED	事務(wù)C REPEATABLE-READ
set autocommit =0;
start transaction ;	start transaction;	start transaction;
insert into t(a)values(4);
	select * from t; 1,2,3	select * from t; 1,2,3


commit;
	select * from t: 1,2,3,4	select * from t: 1,2,3（重復(fù)讀：由于與上面的在一個事務(wù)中，所以只讀到事務(wù)開始事務(wù)的數(shù)據(jù)，也就是重復(fù)讀）
		commit（提交事務(wù)，下面的就是一個新的事務(wù)，所以可以讀到事務(wù)提交以后的最新數(shù)據(jù)）
		select * from t: 1,2,3,4
REPEATABLE-READ可以確保一個事務(wù)中讀取的數(shù)據(jù)是可重復(fù)的，也就是相同的讀�。ǖ谝淮巫x取以后，即使其他事務(wù)已經(jīng)提交新的數(shù)據(jù)，同一個事務(wù)中再次select也并不會被讀取）。 READ-COMMITTED只是確保讀取最新事務(wù)已經(jīng)提交的數(shù)據(jù)。

當(dāng)然數(shù)據(jù)的可見性都是對不同事務(wù)來說的，同一個事務(wù)，都是可以讀到此事務(wù)中最新數(shù)據(jù)的。如下，

start transaction;  
insert into t(a)values(4);  
select *from t;    
1,2,3,4;  
insert into t(a)values(5);  
select *from t;  
1,2,3,4,5;

2.3、實驗三：測試SERIALIZABLE事務(wù)對其他的影響

事務(wù)A SERIALIZABLE	事務(wù)B READ-UNCOMMITTED	事務(wù)C READ-COMMITTED,	事務(wù)D REPEATABLE-READ	事務(wù)E SERIALIZABLE
set autocommit =0;
start transaction ;			start transaction;
select a from t union all select sleep(1000) from dual;
	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	SERIALIZABLE 串行化執(zhí)行，導(dǎo)致所有其他事務(wù)不得不等待事務(wù)A結(jié)束才行可以執(zhí)行，這里特意使用了sleep函數(shù)，直接導(dǎo)致事務(wù)B,C,D,E等待事務(wù)A持有釋放的鎖。由于我sleep了1000秒，而innodb_lock_wait_timeout為120s。所以120s到了就報錯HY000錯誤。
SERIALIZABLE是相當(dāng)嚴(yán)格的串行化執(zhí)行模式，不管是讀還是寫，都會影響其他讀取相同的表的事務(wù)。是嚴(yán)格的表級讀寫排他鎖。也就失去了innodb引擎的優(yōu)點。實際應(yīng)用很少。

2.4、實驗四：幻讀

一些文章寫到InnoDB的可重復(fù)讀避免了“幻讀”（phantom read），這個說法并不準(zhǔn)確。做個實驗：(以下所有試驗要注意存儲引擎和隔離級別)

CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
@@global.tx_isolation @@tx_isolation
+-----------------------+-----------------+
REPEATABLE-READ REPEATABLE-READ
+-----------------------+-----------------+

實驗4-1：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly; empty set
	INSERT INTO t_bitfly VALUES (1, 'a');COMMIT;
SELECT * FROM t_bitfly; empty set
INSERT INTO t_bitfly VALUES (1, 'a'); ERROR 1062 (23000): Duplicate entry '1' for key 1 (剛剛明明告訴我沒有這條記錄的)	I

如此就出現(xiàn)了幻讀，以為表里沒有數(shù)據(jù)，其實數(shù)據(jù)已經(jīng)存在了，提交后，才發(fā)現(xiàn)數(shù)據(jù)沖突了。

實驗4-2：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
	INSERT INTO t_bitfly VALUES (2, 'b');
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
UPDATE t_bitfly SET value='z'; Rows matched: 2 Changed:2 Warnings: 0 (怎么多出來一行)
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 z 2 z +------+-------+

本事務(wù)中第一次讀取出一行，做了一次更新后，另一個事務(wù)里提交的數(shù)據(jù)就出現(xiàn)了。也可以看做是一種幻讀。

附說明

那么，InnoDB指出的可以避免幻讀是怎么回事呢？

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

By default, InnoDB operatesin REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.6.8.5, “Avoidingthe Phantom Problem Using Next-Key Locking”).

準(zhǔn)備的理解是，當(dāng)隔離級別是可重復(fù)讀，且禁用innodb_locks_unsafe_for_binlog的情況下，在搜索和掃描index的時候使用的next-keylocks可以避免幻讀。

關(guān)鍵點在于，是InnoDB默認(rèn)對一個普通的查詢也會加next-key locks，還是說需要應(yīng)用自己來加鎖呢？如果單看這一句，可能會以為InnoDB對普通的查詢也加了鎖，如果是，那和序列化（SERIALIZABLE）的區(qū)別又在哪里呢？

MySQL manual里還有一段：

13.2.8.5. Avoiding the PhantomProblem Using Next-Key Locking (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html)

Toprevent phantoms, InnoDB usesan algorithm called next-key locking that combinesindex-row locking with gap locking.

Youcan use next-key locking to implement a uniqueness check in your application:If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the success or of your row during the read prevents anyone mean while inserting a duplicate for your row. Thus, the next-key locking enables you to “l(fā)ock” the nonexistence of something in your table.

我的理解是說，InnoDB提供了next-key locks，但需要應(yīng)用程序自己去加鎖。manual里提供一個例子：

SELECT * FROM child WHERE id> 100 FOR UPDATE;

這樣，InnoDB會給id大于100的行（假如child表里有一行id為102），以及100-102，102+的gap都加上鎖。

可以使用show engine innodb status來查看是否給表加上了鎖。

再看一個實驗，要注意，表t_bitfly里的id為主鍵字段。

實驗4-3：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly WHERE id<=1 FOR UPDATE; +------+-------+ id value +------+-------+ 1 a +------+-------+
	INSERT INTO t_bitfly VALUES (2, 'b'); Query OK, 1 row affected
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
	INSERT INTO t_bitfly VALUES (0, '0'); (waiting for lock ... then timeout) ERROR 1205 (HY000):Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+

可以看到，用id<=1加的鎖，只鎖住了id<=1的范圍，可以成功添加id為2的記錄，添加id為0的記錄時就會等待鎖的釋放。

附說明：

MySQL manual里對可重復(fù)讀里的鎖的詳細(xì)解釋：

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record)locks to block insertions by other sessions into the gaps covered by the range.

一致性讀和提交讀，先看實驗，

實驗4-4：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
	INSERT INTO t_bitfly VALUES (2, 'b');
	COMMIT;
SELECT * FROM t_bitfly; +------+-------+ id value +------+-------+ 1 a +------+-------+
SELECT * FROM t_bitfly LOCK IN SHARE MODE; +----+-------+ id value +----+-------+ 1 a 2 b +----+-------+
SELECT * FROM t_bitfly FOR UPDATE; +----+-------+ id value +----+-------+ 1 a 2 b +----+-------+
SELECT * FROM t_bitfly; +----+-------+ id value +----+-------+ 1 a +----+-------+

附說明：如果使用普通的讀，會得到一致性的結(jié)果，如果使用了加鎖的讀，就會讀到“最新的”“提交”讀的結(jié)果。

本身，可重復(fù)讀和提交讀是矛盾的。在同一個事務(wù)里，如果保證了可重復(fù)讀，就會看不到其他事務(wù)的提交，違背了提交讀；如果保證了提交讀，就會導(dǎo)致前后兩次讀到的結(jié)果不一致，違背了可重復(fù)讀。

可以這么講，InnoDB提供了這樣的機(jī)制，在默認(rèn)的可重復(fù)讀的隔離級別里，可以使用加鎖讀去查詢最新的數(shù)據(jù)。

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

If you want to see the “freshest” state of the database, you should use either theREAD COMMITTED isolation level or a locking read:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

3、總結(jié)

結(jié)論：MySQL InnoDB事務(wù)默認(rèn)隔離級別是可重復(fù)讀并不保證避免幻讀，需要應(yīng)用使用加鎖讀來保證。而這個加鎖度使用到的機(jī)制就是next-key locks。

以上就是MySQL InnoDB四個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是什么的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！

學(xué)習(xí)教程快速掌握從入門到精通的SQL知識。

標(biāo)簽：MySQL InnoDB4個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是啥

上一篇：怎么使用delphi將Clientdataset的Delta保存到數(shù)據(jù)庫中

下一篇：MySQL慢查詢?nèi)罩镜脑O(shè)置以及使用圖文詳細(xì)教程詳細(xì)說明

MySQL InnoDB4個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是什么

1、MySQL InnoDB事務(wù)隔離級別臟讀、可重復(fù)讀、幻讀

2、隔離級別實驗

2.1、實驗一：解釋臟讀、可重復(fù)讀問題

2.2、實驗二：測試READ-COMMITTED與REPEATABLE-READ

2.3、實驗三：測試SERIALIZABLE事務(wù)對其他的影響

2.4、實驗四：幻讀

3、總結(jié)

推薦資訊總?cè)藲獍?/span>

最新教程本月人氣

相關(guān)文章

MySQL InnoDB4個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是什么

1、MySQL InnoDB事務(wù)隔離級別臟讀、可重復(fù)讀、幻讀

2、隔離級別實驗

2.1、實驗一：解釋臟讀、可重復(fù)讀問題

2.2、實驗二：測試READ-COMMITTED與REPEATABLE-READ

2.3、實驗三：測試SERIALIZABLE事務(wù)對其他的影響

2.4、實驗四：幻讀

3、總結(jié)

推薦資訊 總?cè)藲獍?/span>

最新教程 本月人氣

相關(guān)文章

MySQL InnoDB4個事務(wù)級別與臟讀、不重復(fù)讀、幻讀是什么

1、MySQL InnoDB事務(wù)隔離級別臟讀、可重復(fù)讀、幻讀

2.1、實驗一：解釋臟讀、可重復(fù)讀問題

2.2、實驗二：測試READ-COMMITTED與REPEATABLE-READ

2.4、實驗四：幻讀

3、總結(jié)

推薦資訊總?cè)藲獍?/span>

最新教程本月人氣