MySQL InnoDB四個事務等級與髒讀、不重複讀、幻讀是什麼

1、MySQL InnoDB交易隔離等級髒讀、可重複讀取、幻讀

MySQL InnoDB交易的隔離等級有四級，預設為「可重複讀取」（REPEATABLE READ）。

·        1）.未提交讀取（READUNCOMMITTED）。另一個事務修改了數據，但尚未提交，而本事務中的SELECT會讀到這些未提交的數據（髒讀）( 隔離級別最低，並發性能高 )。

·        2）.提交讀取（READCOMMITTED）。本事務讀取到的是最新的資料（其他事務提交後的）。問題是，在同一筆事務裡，前後兩次相同的SELECT會讀到不同的結果（不重複閱讀）。會出現不可重複讀取、幻讀問題（鎖定正在讀取的行）

·        3）.可重複讀取（REPEATABLEREAD）。在同一筆交易裡，SELECT的結果是交易開始時時間點的狀態，因此，同樣的SELECT操作所讀到的結果會是一致的。但是，會有幻讀現象（稍後解釋）。會出幻讀（鎖定所讀取的所有行）。

·        4）.以串列化（SERIALIZABLE）。讀取操作會隱含取得共享鎖，可確保不同事務間的互斥（鎖表）。

‘

四個層級逐漸增強，每個層級解決一個問題。

·        1）.髒讀。另一個事務修改了數據，但尚未提交，而本事務中的SELECT會讀到這些未提交的數據。

·        2）.不重複閱讀。解決了髒讀後，會遇到，同一個事務執行過程中，另外一個事務提交了新數據，因此本事務先後兩次讀到的數據結果會不一致。

·        3）.幻讀。解決了不重複讀，保證了同一個事務裡，查詢的結果都是事務開始時的狀態（一致性）。但是，如果另一個事務同時提交了新數據，本事務再更新時，就會「驚奇的」發現了這些新數據，貌似之前讀到的數據是「鬼影」一樣的幻覺。

     具體地：

    1). 髒讀 

#      首先區分髒頁和髒資料

      髒頁是記憶體的緩衝池中已修改的page，未及時flush到硬碟，但已寫到redo log中。讀取和修改緩衝池的page很正常，可以提高效率，flush即可同步。髒資料是指交易對緩衝池中的行記錄record進行了修改，但還沒提交！ ！ ！ ，如果這時讀取緩衝池中未提交的行資料就叫臟讀，違反了事務的隔離性。髒讀就是指當一個事務正在存取數據，並且對數據進行了修改，而這種修改還沒有提交到資料庫中，這時，另外一個事務也訪問這個數據，然後使用了這個數據。

    2). 無法重複讀取 

     是指在一個事務內，多次讀取相同資料。在這個事務還沒結束時，另外一個事務也存取該相同資料。那麼，在第一個事務中的兩次讀取資料之間，由於第二個事務的修改，第二個事務已經提交。那麼第一個事務兩次讀到的的數據可能是不一樣的。這樣就發生了在一個事務內兩次讀到的資料是不一樣的，因此稱為是不可重複讀。例如，一個編輯人員兩次讀取同一文檔，但在兩次讀取之間，作者重寫了該文檔。當編輯人員第二次讀取文件時，文件已更改。原始讀取不可重複。如果只有在作者全部完成撰寫後編輯人員才可以讀取文檔，則可以避免該問題

    3). 幻讀:

     是指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的數據，這種修改是向表中插入一行新數據。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好像發生了幻覺一樣。例如，一個編輯人員更改作者提交的文檔，但當生產部門將其更改內容合併到該文檔的主副本時，發現作者已將未編輯的新資料新增至該文檔。如果在編輯人員和生產部門完成原始文件的處理之前，任何人都無法將新資料新增至文件中，則可以避免該問題。

2.隔離等級實驗

       下列實驗基於部落客MySQL Server 5.6

#

       先建立一個表，如下：

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

2.1、實驗一：解釋髒讀、重複讀取問題  

## 

#	交易A READ-UNCOMMITTED	事務B READ-COMMITTED ,	交易C-1 REPEATABLE-READ	#交易C-2 REPEATABLE-READ	#事務D SERIALIZABLE
set autocommit =0;			#
start transaction ;			#	start transaction;	#
insert into t(a)values( 4);
	select * from t; 1,2,3,4(髒讀：讀取到了未提交的事務中的資料)	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 (與上面的不在一個事務中，所以讀到為事務提交後最新的，所以可讀到4)	select * from t: 1,2,3（重複讀取：由於與上面的在一個事務中，所以只讀到事務開始事務的數據，也就是重複讀取）	select * from t: 1,2,3,4
#			# #	commit（提交事務，下面的是一個新的事務，所以可以閱讀事務提交以後的最新資料）
				select * from t: 1,2,3,4
READ-UNCOMMITTED 會產生髒讀，基本上很少適用於實際場景，所以基本上不使用。

#

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

#select * from t;# ## commit;

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

### ############ ##########select * from t:######1,2,3,4################################################################################### # #######select * from t:######1,2,3（重複讀：由於與上面的在一個事務中，所以只讀到事務開始事務的數據，也就是重複讀)############### ###### ##########commit（提交事務，下面的是新的事務，所以可以讀到事務提交以後的最新資料）############### ###### #########select * from t:#####1,2, 3,4##################REPEATABLE-READ可以確保一個事務中讀取的資料是可重複的，也就是相同的讀取（第一次讀取以後，即使其他事務已經提交新的數據，同一個事務中再次select也並不會被讀取）。 ######READ-COMMITTED只是確保讀取最新事務已經提交的資料。 ################

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。如下，

1. 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事务对其他的影响

事务A SERIALIZABLE	事务B READ-UNCOMMITTED	事务C READ-COMMITTED,	事务D REPEATABLE-READ	事务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 串行化执行，导致所有其他事务不得不等待事务A结束才行可以执行，这里特意使用了sleep函数，直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒，而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。
SERIALIZABLE是相當嚴格的串列化執行模式，不管是讀取或寫入，都會影響其他讀取相同的表的事務。是嚴格的表級讀寫排他鎖。也就失去了innodb引擎的優點。實際應用很少。

#

2.4、實驗四：幻讀

有些文章寫到InnoDB的可重複讀避免了「幻讀」（phantom read），這個說法並不準確。做個實驗：(以下所有試驗要注意儲存引擎和隔離等級)

1. TABLE `t_bitfly` (  

   `id` 
bigint

2. (20) NOT NULL default '0',  

   `value` 
varchar

3. (32) #default  NULL,  

PRIMARY

4. # KEY (`id`)  

   ) ENGINE=InnoDB 
#DEFAULT

5. # CHARSET=utf8;  

   #  

7. select @@global.tx_isolation, @@tx_isolation;  

8. ##+#------------------------+ -----------------+  

9. ##| @@

   global.tx_isolation | @@tx_isolation |  

##+
10. #------------------------+ -----------------+

      

    ##| 
可重複

11. -閱讀       | 可重複-閱讀 |  ##+

    #------------------------+ -----------------+
12.   

    #實驗4-1：

會話 A開始交易 ;空集合SELECT * FROM t_bitfly;

會話B	開始交易 ;
	SELECT * FROM t_bitfly;
##INSERT INTO t_bitfly VALUES (1, 'a');COMMIT;	|空集合
##
INSERT INTO t_bitfly VALUES (1, 'a'); |ERROR 1062 (23000): |Duplicate entry '1' for key 1 (剛剛明明告訴我沒有這條記錄的)	I

如此就出現了幻讀，以為表裡沒有數據，其實數據已經存在了，提交後，才發現資料衝突了。

實驗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     |###| +------+ -------+############ ################

本事務中第一次讀取出一行，做了一次更新後，另一個事務裡提交的資料就出現了。也可以看做是一種幻讀。

附說明

那麼，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」).

準備的理解是，當隔離等級是可重複讀取，且停用innodb_locks_unsafe_for_binlog的情況下，在搜尋和掃描index的時候使用的next-keylocks可以避免幻讀。

關鍵點在於，是InnoDB預設對一個普通的查詢也會加上next-key locks，還是說需要套用自己來加鎖呢？如果單看這一句，可能會以為InnoDB對普通的查詢也加了鎖，如果是，那和序列化（SERIALIZABLE）的差別又在哪裡呢？

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 dupate for 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 “lock” the nonexistence of something in your table.

##我的理解是

說，InnoDB提供了next-key locks，但需要應用程式自己加鎖。 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：

##

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

附說明：

MySQL manual裡對可重複讀取裡的鎖定的詳細解釋：

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  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   |值| | +------+--------+ | |    1 |a     | | +------+--------+

#  | +------+--------+| +------+--------+| |    1 |a     || +------+--------+| +----+--------+| |  1 |a     || |  2 |b     || +----+--------+| |  2 |b     || +----+--------+

#Session A
	# ##Session B##################start transaction ;############
##SELECT * FROM t_bitfly; | +------+--------+ | | id   |值| | +------+--------+ | |    1 |a     | | +------+--------+
	# 插入t_bitfly   值(2, 'b');
	#COMMIT;
##SELECT * FROM t_bitfly;	| | id   |值|
	SELECT * FROM t_bitfly鎖定共享模式; | +----+--------+ | |編號 |值|
| +----+--------+ ##  #SELECT * FROM t_bitfly FOR UPDATE;	| |編號 |值| | +----+--------+ | |  1 |a     |

###### ###################SELECT * FROM t_bitfly; # ##| +----+--------+###| |編號 |值|###| +----+--------+###| |  1 |a     |###| +----+--------+###########################

附說明：如果使用普通的讀，會得到一致性的結果，如果使用了加鎖的讀，就會讀到「最新的」「提交」讀的結果。

本身，可重複讀取和提交讀取是矛盾的。在同一個事務裡，如果保證了可重複讀，就會看不到其他事務的提交，違背了提交讀；如果保證了提交讀，就會導致前後兩次讀到的結果不一致，違背了可重複讀。

可以這麼講，InnoDB提供了這樣的機制，在預設的可重複讀取的隔離層級裡，可以使用加鎖讀取去查詢最新的資料。

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、總結

結論：MySQL InnoDB交易預設隔離等級是可重複讀取並不保證避免幻讀，需要應用使用加鎖讀取來保證。而這個加鎖度使用到的機制就是next-key locks。

以上是MySQL InnoDB四個事務等級與髒讀、不重複讀、幻讀是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！