首頁 >資料庫 >mysql教程 >MySQL中的四種交易隔離等級實例分析

MySQL中的四種交易隔離等級實例分析

小云云原創: 2017-12-22 14:27:241835瀏覽

在資料庫操作中，為了有效保證並發讀取資料的正確性，所提出的事務隔離等級。資料庫事務的隔離級別有4個，SQL標準定義了4種隔離級別，包括了一些具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。以下這篇文章透過實例詳細的給大家分析了關於MySQL中的四種事務隔離等級的相關資料，需要的朋友可以參考下。

前言

下面話不多說了，來一起看看詳細的介紹。

資料庫事務有四個隔離等級：

#未提交讀取(Read Uncommitted)：允許髒讀，也就是可能讀取到其他會話中未提交事務修改的數據。
提交讀取(Read Committed)：只能讀取到已經提交的數據，Oracle等多數資料庫預設都是該級別。
可重複讀(Repeated Read)：可重複讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB預設層級。在SQL標準中，此隔離等級消除了不可重複讀，但也存在幻讀。
序列讀(Serializable)：完全串列化的讀，每次讀取都需要取得表級共享鎖定，讀寫相互都會阻塞。

上面這樣的教科書式定義第一次接觸事務隔離概念的朋友看了可能會一臉懵逼，下面我們就透過具體的實例來解釋四個隔離等級。

首先我們建立一個user表：

CREATE TABLE user (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

讀取未提交隔離等級

我們先將交易的隔離等級設定為read committed：

mysql> set session transaction isolation level 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)

#在下面我們開了兩個終端分別用來模擬事務一和事務二，p.s: 操作一和操作二的意思是按照時間順序來執行的。

事務1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('ziwenxie'); # 操作3
Query OK, 1 row affected (0.05 sec)

事務2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作4
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

從上面的執行結果可以很清楚的看出來，在read uncommited層級下面我們在事務一中可能會讀取到事務二中沒有commit的數據，這就是髒讀。

讀取提交隔離等級

透過設定隔離等級為committed可以解決上面的髒讀問題。

mysql> set session transaction isolation level read committed;

事務一

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

事務二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

雖然髒讀的問題解決了，但是註意在事務一的操作七中，事務二在操作六commit後會造成事務一在同一個transaction中兩次讀取到的資料不同，這就是不可重複讀取問題，使用第三個事務隔離等級repeatable read可以解決這個問題。

可重複讀取隔離級別

MySQL的Innodb儲存引擎預設的交易隔離級別就是可重複讀取隔離級別，所以我們不用進行多餘的設定。

事務一

mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

事務二

mysql> start tansactoin; # 操作二
mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作四

在事務一的操作五中我們並沒有讀取到事務二在操作三中的update，只有在commit之後才能讀到更新後的數據。

Innodb解決了幻讀麼

實際上RR等級是可能產生幻讀，InnoDB引擎官方稱中利用MVCC多版本並發控制解決了這個問題，下面我們驗證一下Innodb真的解決了幻讀了什麼？

為了方便展示，我修改了一下上面的user表：

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)
mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+----------+----------+
| id | name  | salary |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

交易一

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # 操作六，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user; # 操作七， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name  | salary |
+----+----------+--------+
| 10 | ziwenxie | 4444 |
| 11 | zhangsan | 4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

交易二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

從上面的範例可以看出，Innodb並沒有如官方所說解決幻讀，不過上面這樣的場景中也不是很常見不用過多的擔心。

串行化隔離級別

所有事務串行執行，最高隔離級別，不會出現幻讀性能會很差，實際開發中很少使用到。

MySQL中的四種交易隔離等級實例分析

相關文章