MySQL 數據庫服務事務知識

數據庫存儲事務機制概念

事務(Transaction)可以更通俗的理解為交易，所以事務會伴隨著交易類的業務類型出現的概念（工作模式）；

現實生活中存在很多的交易行為，比如：物換物的等價交換、貨幣換物的等價交換、虛擬貨幣換物(虛擬物品)的等價交換；

因此就需要考慮如何保證現實生活中交易過程的和諧，一般會有法律、道德等方面規則進行約束；

而在數據庫服務中為了保證線上交易的"和諧"，便加入了"事務"工作機制

數據庫存儲事務機制特性

在數據庫服務中引入事務機制概念，主要是為了應用事務機制的相關特性處理安全一致性問題，其中事務機制主要包含的特性有：

特性一：原子性（Atomicity）

原子性表示一個事務生命周期中的DML語句，要么全成功要么全失敗，不可以出現中間狀態；

語句要么全執行，要么全不執行，是事務最核心的特性，事務本身就是以原子性來定義的；實現主要基于undo log

Begin：DML01 DML02 DML03 Commit;

特性二：一致性（Consistency）

一致性表示一個事務發生前、中、后，數據都最終保持一致，即讀和寫都要保證一致性；

事務追求的最終目標，一致性的實現既需要數據庫層面的保障，也需要應用層面的保障；

CR + Double write

特性三：隔離性（Isolation）

隔離性表示一個事務操作數據行的時候，不會受到其他事務的影響，主要利用鎖機制來保證隔離性；

特性四：持久性（Durability）

持久性表示一旦事務進行了提交，即可永久生效（落盤）

保證事務提交后不會因為宕機等原因導致數據丟失；實現主要基于redo log

事務ACID相關知識官方說明：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

數據庫存儲事務生命周期

在運用事務機制完成相關工作任務時，對于事務使用是存在生命周期概念的，標準顯示的事務生命周期控制語句有：

-- 開啟事務機制
begin;
start transaction;

-- 提交事務任務
commit;

-- 回滾事務操作
rollback;

說明：事務生命周期中，只能使用DML語句，其中包括：select、update、delete、insert；DDL語句會隱式進行提交

事務的生命周期操作演示：

# 進行測試數據庫查詢數據
mysql> use world;
mysql> select * from city limit 10;

# 進行測試數據庫數據撤銷修改
mysql> begin;
mysql> update city set population=10 where id=1;
mysql> update city set population=10 where id=2;
-- 由于是采用事務進行的修改，所以只是在內存層面進行的修改，并沒有對磁盤上的數據進行修改；
mysql> select * from city limit 10;
-- 由于是采用事務進行的修改，此時看到的數據信息只是內存層面的修改信息
mysql> rollback;
-- 由于是采用事務進行的撤銷，會讀取undo文件信息，將事務操作撤回到事務開始前的狀態
mysql> select * from city limit 10;
-- 由于是采用事務進行的修改，當撤銷操作執行完，看到數據信息還是原來的；

# 進行測試數據庫數據永久修改
mysql> begin;
mysql> update city set population=10 where id=1;
mysql> update city set population=10 where id=2;
-- 由于是采用事務進行的修改，所以只是在內存層面進行的修改，并沒有對磁盤上的數據進行修改；
mysql> select * from city limit 10;
-- 由于是采用事務進行的修改，此時看到的數據信息只是內存層面的修改信息
mysql> commit;
-- 由于是采用事務進行的提交，會加載redo文件信息，將事務內存層面的修改同步到磁盤中（完成了D特性）
mysql> select * from city limit 10;
-- 由于是采用事務進行的修改，當執行操作執行完，看到數據信息將永久保存下載；

數據庫存儲事務提交方式

方式一：在事務生命周期管理過程中，事務的提交機制可以采用自動提交方式（auto_commit）

事務自動提交方式作用說明：

事務自動提交表示在沒有顯示的使用begin語句的時候，執行DML操作語句時，會在DML操作語句前自動添加begin；

并在DML操作語句執行后自動添加commit；

在生產環境中，若處于頻繁事務業務場景中，建議關閉autocommit自動提交功能，或者每次事務執行的時候；

都進行顯示的執行begin和commit

事務自動提交方式參數信息：

mysql> select @@autocommit;
+---------------------+
| @@autocommit |
+---------------------+
|                          1 |
+---------------------+
1 row in set (0.00 sec)
-- 在事務自動提交功能設置修改時，設置為1表示開啟自動提交，設置為0表示關閉自動提交

事務自動提交方式參數修改：

# 臨時關閉事務自動提交功能
mysql> set global autocommit=0;
-- 配置調整后，重新登錄mysql數據庫生效

# 永久關閉事務自動提交功能
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0
-- 配置調整后，重新啟動mysql數據庫生效

事務自動提交方式設置方式優點缺點說明：

序號	參數配置	優劣勢
情況1	autocommit=0 關閉事務自動提交	優勢：可以編寫多個關聯的DML，進行一次性提交操作，若出現異常可以回滾 符合原子特性
		劣勢：可能出現多個關聯的DML，只是完成了部分操作，這時就可能等待狀態 基于隔離特性，操作的數據表或數據行就會進入鎖定狀態
情況2	autocommit=1 開啟事務自動提交	優勢：可以出現多個關聯的DML，逐行操作自動提交，就可以不用處于鎖等待狀態
		劣勢：可能出現多個關聯的DML，，每執行一條就進行提交，會造成多個語句執行不符合原子性

方式二：在事務生命周期管理過程中，事務的提交機制可以采用隱式提交方式：

在進行事務操作時，需要注意操作語句必須都是DML語句，如果中間插入了DDL語句，也會造成之前的事務操作自動提交；

begin; DML1; DML2; DDL1; COMMIT; DML3; COMMIT;
-- 這種情況出現會破壞原本事務的原子性

隱式自動提交方式語句：

在出現隱式自動提交時，可能導致提交的非事務語句有：

序號	語句類型	涉及命令
01	DDL語句類型	alter、create、drop
02	DCL語句類型	grant、revoke、set password
03	鎖定語句類型	lock tables、unlock tables
04	其他語句類型	truncate table、load data infile、select for update

說明：在多個數據庫會話窗口中，A窗口的所有事務性DML操作，不會受到B窗口的非事務語句影響，同一會話窗口會有影響；

隱式自動回滾情況分析：

• 情況一：在事務操作過程中，會話窗口自動關閉了，會進行隱式自動回滾；
• 情況二：在事務操作過程中，數據庫服務被停止了，會進行隱式自動回滾；
• 情況三：在事務操作過程中，出現事務沖突死鎖了，會進行隱式自動回滾；

數據庫存儲事務隔離級別

數據庫事務隔離級別主要作用是實現事務工作期間，數據庫操作讀的隔離特性，所謂讀的操作就是將數據頁可以調取到內存；

然后可以讀取數據頁中相應數據行的能力，并且不同事務之間的數據頁讀操作相互隔離；

可以簡單理解為：一個事務在對數據頁中數據行做更新操作時，在沒有更新提交前，另一個事務此時是不能讀取數據頁中數據行內容的；

對于數據庫存儲事務隔離級別包括4種，可以通過操作命令查看獲取當前使用的隔離級別:

mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation  |
+---------------------------------+
| REPEATABLE-READ              |
+---------------------------------+
1 row in set (0.00 sec)

常用的事務隔離級別類型：

類型一：RU（READ-UNCOMMITTED 表示讀未提交）

可以讀取到事務未提交的數據，隔離性差，會出現臟讀（當前內存讀），不可重復讀，幻讀問題;

類型二：RC（READ-COMMITTED 表示讀已提交）可用

可以讀取到事務已提交的數據，隔離性一般，不會出現臟讀問題，但是會出現不可重復讀，幻讀問題；

類型三：RR（REPEATABLE-READ 表示可重復讀）默認

可以防止臟讀（當前內存讀），防止不可重復讀問題，防止會出現的幻讀問題，但是并發能力較差；

會使用next lock鎖進制，來防止幻讀問題，但是引入鎖進制后，鎖的代價會比較高，比較耗費CPU資源，占用系統性能；

類型四：SR（SERIALIZABLE 可串行化）

隔離性比較高，可以實現串行化讀取數據，但是事務的并發度就沒有了；

這是事務的最高級別，在每條讀的數據上，加上鎖，使之不可能相互沖突

事務隔離級別官方鏈接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

常用的事務隔離級別名詞：

在解釋分析說明相應的隔離級別名詞前，需要對數據庫事務隔離級別進行調整，以及關閉自動提交功能：

# 設置事務隔離級別
mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> set global transaction_isolation='REPEATABLE-READ';

# 查看事務隔離級別
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED         |
+---------------------------------+
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation  |
+---------------------------------+
| READ-COMMITTED               |
+---------------------------------+
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| REPEATABLE-READ             |
+---------------------------------+

# 臨時關閉自動提交功能：
mysql> set global autocommit=0;
mysql> select @@autocommit;
+---------------------+
| @@autocommit |
+---------------------+
|                          0 |
+---------------------+

創建隔離級別測試數據表：

mysql> use mydb
mysql> create table t1 (
    id int not null primary key auto_increment,
    a int not null,
    b varchar(20) not null,
    c varchar(20) not null
) charset=utf8mb4 engine=innodb;

mysql> begin;
mysql> insert into t1(a,b,c)
values
(5,'a','aa'),
(7,'c','ab'),
(10,'d','ae'),
(13,'g','ag'),
(14,'h','at'),
(16,'i','au'),
(20,'j','av'),
(22,'k','aw'),
(25,'l','ax'),
(27,'o','ay'),
(31,'p','az'),
(50,'x','aze'),
(60,'y','azb');
mysql> commit;
-- 確認兩個SQL會話窗口，即不同的事務查看的數據是否一致的；

• 名詞解讀分析一：臟讀

臟讀主要表示在一個事務窗口中，沒有數據修改提交操作前，另一個事務就可以看到內存中數據頁的修改；

簡單理解：在一個事務窗口中，可以讀取到別人沒有提交的數據信息；

利用隔離級別RU解讀：

# 設置事務隔離級別
mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED         |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新開啟兩個SQL會話窗口

# 數據庫A會話窗口操作
mysql> begin;
mysql> update t1 set a=10 where id=1;
-- 只是在內存層面進行數據頁中數據修改
mysql> rollback;
-- 進行事務回滾操作

# 數據庫B會話窗口操作
mysql> begin;
mysql> select * from t1 where id=1;
+----+----+---+----+
| id   | a   | b  | c   |
+----+----+---+----+
|  1   | 10 | a  | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在A會話窗口沒提交的事務修改，被B會話窗口查詢到了
mysql> select * from t1 where id=1;
+----+----+---+----+
| id   | a   | b  | c   |
+----+----+---+----+
|  1   | 5   | a  | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在A會話窗口進行回滾后，在B窗口查詢的數據又恢復了

• 名詞解讀分析二：不可重復讀

不可重復讀表示在一個事務中，利用相同的語句多次查詢，獲取的數據信息是不同的；

利用隔離級別RU解讀：

# 數據庫B會話窗口操作
mysql> begin;
mysql> select * from t1 where id=1;
+----+----+---+----+
| id   | a   | b  | c   |
+----+----+---+----+
|  1   | 10 | a  | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在B會話事務窗口進行數據第一次查詢看到數據信息：a=10
mysql> select * from t1 where id=1;
+----+----+---+----+
| id   | a   | b  | c   |
+----+----+---+----+
|  1   | 5   | a  | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在B會話事務窗口進行數據第二次查詢看到數據信息：a=5

利用隔離級別RC解讀：

# 設置事務隔離級別
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| READ-COMMITTED              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新開啟兩個SQL會話窗口

# 數據庫A會話窗口操作
mysql> use mydb;
mysql> begin;
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事務查詢信息 = B窗口事務查詢信息
mysql> update t1 set a=10 where id=1;
-- A窗口事務進行修改
mysql> commit;
-- A窗口事務進行提交

# 數據庫B會話窗口操作
mysql> use mydb;
mysql> begin;
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事務查詢信息 = B窗口事務查詢信息
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- B窗口事務查詢信息，不能看到A窗口事務未提交的數據變化，避免了臟數據問題；
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 10 | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事務提交之后，B窗口事務查詢信息和之前不同了

利用隔離級別RR解讀：

# 設置事務隔離級別
mysql> set global transaction_isolation='REPEATABLE-READ';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| REPEATABLE-READ              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新開啟兩個SQL會話窗口

# 數據庫A會話窗口操作
mysql> use mydb;
mysql> begin;
mysql> select * from t1;
-- 確認初始數據信息
mysql> update t1 set a=10 where id=1;
-- A窗口事務進行修改
mysql> commit;
-- A窗口事務進行提交

# 數據庫B會話窗口操作
mysql> use mydb;
mysql> begin;
mysql> select * from t1;
-- 確認初始數據信息
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- B窗口事務查詢信息，不能看到A窗口事務未提交的數據變化，避免了臟數據問題；
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事務提交之后，B窗口事務查詢信息和之前是相同的；
-- 在RR級別狀態下，同一窗口的事務生命周期下，每次讀取相同數據信息是一樣，避免了不可重復讀問題
mysql> commit;
mysql> select * from t1 where id=1;
-- 在RR級別狀態下，同一窗口的事務生命周期結束后，看到的數據信息就是修改的了

• 名詞解讀分析三：幻讀

利用隔離級別RC解讀：

# 設置事務隔離級別
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| READ-COMMITTED              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新開啟兩個SQL會話窗口

# 數據庫A會話窗口操作（重新進入）
mysql> use mydb;
mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 | 10 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 | 14 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)
-- 查看獲取A窗口表中數據
mysql> alter table t1 add index idx(a);
-- 在A窗口中，添加t1表的a列為索引信息
mysql> begin;
-- 在A窗口和B窗口中，同時做開始事務操作；
mysql> update t1 set a=20 where a<20;
-- 在A窗口中，將a<20的信息均調整為20
mysql> commit;
-- 在A窗口中，進行事務提交操作，是在B窗口事務沒有提交前
mysql> mysql> select * from t1;
-- 在A窗口中，查看數據信息，希望看到的a是沒有小于20的，但是結果看到了a存在等于10的（即出現了幻讀）

# 數據庫B會話窗口操作（重新進入）
mysql> use mydb;
mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 | 10 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 | 14 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)
-- 查看獲取B窗口表中數據
mysql> begin;
mysql> insert into t1(a,b,c) values(10,'A','B')
-- 在B窗口中，插入一條新的數據信息 a=10 
mysql> commit;
-- 在B窗口中，進行事務提交操作

利用隔離級別RR解讀：

# 設置事務隔離級別
mysql> set global transaction_isolation='REPEATABLE-READ';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| REPEATABLE-READ              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新開啟兩個SQL會話窗口

# 數據庫A會話窗口操作
mysql> use mydb;
mysql> select * from t1;
-- 查看獲取A窗口表中數據
mysql> alter table t1 add index idx(a);
-- 在A窗口中，添加t1表的a列為索引信息
mysql> begin;
mysql> update t1 set a=20 where a>20;
-- 在A窗口中，將a>20的信息均調整為20

# 數據庫B會話窗口操作
mysql> use mydb;
mysql> select * from t1;
-- 查看獲取B窗口表中數據
mysql> begin;
mysql> insert into t1(a,b,c) values(30,'sss','bbb');
-- 在B窗口中，插入一條新的數據信息 a=30，但是語句執行時會被阻塞，沒有反應；
mysql> show processlist;
-- 在C窗口中，查看數據庫連接會話信息，insert語句在執行，等待語句超時（默認超時時間是50s）
-- 因為此時在RR機制下，創建了行級鎖(阻塞修改)+間隙鎖(阻塞區域間信息插入)=next lock
-- 區域間隙鎖 < 左閉右開(可用臨界值)  ;  區域間隙鎖 > 左開右閉（不可用臨界值）

事務隔離機制知識點補充：

提到事務肯定不陌生，和數據庫打交道的時候，總是會用到事務。

最經典的例子就是轉賬，你要給朋友小王轉 100 塊錢，而此時你的銀行卡只有 100 塊錢。

轉賬過程具體到程序里會有一系列的操作，比如查詢余額、做加減法、更新余額等，這些操作必須保證是一體的；

不然等程序查完之后，還沒做減法之前，你這100塊錢，完全可以借著這個時間差再查一次，然后再給另外一個朋友轉賬，

如果銀行這么整，不就亂了么？這時就要用到“事務”這個概念了。

簡單來說，事務就是要保證一組數據庫操作，要么全部成功，要么全部失敗。

在 MySQL 中，事務支持是在引擎層實現的。MySQL 是一個支持多引擎的系統，但并不是所有的引擎都支持事務。

比如 MySQL 原生的 MyISAM 引擎就不支持事務，這也是 MyISAM 被 InnoDB 取代的重要原因之一。

下面將會以 InnoDB 為例，剖析 MySQL 在事務支持方面的特定實現，并基于原理給出相應的實踐建議，希望這些案例能加深你對 MySQL 事務原理的理解。

隔離性與隔離級別

提到事務，肯定會想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、持久性），

我們就來說說其中 I，也就是“隔離性”。

當數據庫上有多個事務同時執行的時候，就可能出現以下問題：

• 臟讀（dirty read）
• 不可重復讀（non-repeatable read）
• 幻讀（phantom read）

為了解決這些問題，就有了“隔離級別”的概念。在談隔離級別之前，首先要知道，隔離得越嚴實，效率就會越低。

因此很多時候，都要在二者之間尋找一個平衡點。SQL 標準的事務隔離級別包括：

隔離級別	英文描述	解釋說明
讀未提交	RU-read uncommitted	一個事務還沒提交時，它做的變更就能被別的事務看到。
讀提交	RC-read committed	一個事務提交之后，它做的變更才會被其他事務看到。
可重復讀	RR-repeatable read	一個事務執行過程中看到的數據，總是跟這個事務在啟動時看到的數據是一致的。 當然在可重復讀隔離級別下，未提交變更對其他事務也是不可見的。
串行化	serializable	顧名思義是對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。 當出現讀寫鎖沖突的時候，后訪問的事務必須等前一個事務執行完成，才能繼續執行。

其中“讀提交”和“可重復讀”比較難理解，所以我用一個例子說明這幾種隔離級別。

假設數據表 T 中只有一列，其中一行的值為 1，下面是按照時間順序執行兩個事務的行為。

mysql> create table T(c int) engine=InnoDB;
mysql> insert into T(c) values(1);

兩個事務操作行為：

事務行為順序	事務A	事務B
01	啟動事務；查詢得到值1	啟動事務
02		查詢得到值1
03		將1改為2
04	查詢得到值v1
05		提交事務B
06	查詢得到值v2
07	提交事務A
08	查詢得到值v3

在不同的隔離級別下，事務 A 會有哪些不同的返回結果，也就是圖里面 V1、V2、V3 的返回值分別是什么。

• 若隔離級別是“讀未提交”：

則 V1 的值就是 2，事務 B 雖然還沒有提交，但是結果已經被 A 看到了。因此，V2、V3 也都是 2。

• 若隔離級別是“讀提交”：

則 V1 是 1，V2 的值是 2，事務 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

• 若隔離級別是“可重復讀”：

則 V1、V2 是 1，V3 是 2，之所以 V2 還是 1，遵循的就是這個要求：事務在執行期間看到的數據前后必須是一致的。

• 若隔離級別是“串行化”：

則在事務 B 執行“將 1 改成 2”的時候，會被鎖住。直到事務 A 提交后，事務 B 才可以繼續執行。

所以從 A 的角度看， V1、V2 值是 1，V3 的值是 2。

數據庫存儲事務工作流程

根據存儲事務的工作流程原理，來了解如何保證事務的ACID特性，利用了MySQL數據庫的哪些工作機制；

事務工作流程名字解釋：

• 名詞解釋一：redo log-Disk

表示重做日志，當出現異常情況，內存中數據直接寫入磁盤失敗時，可以通過重啟數據庫服務，讀取此文件修復數據信息；

文件存儲表項為：ib_logfile0~N 默認48M，輪詢使用

• 名詞解釋二：redo log buffer-mem

表示重做日志生成緩沖區，相當于redo log的內存區域。redo log文件與redo log buffer是有IO關系的；

事務修改提交后：redo log buffer -> redo log，表示寫入數據到redo log；

事務操作恢復時：redo log -> redo log buffer，表示讀取數據從redo log；

• 名詞解釋三：tablespace file-disk

表示存儲表數據行和索引等信息的文件，含有表空間所有數據文件；ibd

• 名詞解釋四：Innodb buffer pool-mem

表示數據緩沖區，主要用于緩沖事務要處理的數據和索引信息，tablespace文件與buffer pool是有IO關系的；

• 名詞解釋五：LSN

表示日志序列號，在buffer pool中有數據頁信息的變化就會記錄到redo log buffer中，主要記錄變化了多少字節量；

利用LSN記錄相應數據頁的變化量(LSN+變化字節量)，也可以理解為記錄的是日志量的變化；

MySQL每次數據庫啟動，都會比較磁盤數據頁和redolog的LSN，必須要求兩者一致，數據庫才能正常啟動；

• 名詞解釋六：WAL（Write Ahead Log）

表示redo日志生成記錄優先于數據頁寫入到磁盤的過程，并且是支持預寫入機制（group commit）的；

• 名詞解釋七：Dirty page

表示在內存進行修改的數據頁，在redo buffer中會記錄數據頁的數據量的變化，此時在數據頁還未最終寫入到磁盤中時；

就稱之為臟頁，所以一般所謂的臟讀就是讀取臟頁的數據頁信息；

• 名詞解釋八：CheckPoint

表示為檢查點，就是將臟頁刷寫到磁盤的動作；

• 名詞解釋九：DB_TRX_ID（6字節）

表示為事務ID號，InnoDB會為每一個事務生成一個事務號（由事務管理器管理TM），伴隨著整個事務生命周期

其中事務ID號碼信息，在redo和undo日志文件中都會有相應的標識；

• 名詞解釋十：DB_ROLL_PTR（7字節）

表示回滾指針，在rollback時會使用undo日志回滾已修改的數據，DB_ROLL_PTR會指向此次事務的回滾業務點；

從而找到undo上的相應的日志信息；

數據庫名詞解釋官方參考：https://dev.mysql.com/doc/refman/8.0/en/glossary.html

事務工作流程具體解讀：

簡單事務情況舉例：

mysql> begin;
mysql> update t1 set A=2 where A=1;
mysql> commit;

事務工作流程一：redo log 重做日志如何應用

• 用戶發起update操作事務語句，將磁盤數據頁（page100，A=1，LSN=1000）加載到內存（buffer_pool）緩沖區；

• 將在內存中發生數據頁修改操作（A=1改為A=2），形成數據頁臟頁，更改中數據頁的變化會記錄到redo buffer中；

  加入1000個字節日志信息，LSN=1000+1000=2000;

• 當執行事務提交操作的時候，基于WAL機制，等到redo buffer中的日志完全落盤到ib_logfileN-redo log中，即commit正式完成；

• 此時ib_logfileN中記錄了一條日志，內容為：page100數據頁變化+LSN=2000

簡單理解：記錄內存數據頁變化日志+undo（DB_TRX_ID，DB_ROLL_PTR），通過LSN和數據頁建立關系

特殊情景分析：當此時，redo落盤了，數據頁沒有落盤，出現宕機情況了；

• MySQL CR（自動故障恢復）工作模式，啟動數據庫時，自動檢查redo的LSN和數據頁LSN；
• 如果發現redo LSN > 數據頁的LSN，加載原始數據頁+變化redo指定內存，使用redo重構臟頁（前滾）；
• 如果確認此次事務已經提交（commit標簽），立即觸發CKPT（checkpoint）動作，將臟頁刷寫到磁盤上；

知識點補充：

MySQL有一種機制，批量刷寫redo的機制：會在A事務commit時，順便將redo buffer中的未提交的redo日志也一并刷到磁盤；

為了區分不同狀態的redo，日志記錄時會標記是否commit；

redo保證了ACID哪些特性：

主要保證了D的特性，另外A C也有間接關聯；

Redo Log日志文件生成流程：

Redo Log日志文件應用流程：

說明：利用redo Log重做日志功能可以保證事務的D特性，基于可以丟內存數據，但是不可以丟操作事務日志的原則；

存儲引擎讀寫磁盤數據頁IO信息：

mysql> select @@innodb_read_io_threads;
+------------------------------------+
| @@innodb_read_io_threads |
+------------------------------------+
|                                                4 |
+------------------------------------+
1 row in set (0.00 sec)
-- 接收SQL層處理信息傳達到存儲引擎層的讀IO配置信息；

mysql> select @@innodb_write_io_threads;
+------------------------------------+
| @@innodb_write_io_threads |
+------------------------------------+
|                                                 4 |
+------------------------------------+
1 row in set (0.00 sec)
-- 接收SQL層處理信息傳導到存儲引擎層的寫IO配置信息

存儲引擎序號號碼信息查看：

mysql> show engine innodb status\G
Log sequence number                   105377511
-- redo buffer中的SN號碼信息
Log flushed up to                           105377511
-- redo buffer刷新到磁盤上的SN號碼信息
Last checkpoint at                         105377511
-- 磁盤數據頁的SN號碼信息

存儲引擎redo buffer落盤的機制策略：****

mysql> select @@innodb_flush_log_at_trx_commit;
+-----------------------------------------------+
| @@innodb_flush_log_at_trx_commit |
+-----------------------------------------------+
|                                                               1 |
+-----------------------------------------------+
1 row in set (0.00 sec)
-- 表示數據庫配置與安全有關的兩個雙一配置
-- 當數值為1：表示每次事務提交就立刻進行redo buffer刷新落盤，若落盤不成功，則commit命令操作也不會成功；默認
-- 當數值為0：表示日志緩存信息寫入磁盤是按照每秒種進行一次操作，未刷新日志的事務可能會在崩潰中丟失；不安全
-- 當數值為2：表示在事務提交后先生成日志緩存信息，然后再按照每秒鐘進行一次寫入磁盤操作；不安全
-- 參考官方鏈接：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html

事務工作流程二：undo log 回滾日志如何應用

• 事務發生數據頁修改之前，會申請一個undo事務操作，保存了事務回滾日志（逆向操作的邏輯日志）
• undo寫完之后，事務修改數據頁頭部（會記錄DB_TRX_ID+DB+ROLL_PTR）,這個信息也會被記錄在redo Log中

簡單理解：記錄數據修改的前鏡像（逆向操作）,數據頁和undo通過DB_TRX_ID，DB_ROLL_PTR建立關系

特殊情景分析01：

當執行rollback命令時，根據數據頁的DB_TRX_ID+DB+ROLL_PTR信息，找到undo日志并進行回滾；

特殊情景分析02：

mysql> begin;
mysql> update t1 set A=2 where A=1;
-- 此時宕機了

假設：undo 有；redo 沒有

• 啟動數據庫時，檢查redo和數據頁的LSN號碼，發現是一致的；
• 所以不需要進行redo的前滾，此時也不需要回滾。undo信息直接被標記為可覆蓋狀態；

假設：undo 有；redo 也有（沒有commit標簽）

• MySQL CR（自動故障恢復）工作模式，啟動數據庫時，自動檢查redo的LSN和數據頁LSN;
• 如果發現redo LSN>數據頁的LSN。隨即加載原始數據頁+變化redo Log日志信息到相應內存位置，使用redo重構臟頁（前滾）；
• 如果確認此次事務沒有commit標記，立即觸發回滾操作，根據DB_TRX_ID+DB_ROLL_PTR信息，找到undo回滾日志，實現回滾；

以上流程被稱之為InnoDB的核心特性：自動故障恢復（CR），會先前滾再回滾，先應用redo再應用undo；

undo保證了ACID哪些特性：

主要保證事務的A的特性，同時C和I的特性也有關系；

undo Log日志文件生成流程：

undo Log日志文件應用流程：

說明：利用undo Log重做日志功能可以保證事務的A特性，基于先進行數據頁前滾操作恢復臟頁，在進行回滾操作恢復操作前事務；

事務工作流程三：事務中的C特性如何保證

InnoDB crash recovery：數據庫意外宕機時刻，通過redo前滾+undo回滾保證數據的最終一致；

InnoDB doubewrite buffer：默認存儲在ibdataN中，解決數據頁寫入不完整；DWB一共2M，分兩次。每次1M寫入；

redo日志只能恢復好的數據頁的內容，但是不能恢復已經有異常的數據頁內容；

可以參考官方資料：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

DWB文件信息生成流程：

DWB文件信息應用流程：

事務工作流程四：事務中的I特性如何保證

主要對數據庫服務并發訪問資源的保護，在并發事務工作期間，防止事務與事務之間的資源爭搶（相互影響）；

• 保證讀隔離性

方式一：利用隔離級別保證

序號	隔離級別	簡單回顧
01	RU	有可能會出現臟讀、不可重復讀、幻讀
02	RC	有可能會出現不可重復讀，幻讀
03	RR	有可能會出現幻讀（99.9%的讀異常問題配合鎖機制都可以處理）
04	SR(SE)	采用事務串行工作機制

方式二：利用MVCC機制隔離（只能保證讀的隔離）

MVCC(multi-version-concurrent-control)即多版本并發控制，是一種并發控制的方法；

可以類別成Git進行并發處理的機制，其實就是每個事務在發生更新的過程中，維護發生更新事務的各個版本；

各個事務版本通過undo的日志（前鏡像）實現快照的技術（read view），從而可以保存多個事務版本；

對于隔離級別而言，只有RC和RR級別可以使用到MVCC機制的，實現一種快照讀機制，而RU和SR級別是不會使用到MVCC機制的；

• RC：應用MVCC的快照讀機制，是基于語句級別的；（不可重復讀 ture）

在事務期間，執行每個查詢語句的時候，都會檢查MVCC版本（快照列表），獲取最新的已提交事務的快照；

• RR：應用MVCC的快照讀機制，是基于事務級別的；（不可重復讀 false）

在事務期間，執行首條查詢語句的時候，就會生成MVCC版本（相應快照），將會一直讀取此快照數據信息，直到事務生命周期結束；

以上的RR隔離級別利用MVCC的快照讀機制，又稱為一致性快照讀；

==================================================================================================

MVCC進行多版本控制時，會應用的兩種鎖機制：樂觀鎖/悲觀鎖

每個事務操作都要經歷兩個階段：

• 讀階段--應用樂觀鎖：

MVCC利用樂觀鎖機制，實現非鎖定讀取，借助快照技術（read view）

# 進行操作事務處理過程（trx1）
> begin;
> DML01 語句
-- 在做第一次事務操作的時候，當前事務獲取系統最新的 rv1 版本快照
> DML02 語句
-- 在做下一次事務操作的時候，生成新的事務系統查詢的 rv2 版本快照
> select 
-- 此時查詢的是 rv2快照數據信息
> commit
-- rv2 快照數據被提交，成為系統最新的快照

RC隔離級別快照應用：

trx-01：rv1  -> rv2  -> commit;
trx-02：rv1  -> rv1  -> rv2

RR隔離級別快照應用：

trx-01：第一個查詢時，生成global consitence snapshot RV-CS1（10:00），一直伴隨著事務生命周期結束
trx-02：第一個查詢時，生成global consitence snapshot RV-CS2（10:01），一直伴隨著事務生命周期結束

• 寫階段--應用悲觀鎖：

即對于寫操作，是不能進行并發操作的；

MVCC技術總結：

01 mvcc采用樂觀鎖機制，實現非鎖定讀取；

02 在RC級別下，事務中可以立即讀取到其它事務提交過的readview數據快照信息；

03 在RR級別下，事務中從第一次查詢開始，生成一個一致性readview，直到事務結束

==================================================================================================

• 保證寫隔離性

方式一：利用隔離級別保證

在應用不同隔離級別時也會有不同的鎖機制

• RC：具有記錄鎖機制；
• RR：具有間隙鎖機制+下一鍵鎖機制（next lock） 表鎖

方式二：利用鎖進制隔離（保護并發訪問資源）

類型	鎖機制	簡述說明
內存資源鎖	latch（閂鎖）	主要是保護內存資源；rwlock（讀寫鎖）、mutex（只讀鎖） 避免不同程序爭用相同地址區域內存資源）
元數據鎖	MDL	主要是保護元數據資源，限制DDL操作；metadata lock
表級別鎖	table_lock	主要是保護整個數據表資源；
	命令方式鎖表	lock table t1 read；
	工具方式鎖表	利用mysqldump、XBK(PBK)進行備份非InnoDB數據時，將觸發FTWRL全局鎖表；
	行鎖升級為表鎖	比如做數據更新操作時，沒有設置索引條件信息，就會出現全表掃描，出現表鎖；
行級別鎖	row_lock	InnoDB默認鎖粒度，加鎖方式都是在索引上加鎖的；
	record lock	記錄鎖，在聚簇索引鎖定，在RC級別只有record lock
	gap lock	間隙鎖，在輔助索引間隙加鎖，在RR級別存在，防止幻讀；
	next look	下一鍵鎖，即GAP+Record，在RR級別存在，防止幻讀；

從功能應用方面進行鎖分類：了解

• IS：表示意向讀鎖或查詢鎖，可以在表上進行加鎖做提示（select * from t1 lock in shared mode）;
• S：表示讀鎖或查詢鎖，現在基本上沒有自動設置了，除非手工進行設置鎖定（lock table t1 read);
• IX：表示意向寫鎖或排他鎖，可以在表上進行加鎖做提示（select * from t1 for update）
• X：表示寫鎖或排他鎖，限制其他人的指定操作行為；

官方參考資料鏈接：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html