Mysql之事務(wù)提交和隔離級(jí)別

Mysql之事務(wù)提交和隔離級(jí)別

一、事務(wù)是什么?

事務(wù)簡(jiǎn)言之就是一組SQL執(zhí)行要么全部成功，要么全部失敗。MYSQL的事務(wù)在存儲(chǔ)引擎層實(shí)現(xiàn)。

1、事務(wù)都有ACID特性：

• 原子性（Atomicity）：一個(gè)事務(wù)必須被視為一個(gè)不可分割的單元；

• 一致性（Consistency）：數(shù)據(jù)庫總是從一種狀態(tài)切換到另一種狀態(tài)；

• 隔離性（Isolation）：通常來說，事務(wù)在提交前對(duì)于其他事務(wù)不可見；

• 持久性（Durablity）：一旦事務(wù)提交，所做修改永久保存數(shù)據(jù)庫；

事務(wù)最常用的例子就是銀行轉(zhuǎn)賬。假設(shè)polo需給tom轉(zhuǎn)賬1000元，如下步驟：

• 確認(rèn)polo賬戶余額高于1000元；

• 從polo的賬戶余額減去1000元；

• 將tom的賬戶余額增加1000元；

mysql> create table bank_accout(uid int not null,name varchar(255),balance decimal(9,2));
mysql> insert into bank_accout values(10001,'polo',5000),(10002,'tom',3000);
mysql> select * from bank_accout;
+-------+------+---------+
| uid | name| balance|
+-------+------+---------+
| 10001| polo| 5000.00|
| 10002| tom | 3000.00|
+-------+------+---------+

SQL語句如下:

mysql> BEGIN; 
mysql> select * from bank_accout;
+-------+------+---------+
| uid | name| balance|
+-------+------+---------+
| 10001| polo| 5000.00|
| 10002| tom | 3000.00|
+-------+------+---------+
mysql> UPDATE bank_account SET balance=balance-1000 WHERE uid=10001; 
mysql> UPDATE bank_account SET balance=balance+1000 WHERE uid=10002; 
mysql> COMMIT;

mysql> select * from bank_accout;
+-------+------+---------+
| uid | name| balance|
+-------+------+---------+
| 10001| polo| 4000.00|
| 10002| tom | 4000.00|
+-------+------+---------+

mysql> BEGIN;     
或者
mysql> START TRANSACTION；# mysql啟動(dòng)事務(wù)

mysql> Rollback;  # 回滾，返回修改之前。
mysql> commit；  # 提交數(shù)據(jù)，才真實(shí)修改數(shù)據(jù)。

上述步驟執(zhí)行在一個(gè)事務(wù)中就能夠保證數(shù)據(jù)的完整性，要么全部成功，要么全部失敗。

2、Mysql提供兩種事務(wù)型引擎：

Innodb和NDBCluster。默認(rèn)采用自動(dòng)提交模式，執(zhí)行一條語句自動(dòng)COMMIT。通過AUTOCOMMIT變量可啟用或者禁用自動(dòng)提交模式：

mysql> SHOW VARIABLES LIKE "AUTOCOMMIT"; 
+---------------+-------+ 
| Variable_name|Value |
+---------------+-------+
| autocommit  | ON  | 
+---------------+-------+ 
1 row in set (0.00 sec)

mysql> SET AUTOCOMMIT=1;

AUTOCOMMIT=1表示開啟默認(rèn)提交，0表示關(guān)閉默認(rèn)提交需要手動(dòng)提交。

二、事務(wù)隔離級(jí)別

事務(wù)隔離性的解釋：通常情況下，事務(wù)在提交之前對(duì)于其他事務(wù)不可見。

數(shù)據(jù)庫有四種隔離級(jí)別，當(dāng)然Mysql也是如此。分別為：

• READ UNCOMMITED（未提交讀）

• READ COMMITED（已提交讀）

• EPEATABLE READ（可重復(fù)讀）

• SEAIALIZABLE（可串行化）

個(gè)人理解 : 隔離級(jí)別就是決定一個(gè)事務(wù)的修改，另一個(gè)事務(wù)什么情況下可見。

書本解釋 : 隔離級(jí)別都規(guī)定了一個(gè)事務(wù)中所做修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見的。

上面兩段理解的區(qū)別在于是否存在事務(wù)內(nèi)可見性的規(guī)定。

開始說明Mysql的四種隔離級(jí)別，先準(zhǔn)備一張學(xué)生表：

mysql> CREATE TABLE `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

只有id（主鍵自增）與name字段

1、READ UNCOMMITTED（未提交讀）

事務(wù)中修改沒有提交對(duì)其他事務(wù)也是可見的，俗稱臟讀。非常不建議使用。

例:

客戶端A和B設(shè)置隔離級(jí)別為未提交讀

mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';

客戶端A與B開啟事務(wù)并查詢student

mysql> BEGIN； 
mysql> SELECT * FROM student; 
Empty set (0.00 sec)

客戶端A和B都是空數(shù)據(jù)

客服端B插入一條新的數(shù)據(jù)

mysql> INSERT INTO student(name) VALUES("polo"); 
Query OK, 1 row affected (0.00 sec)

此時(shí)事務(wù)未提交，客服端A查看student表

mysql> SELECT * FROM student; 
+----+------+ 
| id| name| 
+----+------+ 
| 1 | polo|
+----+------+

客戶端A看到B未提交的修改

客戶端B執(zhí)行回滾操作

mysql> ROLLBACK;

成功之后，客戶端A查看student表

mysql> SELECT * FROM student; 
Empty set (0.00 sec)

客戶端A查看數(shù)據(jù)為空

以上可以看出未提交讀隔離級(jí)別的危險(xiǎn)性，對(duì)于一個(gè)沒有提交事務(wù)所做修改對(duì)另一個(gè)事務(wù)是可見狀態(tài)，容易造成臟讀。非特殊情況不得使用此級(jí)別

2、READ COMMITTED（提交讀）

多數(shù)數(shù)據(jù)庫系統(tǒng)默認(rèn)為此級(jí)別（Mysql不是）。已提交讀級(jí)別即為一個(gè)事務(wù)只能已提交事務(wù)所做的修改，也就解決了未提交讀的問題，即臟讀的問題。

例：

客戶端A和B設(shè)置隔離級(jí)別為已提交讀

mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';

客戶端A與B開啟事務(wù)并查詢student

mysql> BEGIN; 
mysql> SELECT * FROM student; 
Empty set (0.00 sec)

客戶端A和B都為空

客服端B插入一條新的數(shù)據(jù)，不提交

mysql> INSERT INTO student (name) VALUES('polo');

客戶端A查看student

mysql> SELECT * FROM student; 
Empty set (0.00 sec)

注意這里與上面不同了，在客戶端B沒有提交事務(wù)情況下無數(shù)據(jù)

下面客戶端B提交事務(wù)

mysql> COMMIT;

客戶端A再次查看student表。

mysql> select * from student;
+----+------+
| id|name |
+----+------+
| 2 |polo |
+----+------+

成功讀取到客戶

從上面的示例可以看出，提交讀沒有了未提交讀的問題，但我們可以看到在客戶端A的一個(gè)事務(wù)中執(zhí)行兩次同樣的SELECT語句得到不同結(jié)果，因此已提交讀又被稱為不可重復(fù)讀。同樣篩選條件可能得到不同的結(jié)果。

3、REPEATABLE READ（可重復(fù)讀）

解決已提交讀不可重復(fù)讀取的問題。

例：

客戶端A和B設(shè)置隔離級(jí)別為可重復(fù)讀

mysql> SET SESSION tx_isolation='REPEATABLE-READ';

客戶端A與B開啟事務(wù)并查看

mysql> BEGIN; 
mysql> select * from student;
+----+------+
| id | name |
+----+------+
| 2 | polo |
+----+------+

客服端B更新polo為tom，并提交事務(wù)

mysql> UPDATE student SET name='tom' WHERE id=2; 
mysql> COMMIT;

客戶端A查看student表

mysql> select * from student;
+----+------+
| id| name|
+----+------+
| 2 | polo|
+----+------+

注意客戶端A查看數(shù)據(jù)未變，沒有不可重復(fù)讀問題

客戶端A提交事務(wù)，并查看student表

mysql> COMMIT; 
mysql> select * from student;
+----+------+
| id| name|
+----+------+
| 2 | tom |
+----+------+

上面實(shí)例可知，可重復(fù)讀兩次讀取內(nèi)容一樣。數(shù)據(jù)庫這級(jí)別并沒有解決幻讀的問題。但是MYSQL在可重復(fù)讀基礎(chǔ)上增加了MVCC機(jī)制解決了此問題，實(shí)例無法演示幻讀效果。

什么是幻讀？

首先，可重復(fù)讀鎖定范圍為當(dāng)前查詢到的內(nèi)容，如執(zhí)行

mysql> SELECT * FROM student WHERE id>=1

鎖定的即id>=1查到的行，為行級(jí)鎖。如另一事務(wù)執(zhí)行并默認(rèn)提交以下語句

mysql> INSERT INTO student (name) VALUES ('polo');

新增的這行并沒有被鎖定，此時(shí)讀取student

mysql> SELECT * FROM student WHERE id>=1;
+----+-------+
| id| name |
+----+-------+
| 2 | tom |
| 3 | polo |
+----+-------+

便出現(xiàn)了幻讀

除了使用MYSQL的MVCC機(jī)制，還可以使用可串行化隔離級(jí)別解決此問題。

4、SEAIALIZABLE（可串行化）

可串行化是最高隔離級(jí)別，強(qiáng)制事務(wù)串行執(zhí)行。執(zhí)行串行了也就解決了一切的問題，這個(gè)級(jí)別只有在對(duì)數(shù)據(jù)一致性要求非常嚴(yán)格且沒用并發(fā)的情況下使用

例：

客戶端A和B設(shè)置隔離級(jí)別為可串行化

mysql> SET SESSION tx_isolation='SERIALIZABLE';

客戶端A執(zhí)行查詢

mysql> BEGIN;
mysql> SELECT * FROM student WHERE id<4; 
+----+---------+ 
| id | name   | 
+----+---------+ 
|  1| polo   | 
|  2| tom     | 
+----+---------+ 
2 rows in set (0.00 sec)

客戶端B執(zhí)行新增

mysql> INSERT INTO student (name) VALUES('yy');

此時(shí)我們會(huì)發(fā)現(xiàn)INSERT語句被阻塞執(zhí)行，原因就是A執(zhí)行了查詢表student同時(shí)滿足id<4，已被鎖定。如果查詢表student條件為id<3，則新增語句可正常執(zhí)行。