MySQL事務隔離級別初探

MySQL有四種隔離級別，分別是：

 

READ UNCOMMITTED（未提交讀）

READ COMMITTED（提交讀）

REPEATABLE READ （可重復讀）

SERIALIZABLE（可串行化）

 

下面會分別用一些例子來解釋各種隔離級別，在開始之前，首先要了解一些前提知識：

 

AUTOCOMMIT：MySQL默認開啟自動提交（AUTOCOMMIT），如果不是顯式地開啟一個事務，則每個查詢都被當做一個事務執行提交操作。在當前連接中，可以通過設置 AUTOCOMMIT 變量來啟用或者禁用自動提交：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)

1 或者 ON 表示啟用，0 或者 OFF 表示禁用。當 AUTOCOMMIT 禁用時，所有的查詢都是在一個事務中，直到顯式地執行 COMMIT 提交或者 ROLLBACK 回滾，該事務結束，同時又開始了另一個新事務。修改 AUTOCOMMIT 對於費事務型的表，比如 MyISAM 或者內存表，不會有任何影響。對這類表來說，沒有 COMMIT 或者 ROLLBACK 的概念，也可以說是相當於一直處於 AUTOCOMMIT 啟用的狀態；

還有一些命令，在執行之前會強制執行 COMMIT 提交當前活動的事務。比如 ALTER TABLE , LOCK TABLES 等，如有需要，請檢查對應版本官方文檔來確認可能導致自動提交的語句；

查看和設置隔離級別：可以通過 SET TRANSACTION ISOLATION LEVEL 命令來設置隔離級別，或者在配置文件中設置整個數據庫的隔離級別；InnoDB支持所有的隔離級別；新的隔離級別會在下一個事務開始的時候生效；

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.00 sec)

 

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)

 

mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

 

mysql> set global tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

 

下面我用一些例子來演示一下各種隔離級別的表現：

 

mysql> create table test (i int,primary key(i)) engine=innodb; Query OK, 0 rows affected (0.02 sec) 

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`i` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------+

下文的例子中，表格左邊和表格右邊分別是兩個session中的行為和表現

 

READ UNCOMMITTED（未提交讀）

在  READ UNCOMMITTED 級別，事務中的修改，即使沒有被提交，對其他事務也都是可見的。事務可以讀取未提交的數據，這也被稱為髒讀（Dirty Read）。這個級別會導致很多的問題，從性能上來說，這個級別不會比其他的級別好太多，但是卻缺乏其他級別的種種好處，除非真的有非常充足的理由，在實際應用中，一般很少使用；

 

mysql> set session tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec)  mysql> set session tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec)  mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)  mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; Empty set (0.00 sec)      mysql> insert into test values(1); Query OK, 1 row affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec)  在右邊的session中未提交的數據， 在左邊session中可以讀到， 這就是所謂的髒讀；     mysql> rollback; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; Empty set (0.00 sec)    mysql> commit; Query OK, 0 rows affected (0.00 sec)   

  

READ COMMITTED（提交讀）

大多數數據庫的默認隔離級別都是 READ COMMITTED ，但 MySQL 不是。這個級別滿足了ACID原則中隔離性的簡單定義：一個事務開始時，只能“看見”已經提交的事務所做的修改。換句話說，一個事務從開始到提交之前，所做的任何修改對其他事務都是不可見的。這個級別有時候也叫作不可重復讀（nonrepeatable read），因為兩次執行同樣的查詢，可能會得到不一樣的結果；

mysql> set session tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED         | +------------------------+ 1 row in set (0.01 sec)  mysql> set session tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED         | +------------------------+ 1 row in set (0.01 sec)  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; Empty set (0.00 sec)      mysql> insert into test values(1); Query OK, 1 row affected (0.00 sec)  mysql> select * from test; Empty set (0.00 sec)  右邊 session 未提交的數據不能被讀取到，避免了髒讀     mysql> commit; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec)  右邊 session 已經提交，此時可以讀到     mysql> start transaction; Query OK, 0 rows affected (0.00 sec)    mysql> insert into test values(2); Query OK, 1 row affected (0.00 sec)   mysql> commit; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)  左邊 session 在一個事務的兩次查詢中， 出現了數據不一致的情況， 這在某些場景下是會有問題的， 所以這個隔離級別有時也被叫做 不可重復讀；   mysql> commit; Query OK, 0 rows affected (0.01 sec)   

 

REPEATABLE READ（可重復讀）

該級別是 MySQL 的默認隔離級別，這個隔離級別解決了上述的髒讀和不可重復讀的問題。在該級別下，同一個事務中多次讀取同樣的記錄的結果是一致的。不過可重復讀級別還是沒有解決另外一個幻讀（Phantom Read）的問題。所謂幻讀，指的是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，會產生幻行（Phantom Row）。

mysql> set session tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ        | +------------------------+ 1 row in set (0.00 sec)  mysql> set session tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)   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> start transaction; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)      mysql> insert into test values (3); Query OK, 1 row affected (0.00 sec)    mysql> commit; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)  即使右邊 session 已經提交，同一事務中讀取的數據不會變   mysql> commit; Query OK, 0 rows affected (0.00 sec)    mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec)    mysql> start transaction; Query OK, 0 rows affected (0.00 sec)      mysql> insert into test values (4); Query OK, 1 row affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec)    mysql> insert into test values (4); ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' 明明沒有‘4’這條數據，卻顯示主鍵沖突， 就好像有一個幻影數據的值為‘4’    mysql> commit; Query OK, 0 rows affected (0.00 sec)    mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | | 3 | | 4 | +---+ 4 rows in set (0.00 sec)    mysql> start transaction; Query OK, 0 rows affected (0.00 sec)      mysql> insert into test values (5); Query OK, 1 row affected (0.00 sec)  mysql> select * from test; +---+ | i | +---+ | 1 | | 2 | | 3 | | 4 | +---+ 4 rows in set (0.00 sec)    mysql> update test set i = i*10; Query OK, 5 rows affected (0.01 sec) Rows matched: 5  Changed: 5  Warnings: 0  一共就4條數據，這裡卻顯示匹配了5條， 多出來的1條就像幻影一樣   mysql> select * from test; +----+ | i  | +----+ | 10 | | 20 | | 30 | | 40 | | 50 | +----+ 5 rows in set (0.00 sec)  剛才還是4條數據，現在卻變成了5條數據   mysql> commit ; Query OK, 0 rows affected (0.00 sec)   

 

SERIALIZABLE（可串行化）

SERIALIZABLE是最高的隔離級別。它通過強制事務串行執行，避免了前面說的幻讀的問題。簡單來說，這個隔離級別會在讀取的每一行數據上都加鎖，所以可能導致大量的超時和鎖爭用問題。實際應用中也很少用到這個隔離級別，只有在非常需要確保數據一致性且可以接受沒有並發的情況下，才考慮使用此級別；讀取時相當於加了S鎖、更新時加X鎖；

mysql> set session tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | SERIALIZABLE           | +------------------------+ 1 row in set (0.00 sec)  mysql> set session tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | SERIALIZABLE           | +------------------------+ 1 row in set (0.00 sec)  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from test; +----+ | i  | +----+ | 10 | | 20 | | 30 | | 40 | | 50 | +----+ 5 rows in set (0.00 sec)     

mysql> update test set i = i+5 where i = 10;

阻塞...

mysql> commit; Query OK, 0 rows affected (0.00 sec)      Query OK, 1 row affected (11.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0 修改成功，可以看到阻塞了11.02秒

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set i = i - 5 where i = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

   

mysql> select * from test;
+----+
| i |
+----+
| 15 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (0.00 sec)

在autocommit為1且不顯式開啟事務的情況下，不會被左邊 session 中的更新操作阻塞，

不會獲取S鎖；

 

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

超時了...

mysql> select * from test;

阻塞...

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

   

+----+
| i |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (9.63 sec)

讀到了...