MySQL全面瓦解14：事務

翁智華發表於2020-12-16
MySql

關於事務

我們在資料庫中需要執行一個系列的操作的時候，要保證這個系列執行的連續性和完整性，要麼整個系列的執行都成功，要麼就全部失敗（只要有一個步驟失敗，其他均回滾到之前的狀態），

保證不會存在一部分成功一部分失敗的情況。這就是我們事務的職責。下面舉個分蘋果的例子：

A同學有3個蘋果，B同學有2個蘋果，如果A同學給一個蘋果給B同學，那麼A同學只剩下2個蘋果，而B同學有了3個。步驟如下

1 update tname  set apples=apples-1 where name = "A"；  
2 update tname  set apples=apples+1 where name = "B"；  

當然，這是理想情況下的結果。有可能會出錯：A同學減去了一個蘋果，然後執行B同學的加蘋果的時候，系統宕掉了，B同學沒有加成功，A同學的庫存中無厘頭少了一個蘋果。

這時候我們就需要事務支援了，有事務的情況下，就有兩種狀態，要麼都成功，要麼都失敗。

操作成功：A同學減去一個蘋果，B同學增加一個蘋果，最終A 2個，B 3個。

操作失敗：A同學依舊是3個蘋果，B同學依舊是2個蘋果。

事務有如下特性（參考官方描述）：

1、在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務。
2、事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
3、事務用來管理 insert,update,delete 語句。

事務的四個特性（ACID）

一般來說，衡量事務必須滿足四個特性：ACID，即 原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、永續性（Durability）。

原子性（Atomicity）：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

一致性（Consistency）：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。

隔離性（Isolation）：資料庫允許多個併發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括讀未提交（read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和序列化（Serializable），下面會詳細說明。

永續性（Durability）：事務處理結束後，對資料的修改就是永久的，會持久化到硬碟上，即便系統故障也不會丟失。

顯示和隱式事務

事務分為顯示事務和隱式事務，

隱式事務：事務自動開啟、提交或回滾，比如insert、update、delete語句，事務的開啟、提交或回滾由mysql內部自動控制的

顯示事務：事務需要手動開啟、提交或回滾，由開發者自己控制。 

自動提交

MySQL中事務預設是隱式事務（即自動提交（autocommit）模式為 ON），執行insert、update、delete操作的時候，資料庫自動開啟事務、提交或回滾事務。如下所示：

1 mysql> show variables like 'autocommit';
2 +---------------+-------+
3 | Variable_name | Value |
4 +---------------+-------+
5 | autocommit    | ON    |
6 +---------------+-------+
7 1 row in set 

在自動提交模式下，如果沒有start transaction顯式地開始一個事務，那麼每個sql語句都會被當做一個事務執行提交操作。

通過如下方式，可以關閉autocommit；需要注意的是，autocommit引數是針對連線的，在一個連線中修改了引數，不會對其他連線產生影響。如果你新開一個命令視窗，會恢復到預設值。

 1 mysql> set autocommit = 0;
 2 Query OK, 0 rows affected
 3 
 4 mysql> show variables like 'autocommit';
 5 +---------------+-------+
 6 | Variable_name | Value |
 7 +---------------+-------+
 8 | autocommit    | OFF   |
 9 +---------------+-------+
10 1 row in set 

如果關閉了autocommit，則所有的sql語句都在一個事務中，直到執行了commit或rollback，該事務結束，同時開始了另外一個事務。

特殊操作

在MySQL中，存在一些特殊的命令，如果在事務中執行了這些命令，會馬上強制執行commit提交事務；比如DDL語句(create table/drop table/alter table)。

不過，常用的select、insert、update和delete命令，都不會強制提交事務。

手動操作事務的兩種方式

改變預設事務提交策略

SET AUTOCOMMIT=0 禁止自動提交

SET AUTOCOMMIT=1 開啟自動提交

這個跟上面的指令碼大概一致，先設定為禁止自動提交事務，這樣執行的修改並沒有真正的到資料庫中，等commit 或者 rollback來最終確認，再手動進行事務操作。

1 --1、設定不自動提交事務
2 set autocommit=0;
3 --2、手動進行事務操作
4 commit;|rollback; 

提交示例：

 1 mysql> set autocommit = 0;
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into classes values(5,'初三五班');
 5 Query OK, 1 row affected
 6 
 7 mysql> commit; --提交操作
 8 Query OK, 0 rows affected
 9 
10 mysql> select * from classes;
11 +---------+-----------+
12 | classid | classname |
13 +---------+-----------+
14 |       1 | 初三一班  |
15 |       2 | 初三二班  |
16 |       3 | 初三三班  |
17 |       4 | 初三四班  |
18 |       5 | 初三五班  |
19 +---------+-----------+
20 5 rows in set 

回滾示例：

 1 mysql> set autocommit = 0;
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into classes values(6,'初三六班');
 5 Query OK, 1 row affected
 6 
 7 mysql> rollback; --回滾操作
 8 Query OK, 0 rows affected
 9 
10 mysql> select * from classes;
11 +---------+-----------+
12 | classid | classname |
13 +---------+-----------+
14 |       1 | 初三一班  |
15 |       2 | 初三二班  |
16 |       3 | 初三三班  |
17 |       4 | 初三四班  |
18 |       5 | 初三五班  |
19 +---------+-----------+
20 5 rows in set 

常規啟用事務

START|BEGIN 開始一個事務

ROLLBACK 事務回滾

COMMIT 事務確認

這是典型的MySQL事務操作，其中start transaction標識事務開始，commit提交事務，將執行結果寫入到資料庫。如果sql語句執行出現問題，會呼叫rollback，回滾所有已經執行成功的sql語句。

當然，也可以在事務中直接使用rollback語句進行回滾。：

1 start transaction; --1、開啟事務
2 /* 1條或者n條待執行的語句 */
3 commit;|rollback; --2、手動進行事務操作 

 提交示例：

 1 mysql> start transaction;
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into classes values(6,'初三六班');
 5 Query OK, 1 row affected
 6 
 7 mysql> commit;
 8 Query OK, 0 rows affected
 9 
10 mysql> select * from classes;
11  
12 +---------+-----------+
13 | classid | classname |
14 +---------+-----------+
15 |       1 | 初三一班  |
16 |       2 | 初三二班  |
17 |       3 | 初三三班  |
18 |       4 | 初三四班  |
19 |       5 | 初三五班  |
20 |       6 | 初三六班  |
21 +---------+-----------+
22 6 rows in set 

 回滾示例： 

 1 mysql> start transaction;
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into classes values(7,'初三七班');
 5 Query OK, 1 row affected
 6 
 7 mysql> rollback;
 8 Query OK, 0 rows affected
 9 
10 mysql> select * from classes; 
11 +---------+-----------+
12 | classid | classname |
13 +---------+-----------+
14 |       1 | 初三一班  |
15 |       2 | 初三二班  |
16 |       3 | 初三三班  |
17 |       4 | 初三四班  |
18 |       5 | 初三五班  |
19 |       6 | 初三六班  |
20 +---------+-----------+
21 6 rows in set 

事務儲存點的使用

事務儲存點（savepoint），指的是對事務執行過程中做位置儲存（類似我們打遊戲時的存檔點），如果你寫了一大堆的語句，但是有部分是你不想回滾的，想保留修改的狀態，但是部分是你想回滾的。

這時候使用savepoint是個不錯的方法。

 1 mysql> start transaction; --開啟事務
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into classes values(7,'初三七班');
 5 Query OK, 1 row affected
 6 
 7 mysql> savepoint point1; --注意：這邊設定了一個存檔點
 8 Query OK, 0 rows affected
 9 
10 mysql> insert into classes values(8,'初三八班');
11 Query OK, 1 row affected
12 
13 mysql> rollback to point1; --記住這個語法，回滾到存檔點，存檔點之後的語句就丟棄了
14 Query OK, 0 rows affected
15 
16 mysql> commit;
17 Query OK, 0 rows affected
18 
19 mysql> select * from  classes; --最後輸出，確實只有存檔點之前的成功了
20 +---------+-----------+
21 | classid | classname |
22 +---------+-----------+
23 |       1 | 初三一班  |
24 |       2 | 初三二班  |
25 |       3 | 初三三班  |
26 |       4 | 初三四班  |
27 |       5 | 初三五班  |
28 |       6 | 初三六班  |
29 |       7 | 初三七班  |
30 +---------+-----------+
31 7 rows in set 

這邊需要注意 savepoint 和 rollback to savepoint 的配合使用。

只讀事務的使用

表示在事務中執行的是一些只讀操作，如查詢，但是不會做insert、update、delete操作，資料庫內部對只讀事務可能會有一些效能上的優化。

1 start transaction read only; 

 再只讀操作的事務中進行增、刪、改操作會報錯，如下：

 1 mysql> start transaction read only;
 2 Query OK, 0 rows affected
 3 
 4 mysql> select * from  classes;
 5 +---------+-----------+
 6 | classid | classname |
 7 +---------+-----------+
 8 |       1 | 初三一班  |
 9 |       2 | 初三二班  |
10 |       3 | 初三三班  |
11 |       4 | 初三四班  |
12 |       5 | 初三五班  |
13 |       6 | 初三六班  |
14 |       7 | 初三七班  |
15 +---------+-----------+
16 7 rows in set
17 
18 mysql> insert into classes values(8,'初三八班');
19 1792 - Cannot execute statement in a READ ONLY transaction. --這邊報出異常

事務的髒讀、幻讀、不可重複讀

髒讀：讀取未提交資料

髒讀就是指當一個事務A正在訪問資料，並且對資料進行了修改，而這種修改還沒有提交到資料庫中，這時，另外一個事務B也訪問這個資料，然後使用了這個髒資料。舉個例子

時間順序 A事務 B事務

T1

 開始事務  

T2

   開始事務

T3

 查詢A同學有2個蘋果  

T4

給A同學增加一個蘋果（未提交）

  

T5

   查詢A同學有3個蘋果（讀髒資料）
T6 新增蘋果操作出現錯誤，回滾回2個蘋果  
 T7 提交事務  

 

 

不可重複讀：前後多次讀取資料不一致

不可重複讀指的是在事務A中先後多次讀取同一個資料，讀取的結果不一樣，因為另外一個事務也訪問該同一資料，並且可能修改這個資料，這種現象稱為不可重複讀。

髒讀與不可重複讀的區別在於：前者讀到的是其他事務未提交的資料，後者讀到的是其他事務已提交的資料。

時間順序 A事務 B事務

T1

 開始事務  

T2

   開始事務

T3

    查詢A同學有2個蘋果

T4

給A同學增加一個蘋果（未提交）

  

T5

  提交事務  
T6    查詢A同學有3個蘋果（不可重複讀）

 

按照正確邏輯，事務B前後兩次讀取到的資料應該一致，這邊一次讀到的是2個，一次讀到的是3個。

幻讀：前後多次讀取，資料總量不一致

在事務A中按照某個條件先後兩次查詢資料庫，兩次查詢結果的條數不同，這種現象稱為幻讀。不可重複讀與幻讀的區別可以通俗的理解為：前者是資料變了，後者是資料的行數變了。

時間順序 A事務 B事務

T1

 開始事務  開始事務

T2

   第一次查詢庫存資料有2條

T3

 給A同學增加一個蘋果（增加了一條庫存資料）  

T4

提交事務

  

T5

   第二次查詢庫存資料有3條


按照正確邏輯，按照正確邏輯，事務B前後兩次讀取到的資料總量應該一致

不可重複讀和幻讀的區別

（1）不可重複讀是讀取了其他事務更改的資料，針對update操作
解決：使用行級鎖，鎖定該行，事務A多次讀取操作完成後才釋放該鎖，這個時候才允許其他事務更改剛才的資料。

（2）幻讀是讀取了其他事務新增的資料，針對insert與delete操作
解決：使用表級鎖，鎖定整張表，事務A多次讀取資料總量之後才釋放該鎖，這個時候才允許其他事務新增資料。

幻讀和不可重複讀都是指的一個事務範圍內的操作受到其他事務的影響了。只不過幻讀是重點在插入和刪除，不可重複讀重點在修改

事務的隔離級別

SQL標準中事務的隔離性（Isolation）定義了四種隔離級別，並規定了每種隔離級別下上述幾個（髒讀、不可重複讀、幻讀）問題是否存在。

一般來說，隔離級別越低，系統開銷越低，可支援的併發越高，但隔離性也越差。隔離級別與讀問題的關係如下：

隔離級別的分類

注意：幻讀只會在 可重複讀 級別中才會出現，其他級別下不存在。

隔離級別 髒讀 不可重複讀 幻讀

讀未提交：Read Uncommitted

 ✔ ✔ x

讀已提交：Read Committed

 × ✔ x

可重複讀：Repeatable Read

 ×  x ✔

序列化：Serializable

×

  × ×

檢視|修改 隔離級別

檢視當前的隔離級別，預設應該都是可重複讀（Repeatable Read）：

1 mysql> show variables like 'transaction_isolation';
2 +-----------------------+-----------------+
3 | Variable_name         | Value           |
4 +-----------------------+-----------------+
5 | transaction_isolation | REPEATABLE-READ |
6 +-----------------------+-----------------+
7 1 row in set

修改隔離級別：

找到MySQL安裝目錄中的my.init檔案，會看到當前的隔離級別：REPEATABLE-READ。

1 # 隔離級別設定,READ-UNCOMMITTED讀未提交,READ-COMMITTED讀已提交,REPEATABLE-READ可重複讀,SERIALIZABLE序列
2 transaction-isolation=REPEATABLE-READ

 修改後重啟MySQL即可，下面的各項操作都是在修改了對應的隔離級別之後的操作。 

READ-UNCOMMITTED：讀未提交

事物A和事物B，事物B未提交的資料，事物A可以讀取到，這裡讀取到的資料叫做“髒資料”，這種隔離級別最低，一般理論上存在，資料庫隔離級別大都高於該級別。

舉個例子：學校新增加了7班和8班，教務主任進去錄入了8班的班級資訊，但是該事務並未提交，而8班班主任正好去檢視班級資訊，發現是存在的，摩拳擦掌準備錄入這個班級的學生資訊。可是教務主任發現班級資訊寫錯了，應該先錄入7班，於是迅速回滾了該事務。

最後8班的資訊消失了。出現上述情況，就是我們所說的髒讀 ，兩個併發的事務，“事務A：查詢班級資訊”、“事務B：錄入班級資訊”，事務A讀取了事務B尚未提交的資料。 

資料基礎：

 1 mysql> select * from classes;
 2 +---------+-----------+
 3 | classid | classname |
 4 +---------+-----------+
 5 |       1 | 初三一班  |
 6 |       2 | 初三二班  |
 7 |       3 | 初三三班  |
 8 |       4 | 初三四班  |
 9 |       5 | 初三五班  |
10 |       6 | 初三六班  |
11 |       7 | 初三七班  |
12 +---------+-----------+
13 7 rows in set

 

時間順序事務A事務B
T1 start transaction;  
T2 select * from classes;  
T3   start transaction;
T4   insert into classes values(8,'初三八班');
T5   select * from classes;
T6 select * from classes;  
T7   rollback;
T8 commit;  

說明:

事務A-T2：只有7條資料，事務A-T6：有8條資料，事務B-T6並未提交，此時事務A已經看到了事務B插入的資料，出現了髒讀。

事務A-T2：只有7條資料，T6-A：有8條資料，查詢到的結果不一樣，出現不可重複讀。

結論：讀未提交情況下，可以讀取到其他事務還未提交的資料，多次讀取結果不一樣，出現了髒讀、不可重複讀

READ-COMMITTED：讀已提交

事物A和事物B，事物B提交完的資料，事物A才能讀取到

這種隔離級別高於讀未提交：即對方事物提交之後的資料，我當前事物才能讀取到

這種隔離級別可以避免“髒資料” ，但會導致“不可重複讀取” 

資料基礎跟上面一樣

時間順序事務A事務B
T1 start transaction;  
T2 select * from classes;  
T3   start transaction; 
T4   insert into classes values(8,'初三八班');  
T5 select * from classes;  
T6   commit;
T7 select * from classes;  

說明:

事務A-T5：只有7條資料，A看不到B新增的第8條資料，說明沒有髒讀。

事務A-T5：只有7條資料，事務A-T7：讀到了8條資料，此時事務B已經提交了事務，事務A讀取到了事務B提交的資料，說明可以讀取到已提交的資料。

事務A-T5 和 事務A-T7：兩次讀取的資料結果不一樣，說明不可重複讀。

結論：讀已提交情況下，無法讀取到其他事務還未提交的資料，可以讀取到其他事務已經提交的資料，多次讀取結果不一樣，未出現髒讀，出現了讀已提交、不可重複讀。

REPEATABLE-READ：可重複讀

可重複讀是MySQL預設事務隔離級別

事務A和事務B，事務B提交之後的資料，事務A讀取不到，即對方提交之後的資料，還是讀取不到

事務B是可重複讀取資料，隔離級別高於讀已提交，這種隔離級別可以避免“不可重複讀取”，達到可重複讀取，但是可能導致“幻讀”

資料基礎：
 1 mysql> select * from classes;
 2 +---------+-----------+
 3 | classid | classname |
 4 +---------+-----------+
 5 |       1 | 初三一班  |
 6 |       2 | 初三二班  |
 7 |       3 | 初三三班  |
 8 |       4 | 初三四班  |
 9 |       5 | 初三五班  |
10 |       6 | 初三六班  |
11 |       7 | 初三七班  |
12 |       8 | 初三八班  |
13 +---------+-----------+
14 8 rows in set
時間順序事務A事務B
T1 start transaction;  
T2   start transaction;
T3   insert into classes values(9,'初三九班');
T4 select * from classes;  
T5   commit;
T6   select * from classes;
T7 select * from classes;  
T8 commit;  
T9 select * from classes;  

說明:

事務A-T4、事務A-T7：讀到的是八條資料，事務B-T6：有資料，A事務下讀不到B事務產生的資料，說明沒有髒讀。

事務A-T7：讀到的是9條資料，這時事務B已經commit，事務A看不到事務B已提交的資料，A事務下兩次讀的結果一樣，說明可重複讀。

事務A-T9：讀到的是9條資料。

結論：可重複讀情況下，未出現髒讀，未讀取到其他事務已提交的資料，多次讀取結果一致，即可重複讀。

SERIALIZABLE：序列

SERIALIZABLE會讓併發的事務序列執行。事務A和事務B，事務A在運算元據庫時，事務B只能排隊等待，這種隔離級別很少使用，吞吐量太低，使用者體驗差

這種級別可以避免“幻讀”，每一次讀取的都是資料庫中真實存在資料，事務A與事務B序列，而不併發

資料基礎同上

時間視窗A視窗B
T1 start transaction;  
T2 select * from classes;  
T3   start transaction;
T4   insert into classes values(9,'初三九班');
T5 select * from classes;  
T6 commit;  
T7   commit;

按時間順序執行上面的命令，會發現事務B-T4這樣會被阻塞，直到事務A執行完畢T6步驟。

可以看出來，事務只能序列執行了。序列情況下不存在髒讀、不可重複讀、幻讀的問題了。

隔離級別選擇注意點

1、讀已提交（READ-COMMITTED）通常用的比較多，也是MySQL預設選項。

2、具體選擇哪種需要結合具體的業務來選擇，隔離級別越高，併發性也低，比如最高階別SERIALIZABLE會讓事物序列執行，併發操作變成序列了，會導致系統效能直接降低

事務典型用法

 1 DROP PROCEDURE IF EXISTS t_test;  
 2 DELIMITER //  
 3 CREATE PROCEDURE t_test()  
 4   BEGIN  
 5     DECLARE t_error INTEGER;  
 6     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;  
 7     START TRANSACTION;  
 8          insert into students(studentname,score,classid) VALUE('A',99,3);
 9          insert into students(studentname,score,classid) VALUE('A','lala',3);  --這邊執行錯誤，兩個語句都會被回滾
10          IF t_error = 1 THEN  
11              ROLLBACK;  -- 有錯誤回滾
12          ELSE  
13              COMMIT;  --沒錯誤提交
14          END IF;  
15 END //DELIMITER; 
16 CALL t_test();

 

 這個是典型的用法，score是decimal型別，這兩個語句都會被回滾。

MVCC瞭解

RR解決髒讀、不可重複讀、幻讀等問題，使用的是MVCC（Multi-Version Concurrency Control）協議，即多版本的併發控制協議。

有多個請求來讀取表中的資料時可以不採取任何操作，但是多個請求裡有讀請求，又有修改請求時必須有一種措施來進行併發控制。不然很有可能會造成不一致。
讀寫鎖，解決上述問題很簡單，只需用兩種鎖的組合來對讀寫請求進行控制即可，這兩種鎖被稱為：

1、共享鎖(shared lock),又叫做 讀鎖

讀鎖是可以共享的，或者說多個讀請求可以共享一把鎖讀資料，不會造成阻塞。

2、排他鎖(exclusive lock),又叫做 寫鎖

寫鎖會排斥其他所有獲取鎖的請求，一直阻塞，直到寫入完成釋放鎖。 

通過讀寫鎖，可以做到讀讀可以並行，但是不能做到寫讀，寫寫並行。這邊瞭解一下，後續專門一篇來說明MVCC的原理和實現機制分析。 

總結

1、認識ACID（原子性、一致性、隔離性、永續性）特性及其實現原理

2、瞭解事務的髒讀、幻讀、不可重複讀

3、瞭解事務的隔離級別以及原理

相關文章