Mysql之事務提交和隔離級別

Mysql之事務提交和隔離級別

一、事務是什么?

事務簡言之就是一組SQL執行要么全部成功，要么全部失敗。MYSQL的事務在存儲引擎層實現。

1、事務都有ACID特性：

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

• 一致性（Consistency）：數據庫總是從一種狀態切換到另一種狀態；

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

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

事務最常用的例子就是銀行轉賬。假設polo需給tom轉賬1000元，如下步驟：

• 確認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啟動事務

mysql> Rollback;  # 回滾，返回修改之前。
mysql> commit；  # 提交數據，才真實修改數據。

上述步驟執行在一個事務中就能夠保證數據的完整性，要么全部成功，要么全部失敗。

2、Mysql提供兩種事務型引擎：

Innodb和NDBCluster。默認采用自動提交模式，執行一條語句自動COMMIT。通過AUTOCOMMIT變量可啟用或者禁用自動提交模式：

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

mysql> SET AUTOCOMMIT=1;

AUTOCOMMIT=1表示開啟默認提交，0表示關閉默認提交需要手動提交。

二、事務隔離級別

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

數據庫有四種隔離級別，當然Mysql也是如此。分別為：

• READ UNCOMMITED（未提交讀）

• READ COMMITED（已提交讀）

• EPEATABLE READ（可重復讀）

• SEAIALIZABLE（可串行化）

個人理解 : 隔離級別就是決定一個事務的修改，另一個事務什么情況下可見。

書本解釋 : 隔離級別都規定了一個事務中所做修改，哪些在事務內和事務間是可見的。

上面兩段理解的區別在于是否存在事務內可見性的規定。

開始說明Mysql的四種隔離級別，先準備一張學生表：

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（未提交讀）

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

例:

客戶端A和B設置隔離級別為未提交讀

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

客戶端A與B開啟事務并查詢student

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

客戶端A和B都是空數據

客服端B插入一條新的數據

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

此時事務未提交，客服端A查看student表

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

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

客戶端B執行回滾操作

mysql> ROLLBACK;

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

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

客戶端A查看數據為空

以上可以看出未提交讀隔離級別的危險性，對于一個沒有提交事務所做修改對另一個事務是可見狀態，容易造成臟讀。非特殊情況不得使用此級別

2、READ COMMITTED（提交讀）

多數數據庫系統默認為此級別（Mysql不是）。已提交讀級別即為一個事務只能已提交事務所做的修改，也就解決了未提交讀的問題，即臟讀的問題。

例：

客戶端A和B設置隔離級別為已提交讀

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

客戶端A與B開啟事務并查詢student

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

客戶端A和B都為空

客服端B插入一條新的數據，不提交

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

客戶端A查看student

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

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

下面客戶端B提交事務

mysql> COMMIT;

客戶端A再次查看student表。

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

成功讀取到客戶

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

3、REPEATABLE READ（可重復讀）

解決已提交讀不可重復讀取的問題。

例：

客戶端A和B設置隔離級別為可重復讀

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

客戶端A與B開啟事務并查看

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

客服端B更新polo為tom，并提交事務

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

客戶端A查看student表

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

注意客戶端A查看數據未變，沒有不可重復讀問題

客戶端A提交事務，并查看student表

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

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

什么是幻讀？

首先，可重復讀鎖定范圍為當前查詢到的內容，如執行

mysql> SELECT * FROM student WHERE id>=1

鎖定的即id>=1查到的行，為行級鎖。如另一事務執行并默認提交以下語句

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

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

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

便出現了幻讀

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

4、SEAIALIZABLE（可串行化）

可串行化是最高隔離級別，強制事務串行執行。執行串行了也就解決了一切的問題，這個級別只有在對數據一致性要求非常嚴格且沒用并發的情況下使用

例：

客戶端A和B設置隔離級別為可串行化

mysql> SET SESSION tx_isolation='SERIALIZABLE';

客戶端A執行查詢

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

客戶端B執行新增

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

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