[譯]MySQL不加鎖實現一致性讀的機制分析

標籤：style   blog   http   color   使用   io   strong   資料   

原文直通車：Consistent Nonlocking Reads

 

MySQL的一致性讀的機制是是這樣實現的：InnoDB引擎為一個事務Tx提供一個在時間T1的版本快照（T1就是在本

事務中首次執行查詢語句的時間點）。事務Tx中可以查詢到時間點T1之前提交的資料，時間點T1之後提交的資料在

Tx中是看不到的。唯一的例外Ex是在事務Tx中可以看到在本事務中提交的資料（即便是在T1時間點還沒有提交的資料）。

 

先建一個表，邊理論邊實踐，具體看下MySQL是如何工作的。

mysql> create table mvcc(    ->  id int primary key,    ->  name varchar(20),    ->  city varchar(20)    -> ) engine innodb default charset utf8    -> ;Query OK, 0 rows affected (0.01 sec)

建立資料庫連接，假定為session1，在session1中做如下操作：

mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> set autocommit=off;Query OK, 0 rows affected (0.00 sec)mysql> insert into mvcc(id,name,city) values(1,‘name1‘,‘city1‘);Query OK, 1 row affected (0.00 sec)mysql> select * from mvcc;                                             T1+----+-------+-------+| id | name  | city  |+----+-------+-------+|  1 | name1 | city1 |+----+-------+-------+1 row in set (0.00 sec)

在上面的實驗中可以看到，本事務Tx中T1時間前插入的還未提交的資料，在T1時間建立的快照裡面是可以看到的。

這裡說是快照，其實不是嚴格意義上的快照（其實是在T1點做個標記，T1之後其他事務提交的資料，利用undo log復原，得出舊資料）

這個時候，其實事務Tx，還有其他事務，都可以更新T1時間點的快照資料。

這個例外Ex會導致如下的異常：如果你在Tx中更新了表的資料，在Tx中的Select語句會看到資料這些更新的資料，但是此時Select還可能會看到

某些行的比較老的資料（T1後有其他事務進行了資料的更新，但是在事務Tx中是看不到這些更新的，即便是其他事務提交了，所有上面這些都是在Repeatable read隔離等級的情況）

這個異常異味著在事務Tx的select看到的狀態不是資料庫真是的狀態。

 

如果資料庫的隔離等級是Repeatable Read隔離等級的（MySQL的預設層級），在事務Tx中所有的select語句所看到的快照都是跟Tx中第一條select

看到的資料是一樣的。如果你先給在tx中得到新的快照，只能先commit本事務，在新開事務

 

如果資料庫的隔離等級是READ COMMITTED層級的話，在Tx中的每次select操作都會重新得到一份最新的快照。

 

一致性讀在Innodb引擎的在READ COMMITTED和REPEATABLE READ隔離等級下執行SELECT語句的時候的預設行為。一致性讀不會在所讀的表上添加

鎖，所以其他session也可以同時修改這個表的資料。

 

如果你的MySQL資料庫運行在預設的REPEATABLE READ的隔離等級的話。當你發出一個一致性讀（也就是普通的SELECT語句）。Innodb會給你的事務

賦予一個時間點T，這個時間點T就是你在事務中執行select語句的時間點。如果資料庫伺服器為你當前事務賦予了一個時間點之後其他事務刪除了資料，在你事務中是看不到這條資料被刪除了。其他事務執行Insert你當前事務也看不到新增的資料，其他事務執行update你當前事務也看到更新的資料。

注意：

上述這種資料庫快照集一般使用了在一個事務中做資料讀取操作（select).不太使用於DML（insert，update，delete）語句。

考慮如下情境：

如果你在一個事務中修改或者插入了某條記錄並且提交了該事務。另外一個Repeatable read的transaction TX2是看不到他們的。雖然看不到這些更改或者新增的資料，但是TX2

可以刪除剛才新增的資料（unbeliveable），實驗說明一切。

琢磨下如下實驗：

在repeatable read隔離等級的session1的transaction中建立快照（執行select語句）

步驟1

mysql> select @@session.tx_isolation;+------------------------+| @@session.tx_isolation |+------------------------+| REPEATABLE-READ        |+------------------------+1 row in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from mvcc;+----+-----------------+-------+| id | name            | city  |+----+-----------------+-------+|  1 | name1-not-new-1 | city1 ||  2 | name2-not-new   | city2 ||  3 | name3           | city3 |+----+-----------------+-------+3 rows in set (0.00 sec)

在另一個session2的transaction中新增一條資料

步驟2

mysql> select * from mvcc;+----+-----------------+-------+| id | name            | city  |+----+-----------------+-------+|  1 | name1-not-new-1 | city1 ||  2 | name2-not-new   | city2 ||  3 | name3           | city3 |+----+-----------------+-------+3 rows in set (0.00 sec)mysql> insert into mvcc(id,name,city) values(4,‘name4‘,‘city4‘);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from mvcc;+----+-----------------+-------+| id | name            | city  |+----+-----------------+-------+|  1 | name1-not-new-1 | city1 ||  2 | name2-not-new   | city2 ||  3 | name3           | city3 ||  4 | name4           | city4 |+----+-----------------+-------+4 rows in set (0.00 sec)

在session1中查看剛才的資料，發現查看不到，但是刪除能成功。

步驟3

mysql> select * from mvcc;+----+-----------------+-------+| id | name            | city  |+----+-----------------+-------+|  1 | name1-not-new-1 | city1 ||  2 | name2-not-new   | city2 ||  3 | name3           | city3 |+----+-----------------+-------+3 rows in set (0.00 sec)mysql> delete from mvcc where id=4;Query OK, 1 row affected (0.00 sec)

 

這就是multi-versioned concurrency control（簡稱mvcc）

 

在下面的例子中，session A只有在sessionB commit並且session A commit之後才能看到b提交的資料。sessionB提交跟sessionA提交的這段間隔內sessionA就”錯過了“sessionB的資料

           Session A              Session B           SET autocommit=0;      SET autocommit=0;time|          SELECT * FROM t;|          empty set|                                 INSERT INTO t VALUES (1, 2);|v          SELECT * FROM t;           empty set                                  COMMIT;           SELECT * FROM t;           empty set           COMMIT;           SELECT * FROM t;           ---------------------           |    1    |    2    |           ---------------------           1 row in set

如果想要在在sessionA看到sessionB commit到session A commit這段時間內的資料，可以使用如下文法

SELECT * FROM t LOCK IN SHARE MODE;