關(guān)于SQL的隔離級(jí)別
成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)的關(guān)注點(diǎn)不是能為您做些什么網(wǎng)站，而是怎么做網(wǎng)站，有沒有做好網(wǎng)站，給成都創(chuàng)新互聯(lián)公司一個(gè)展示的機(jī)會(huì)來(lái)證明自己，這并不會(huì)花費(fèi)您太多時(shí)間，或許會(huì)給您帶來(lái)新的靈感和驚喜。面向用戶友好，注重用戶體驗(yàn)，一切以用戶為中心。
SQL標(biāo)準(zhǔn)定義了4類隔離級(jí)別，如下所示：
1. Read Uncommitted (讀取未提交內(nèi)容)
在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少。讀取未提交的數(shù)據(jù)，也被稱之為臟讀(Dirty Read)
2. Read Committed (讀取提交內(nèi)容)
這是大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別(但不是MySQL默認(rèn)的)。它滿足了隔離的簡(jiǎn)單定義：一個(gè)事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變。這種隔離級(jí)別 也支持所謂的不可重復(fù)讀(Nonrepeatable Read)，因?yàn)橥皇聞?wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的commit，所以同一select可能返回不同結(jié)果。
3. Repeatable Read (可重復(fù)讀)
這是MySQL的默認(rèn)事務(wù)隔離級(jí)別，它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)，會(huì)看到同樣的數(shù)據(jù)行。不過(guò)理論上，這會(huì)導(dǎo)致另一個(gè)棘手的問題：幻讀 （Phantom Read）。簡(jiǎn)單的說(shuō)，幻讀指當(dāng)用戶讀取某一范圍的數(shù)據(jù)行時(shí)，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行，當(dāng)用戶再讀取該范圍的數(shù)據(jù)行時(shí)，會(huì)發(fā)現(xiàn)有新的“幻影” 行。InnoDB和Falcon存儲(chǔ)引擎通過(guò)多版本并發(fā)控制（MVCC，Multiversion Concurrency Control）機(jī)制解決了該問題。
4. Serializable (可串行化)
這是最高的隔離級(jí)別，它通過(guò)強(qiáng)制事務(wù)排序，使之不可能相互沖突，從而解決幻讀問題。簡(jiǎn)言之，它是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖。在這個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)。
在Mariadb中，這4種隔離級(jí)別有可能產(chǎn)生的問題如下圖所示：
相關(guān)實(shí)驗(yàn)
下面分別針對(duì)不同的隔離狀態(tài)進(jìn)行測(cè)試：
準(zhǔn)備的環(huán)境如下所示：
在Mariadb服務(wù)器端，創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)名為hldb，創(chuàng)建一個(gè)INNODB引擎的數(shù)據(jù)表名為test，并提前插入兩條數(shù)據(jù)
準(zhǔn)備兩個(gè)MySQL客戶端線程，連接到服務(wù)器上面：
MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+
1. Read uncommitted(讀未提交)
首先將兩個(gè)客戶端的隔離級(jí)別分別都設(shè)置為Read uncommitted模式：
MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> set @@session.tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec) ...... ...... MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> set @@session.tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec)
第一步，在再在id=6的客戶端上面(以后簡(jiǎn)稱為id6)上面完成一次查詢。第二步，在兩個(gè)客戶端上面同時(shí)打開start transaction。第三步，兩個(gè)客戶端都打開了start transaction之后，再在id=5的客戶端上面(以后簡(jiǎn)稱為id5)上面插入一條數(shù)據(jù)。第四步，在id5自身的會(huì)話上面再用select進(jìn)行查詢。第五步，在id6會(huì)話上面用select進(jìn)行查詢。
在id5上面看到的結(jié)果如下：
重慶分公司
重慶分公司
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 21 | b |
+----+------+
2 rows in set (0.00 sec)
MariaDB [hldb]> insert into test(nm) values('c');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 21 | b |
| 22 | c |
+----+------+
3 rows in set (0.00 sec)在id6上面看到的結(jié)果如下：
MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | +----+------+ 2 rows in set (0.00 sec) MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec)
結(jié)論：
可以看到，如果事務(wù)級(jí)別設(shè)定成為Read Uncommitted(讀未提交)，在id5的事務(wù)并未提交的狀態(tài)下，id6的事務(wù)是可以將其未提交的事務(wù)查詢到的。這種能夠讀取到未提交事務(wù)的現(xiàn)象，稱為臟讀
2. Read Committed (讀取提交內(nèi)容)
將兩個(gè)客戶端的事務(wù)隔離級(jí)別均設(shè)定為Read Committed，并且查詢一下test數(shù)據(jù)表里面的內(nèi)容
MariaDB [hldb]> set @@session.tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec) ...... ...... MariaDB [hldb]> set @@session.tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec)
第一步，在兩個(gè)客戶端上面開啟start transaction。第二步，在id5上面添加一條記錄，同時(shí)在id5, id6上面查詢。第三步，在id5上面刪除一條記錄，同時(shí)在id5, id6上面查詢。第四步，在id5上面提交事務(wù)，并且在id5, id6上面查詢。
在id5客戶端上面的操作結(jié)果如下所示：
第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
第二步：
MariaDB [hldb]> insert into test(nm) values('d');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 21 | b |
| 22 | c |
| 23 | d |
+----+------+
4 rows in set (0.00 sec)
第三步：
MariaDB [hldb]> delete from test where nm='b';
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 22 | c |
| 23 | d |
+----+------+
3 rows in set (0.00 sec)
第四步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 22 | c |
| 23 | d |
+----+------+
3 rows in set (0.00 sec)在id6客戶端上面的操作如下所示：
第一步： MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) 第二步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec) 第三步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec) 第四步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 22 | c | | 23 | d | +----+------+ 3 rows in set (0.01 sec)
結(jié)論：
在Read Committed(讀取提交內(nèi)容)的隔離級(jí)別下，解決了臟讀現(xiàn)象，但是帶來(lái)了另外一種現(xiàn)象：不可重復(fù)讀。id5事務(wù)提交的前后，id6在同一個(gè)事務(wù)中，所查詢到的內(nèi)容不一致。
3. Repeatable Read(可重復(fù)讀)
首先將兩個(gè)客戶端的事務(wù)隔離級(jí)別都設(shè)置為Repeatable Read，并查詢test數(shù)據(jù)表里面的內(nèi)容：
MariaDB [hldb]> set @@session.tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) ...... ...... MariaDB [hldb]> set @@session.tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec)
第一步，在兩個(gè)客戶端上面開啟start transaction。第二步，在id5上面添加一條記錄，同時(shí)在id5, id6上面查詢。第三步，在id5上面刪除一條記錄，同時(shí)在id5, id6上面查詢。第四步，在id5上面提交事務(wù)，并且在id5, id6上面查詢。第五步，在id6上面提交事務(wù)，并且在id6上面查詢。
在id5客戶端上面的操作結(jié)果如下所示：
第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
第二步：
MariaDB [hldb]> insert into test(nm) values('b');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 25 | b |
+----+------+
2 rows in set (0.00 sec)
第三步：
MariaDB [hldb]> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 25 | b |
+----+------+
1 row in set (0.00 sec)
第四步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 25 | b |
+----+------+
1 row in set (0.00 sec)在id6上面的操作結(jié)果如下所示：
第一步： MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) 第二步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) 第三步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.01 sec) 第四步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) 第五步： MariaDB [hldb]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 25 | b | +----+------+ 1 row in set (0.00 sec)
結(jié)論：
由上述實(shí)驗(yàn)可以看出，事務(wù)隔離級(jí)別Repeatable read(可重復(fù)讀)和Read Committed(已提交讀)的不同之處在于，在同一事務(wù)環(huán)境下，前后兩次讀取的內(nèi)容是一致的，而不受其他事務(wù)是否提交的影響。
注：
在Repeatable read(可重復(fù)讀)的條件下，有可能會(huì)出現(xiàn)Phantom Read(幻讀)現(xiàn)象。該現(xiàn)象可以通過(guò)模擬update來(lái)實(shí)現(xiàn)：
第一步，客戶端兩邊都開啟start transaction。第二步，在id6上面新增一個(gè)字段，并在id5和id6上面分別查詢。第三步，在id6上面提交，并在id5和id6上面分別查詢。第四步，在id5上面對(duì)所有行的nm字段進(jìn)行更新，并在id5和id6上面分別查詢。
id6上面的結(jié)果如下所示：
第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.00 sec)
第二步：
MariaDB [hldb]> insert into test(nm) values('b');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
第三步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)在id5上面的操作如下所示：
第一步： MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) 第二步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) 第三步： MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) 第四步： MariaDB [hldb]> update test set nm='c'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | c | | 2 | c | +----+------+ 2 rows in set (0.00 sec)
可以看到，通過(guò)更新命令，在未結(jié)束的事務(wù)里面竟然也讀到了“新的數(shù)據(jù)”，這便是一種Phantom Read(幻讀)。
4. Serializable(可序列化)
首先將兩個(gè)客戶端的事務(wù)隔離級(jí)別都設(shè)置為Serializable，并查詢test數(shù)據(jù)表里面的內(nèi)容：
MariaDB [hldb]> set @@session.tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 26 | a | | 27 | b | +----+------+ 2 rows in set (0.00 sec) ...... ...... MariaDB [hldb]> set @@session.tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 26 | a | | 27 | b | +----+------+ 2 rows in set (0.00 sec)
第一步，在兩個(gè)客戶端上面開啟start transaction。第二步，在id5上面添加一條記錄，同時(shí)在id5, id6上查詢
在id5上面查詢的結(jié)果如下：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> insert into test(nm) values('c');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 26 | a |
| 27 | b |
| 28 | c |
+----+------+
3 rows in set (0.00 sec)在id6上面查詢的結(jié)果如下：
MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select * from test; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
由上述結(jié)果可以看到，在id5未提交事務(wù)之前，即使是select查詢操作，在其他事務(wù)里面也是不允許執(zhí)行的，只有等待id5提交事務(wù)之后，其他事務(wù)才可以進(jìn)行更新或者修改的操作。
