實例分析MySQL下的四種事務隔離級別
作者：ziwenxie
數據庫事務有四種隔離級別,本文我們就通過具體的實例來解釋四個隔離級別。
數據庫事務有四種隔離級別：
- 未提交讀(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并沒有如官方所說解決幻讀，不過上面這樣的場景中也不是很常見不用過多的擔心。
串行化隔離級別
所有事務串行執行，***隔離級別，不會出現幻讀性能會很差，實際開發中很少使用到。
責任編輯：龐桂玉
來源：
segmentfault
