mysql-Innodb交易隔離等級-repeatable read詳解

標籤：隔離等級   無法   pac   執行   padding   提交   acl   lib   mat   

http://blog.csdn.net/dong976209075/article/details/8802778

經驗總結：

Python使用MySQLdb資料庫後，如使用多線程，每個線程建立一個db連結，然後再各自建立一個遊標cursor，其中第一個線程讀一個表中資料為空白，第二個寫入該表一條資料並提交，第一個線程再讀該表資料將仍然無法讀出。和多資料庫的事務層級應該有關係；還可以在第一個讀之前先插入一條，將能夠讀出第二個線程寫入並提交過的資料。

 

 

 

 

 

轉載自：http://blog.csdn.net/alifel/article/details/6548075

 

 

一、交易隔離等級

ANSI/ISO SQL標準定義了4中交易隔離等級：未提交讀（read uncommitted），提交讀（read committed），重複讀（repeatable read），串列讀（serializable）。

對於不同的事務，採用不同的隔離等級分別有不同的結果。不同的隔離等級有不同的現象。主要有下面3種現在：

1、髒讀（dirty read）：一個事務可以讀取另一個尚未提交事務的修改資料。

2、非重複讀（nonrepeatable read）：在同一個事務中，同一個查詢在T1時間讀取某一行，在T2時間重新讀取這一行時候，這一行的資料已經發生修改，可能被更新了（update），也可能被刪除了（delete）。

3、幻像讀（phantom read）：在同一事務中，同一查詢多次進行時候，由於其他插入操作（insert）的事務提交，導致每次返回不同的結果集。

不同的隔離等級有不同的現象，並有不同的鎖定/並發機制，隔離等級越高，資料庫的並發性就越差，4種交易隔離等級分別表現的現象如下表：

隔離等級	髒讀	非重複讀	幻像讀
read uncommitted	允許	允許	允許
read committed		允許	允許
repeatable read			允許
serializable

二、資料庫中的預設交易隔離等級

在Oracle中預設的交易隔離等級是提交讀（read committed）。

對於MySQL的Innodb的預設交易隔離等級是重複讀（repeatable read）。可以通過下面的命令查看：

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

+———————–+—————–+

| @@GLOBAL.tx_isolation | @@tx_isolation  |

+———————–+—————–+

| REPEATABLE-READ | REPEATABLE-READ |

+———————–+—————–+

1 row in set (0.00 sec)

下面進行一下測試：

 

Time	Session 1	Session 2
T1	set autocommit=0;	set autocommit=0;
T2	mysql> select * from tmp_test;   +——+———+ | id   | version | +——+———+ |    1 |       1 | +——+———+ 1 row in set (0.00 sec)
T3		mysql> update tmp_test set version=2 where id=1;   Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | +——+———+ 1 row in set (0.00 sec)
T4	mysql> select * from tmp_test;   +——+———+ | id   | version | +——+———+ |    1 |       1 | +——+———+ 1 row in set (0.00 sec) 【說明】 Session 2未提交，看到資料不變，無髒讀。
T5		commit;
T6	mysql> select * from tmp_test;   +——+———+ | id   | version | +——+———+ |    1 |       1 | +——+———+ 1 row in set (0.00 sec) 【說明】 Session 2已經提交，還是看到資料不變，即可以重複讀。
T7	commit;
T8	mysql> select * from tmp_test;   +——+———+ | id   | version | +——+———+ |    1 |       2 | +——+———+ 1 row in set (0.00 sec) 【說明】 提交事務，看到最新資料。
T9		mysql> insert into tmp_test values(2,1);   Query OK, 1 row affected (0.00 sec) mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | |    2 |       1 | +——+———+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
T10	mysql> select * from tmp_test;   +——+———+ | id   | version | +——+———+ |    1 |       2 | +——+———+ 1 row in set (0.00 sec) 【說明】 Session 2的insert事務已經提交，看到的資料和T8的時候一樣，即未發生幻象讀。
T11	mysql> commit;   Query OK, 0 rows affected (0.00 sec) mysql> select * from tmp_test; +——+———+ | id   | version | +——+———+ |    1 |       2 | |    2 |       1 | +——+———+ 2 rows in set (0.00 sec) 【說明】 事務提交，看到最新資料。

上面的結果可以看到Innodb的重複讀（repeatable read）不允許髒讀，不允許非重複讀（即可以重複讀，Innodb使用多版本一致性讀來實現）和不允許幻象讀（這點和ANSI/ISO SQL標準定義的有所區別）。

另外，同樣的測試：

1、當session 2進行truncate表的時候，這個時候session 1重新查詢就看不到資料。

2、當session 2進行alter表的時候，這個時候session 1重新查詢就看不到資料。

 

造成以上的原因是因為 mysql的持續非鎖定讀，在repeatable read層級下，讀採用的是持續非鎖定讀。相關介紹見下面：

 

持續讀意味著InnoDB使用它的多版本化來給一個查詢展示某個時間點處資料庫的快照。查詢看到在那個時間點之前被提交的那些確切事務做的更改，並且沒有其後的事務或未提交事務做的改變。這個規則的例外是，查詢看到發布該查詢的事務本身所做的改變。

如果你運行在預設的REPEATABLE READ隔離等級，則在同一事務內的所有持續讀讀取由該事務中第一個這樣的讀所確立的快照。你可以通過提交當前事務並在發布新查詢的事務之後，為你的查詢獲得一個更新鮮的快照。

持續讀是預設模式，在其中InnoDBzai在READ COMMITTED和REPEATABLE READ隔離等級處理SELECT語句。持續讀不在任何它訪問的表上設定鎖定，因此，其它使用者可自由地在持續讀在一個表上執行的同一時間修改這些表。

注意，持續讀不在DROP TABLE和ALTER TABLE上作用。持續讀不在DROP TABLE上作用，因為MySQL不能使用已經被移除的表，並且InnoDB 破壞了該表。持續讀不在ALTER TABLE上作用，因為它在某事務內執行，該事務建立一個新表，並且從舊錶往新表中插入行。現在，當你重新發出持續讀之時，它不能在新表中看見任何行，因為它們被插入到一個在持續讀讀取的快照中不可見的事務 裡。

 

 

MySQL官方文檔中的多版本一致性讀中說明了原因：Consistent read does not work over certain DDL statements。

mysql-Innodb交易隔離等級-repeatable read詳解