程式員需了解的MYSQL事務控制（八）

文章目錄

• 前言
• 一、事務控制
• • START TRANSACTION 例子
  • COMMIT AND CHAIN
  • SAVE POINT 與ROLL BACK
• 二、事務隔離級别

前言

資料庫事務是一個很重要的概念，日常開發中如何保證資料的準确性是一個很大的學問。MYSQL支援的事務的實作依賴于其ACID特性。ACID是什麼？即

原子性

、

一緻性

、

隔離性

、

持久性

。

事務名稱	解釋
原子性（Atomicity）	事務是一個原子操作，原子操作簡單了解指的是這個操作要麼全部成功，要麼全部失敗
一緻性(Consistency)	事務無論成功與否，資料庫必須保證所處的資料不應被破壞,舉個例子:A給B無論成功或失敗轉賬，那麼A的錢+B的錢前後應該總和相等
隔離性（Isolation）	同一份的資料可能有很多事務進行操作，是以要将各種事務隔離開，防止資料被損壞
持久性（Durability）	事務如果一旦完成，結果都應不變，因為這樣無論系統發送了什麼錯誤，都能進行資料恢複

一、事務控制

MYSQL通過SET AUTOCOMMIT、START TRANSACTION、COMMIT、ROLLBACK 等語句支援事務，MYSQL的事務預設是自動送出的。可以通過語句查詢目前MYSQL的事務資訊：

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 
           

也可以手動開啟事務、送出事務以及復原事務，下面介紹事務控制語句詳情：

• START TRANSACTION 開啟新事務
• COMMIT 和ROLLBACK用于送出和復原事務
• SET AUTOCOMMIT 用于設定是否開啟事務
• CHAIN 出現目前事務送出後，會立即重新開機新的事務并保持相同的隔離級别。
• RELEASE 表示送出事務後會立即斷開與用戶端的連接配接。

下面以實操感受一下資料庫事務特性，首先建立一個資料庫表如下：

　mysql> create table tb_user 
 　(id int primary key auto_increment,
 　name varchar(125) not null,
 　age int not null);
Query OK, 0 rows affected (0.04 sec)
           

START TRANSACTION 例子

回話1	回話2
查詢tb_user表id為1的記錄 mysql> select * from tb_user where id =1; Empty set (0.00 sec)	查詢tb_user表id為1的記錄 mysql> select * from tb_user where id =1; Empty set (0.00 sec)
用START TRANSACTION開啟一個事務并插入一條記錄，沒有進行事務commit mysql> insert into tb_user(name,age) values(‘Michael’,23) ; Query OK, 1 row affected (0.00 sec)	再次查詢表id為1的記錄依然為空 mysql> select * from tb_user where id =1; Empty set (0.00 sec)
事務送出commit	再次查詢表後可以查到結果: mysql> select * from tb_user where id=1;
直接插入一條資料，這個事務是自動送出的 mysql> insert into tb_user(name,age) values(‘John’,24) ; Query OK, 1 row affected (0.00 sec)	可以直接查詢剛剛回話一添加的記錄 mysql> select * from tb_user where id=2;

COMMIT AND CHAIN

目前事務送出并開啟一個新的事務，保持與目前事務同一個事務隔離水準。

會話1	會話2
重新開啟一個事務并在向表中插入一條資料 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb_user(name,age) values(‘T-bag’,25); Query OK, 1 row affected (0.00 sec) 然後用commit and chain指令進行送出 mysql> commit and chain;Query OK, 0 rows affected (0.00 sec)	然後在會話2中無法查詢到會話1中插入的資料
在此用commit指令送出事務	然後在此查詢可以看到會話一中新插入的資料 mysql> select * from tb_user where id =3;

SAVE POINT 與ROLL BACK

SAVE POINT可以指定復原事務的一部分，可以定義不同的SAVE POINT滿足不同條件下復原不同的事務，SAVE PONIT 一般不能重名，因為這會造成後定義的SAVE PONIT會覆寫之前同名的SAVE POINT，如果不在使用SAVE PONIT可以通過RELEASE SAVE PONT指令進行删除，一旦被删除就無法在進行事務復原。

會話1	會話2
啟動一個新的事務，并向表中插入新的資料 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb_user(name,age) values(‘Sara’,22); Query OK, 1 row affected (0.00 sec)	會話2中無法查詢到name為Sara的記錄 mysql> select * from tb_user where name=‘Sara’; Empty set (0.00 sec)
會話1中是可以查到此條記錄的
此處定義SAVE POINT并命名 transaction1 mysql> savepoint transaction1; Query OK, 0 rows affected (0.00 sec)
此處在此插入資料 mysql> insert into tb_user(name,age) values(‘Bellick’,32); Query OK, 1 row affected (0.00 sec)	會話2中無法查詢到name為Sara和name為Bellick的兩條記錄
會話1中可以查詢name為Sara和name為Bellick記錄 mysql> select * from tb_user where name=‘Sara’ or name=‘Bellick’;
復原事務點 transaction1 mysql> rollback to savepoint transaction1; Query OK, 0 rows affected (0.00 sec)
在此查詢name為Sara和name為Bellick的記錄，發現隻能查詢到Sara的記錄
commit送出事務	會話2中可以查詢到name為Sara的記錄

上面我們驗證了savepoint的使用方法，接下來我們示範其出現的覆寫問題，以及删除savepoint的用法如下：

## 開啟新事務
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
## 插入資料
mysql> insert into tb_user(name,age) values('Bellick',32);
Query OK, 1 row affected (0.00 sec)
## 生成savepoint
mysql> savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
           

## 插入新的資料
mysql> insert into tb_user(name,age) values('Veronica',21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 4 | Sara     |  22 |
| 5 | Bellick  |  32 |
| 6 | Veronica |  21 |
+----+----------+-----+
6 rows in set (0.00 sec)
## 復原事務
mysql> rollback to savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
## 在此查詢
mysql> select * from tb_user;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Michael |  23 |
|  2 | John    |  24 |
|  3 | T-bag   |  25 |
| 4 | Sara    |  22 |
| 5 | Bellick |  32 |
+----+---------+-----+
5 rows in set (0.00 sec)
           

## 重新在插入資料
mysql> insert into tb_user(name,age) values('Veronica',21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 4 | Sara     |  22 |
| 5 | Bellick  |  32 |
| 6 | Veronica |  21 |
+----+----------+-----+
6 rows in set (0.00 sec)

mysql> savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
## 再次插入資料并復原
mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 4 | Sara     |  22 |
| 5 | Bellick  |  32 |
| 6 | Veronica |  21 |
| 7 | Fernando |  24 |
+----+----------+-----+
7 rows in set (0.00 sec)
           

## 復原事務
mysql> rollback to savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
## 查詢發現兩次復原同一個名稱為transaction1的savepoint發現查詢結果并不一樣
mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 11 | Sara     |  22 |
| 12 | Bellick  |  32 |
| 14 | Veronica |  21 |
+----+----------+-----+
6 rows in set (0.00 sec)

## 删除savepoint
mysql> release savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
           

二、事務隔離級别

隔離級别	描述
READ_UNCOMMITED	允許事務讀取其他事務未送出的資料，不可重複讀、髒讀、幻讀将會出現
READ_COMMITED	隻允許事務讀取其他事務已送出的資料，可以避免髒讀，但不可重複讀以及幻讀依然存在
REPEATABLE_READ	確定事務可以多次對同一字段讀取是同一個值，該事務持續期間禁止其他事務進行更新，可以避免髒讀、不可重複讀，但仍然存在幻讀的可能性
SERIALIZABLE	確定事務可以多次讀取同一行的值，事務操作期間不允許其他事務進行更新、删除、添加操作，可以避免上述并發的問題，但問題是效率低下

• 事務的隔離級别

典型的事務隔離不同所造成問題如下：

1. 髒讀

   ：髒讀發送在A事務讀取B事務已經改寫但是還未送出的資料,若此時B事務復原了，那麼A事務擷取就是髒資料

2. 不可重複讀

   ：不可重複讀發送在當A事務執行2次查詢，每一次擷取的資料結果都不相同，這是由于B事務在A事務2次查詢期間進行了更新

3. 幻讀

   ： 幻讀發送在當A事務讀取了幾行資料，緊接着B事務進行輸入的插入，在随後的查詢中A事務就會讀了原本不存在的記錄

   ⚠️ 不可重複讀特指修改的記錄，而幻讀指的是新增或删除的記錄

• 資料庫預設隔離級别

  MYSQL預設隔離級别

  REPEATABLE_READ

  ,ORACLE 預設隔離級别

  READ_COMMITED

  。