MySQL InnoDB四個事務級別與臟讀、不重復讀、幻讀是什么

2515

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

事務A

事務B READ-COMMITTED

事務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（重復讀：由于與上面的在一個事務中，所以只讀到事務開始事務的數據，也就是重復讀）
? ?

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. create?table?`t_bitfly`?(??

  2. `id`?bigint(20)?NOT?NULL?default?‘0’,??

  3. `value`?varchar(32)?default?NULL,??

  4. PRIMARY?KEY?(`id`)??

  5. )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;??

  6. ??

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

  8. +———————–+—————–+??

  9. |?@@global.tx_isolation?|?@@tx_isolation??|??

  10. +———————–+—————–+??

  11. |?REPEATABLE–READ???????|?REPEATABLE–READ?|??

  12. +———————–+—————–+??

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

如此就出現了幻讀，以為表里沒有數據，其實數據已經存在了，提交后，才發現數據沖突了。

實驗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 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 “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：

Session A

Session B

start transaction ;

start transaction ;

SELECT * FROM t_bitfly
?WHERE id?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

附說明：

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;
| +——+——-+
| | 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?????|
| +—-+——-+

?

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

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

可以這么講，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。

? 版權聲明
THE END
數據庫
# mysql
喜歡就支持一下吧
點贊15 分享