中文字幕精品亚洲无线码二区,国产黄a三级三级三级看三级,亚洲七七久久桃花影院,丰满少妇被猛烈进入,国产小视频在线观看网站

MySQL 數據庫服務(wu)事(shi)務(wu)知(zhi)識

數據庫存儲事務機制概念

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

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

因此(ci)就需要考慮如何保(bao)證現實(shi)生活中交易過(guo)程的和(he)諧，一般(ban)會有(you)法律、道德(de)等方(fang)面規則進(jin)行約束(shu)；

而在數據庫服(fu)務(wu)中(zhong)為了(le)保證線(xian)上交易(yi)的"和(he)諧"，便加(jia)入了(le)"事務(wu)"工作機制(zhi)

數據庫存儲事務機制特性

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

特性一：原子性（Atomicity）

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

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

Begin：DML01 DML02 DML03 Commit;

特性二：一致性（Consistency）

一(yi)致(zhi)(zhi)性(xing)表示一(yi)個事(shi)務發(fa)生(sheng)前、中、后，數據都最終保持一(yi)致(zhi)(zhi)，即讀和寫都要(yao)保證一(yi)致(zhi)(zhi)性(xing)；

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

CR + Double write

特性三：隔離性（Isolation）

隔(ge)離性表示一個(ge)事務(wu)操作(zuo)數(shu)據行的(de)時候，不(bu)會(hui)受到(dao)其他事務(wu)的(de)影響，主要(yao)利(li)用鎖(suo)機制來保證隔(ge)離性；

特性四：持久性（Durability）

持久性表示(shi)一(yi)旦事務進行了提(ti)交，即(ji)可永(yong)久生(sheng)效（落(luo)盤）

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

事(shi)務ACID相關知識官方說明：

數據庫存儲事務生命周期

在運用事務(wu)機(ji)制(zhi)完成相(xiang)關工(gong)作任務(wu)時，對(dui)于事務(wu)使用是存在生(sheng)命周期概念的，標準顯(xian)示的事務(wu)生(sheng)命周期控制(zhi)語句有：

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

-- 提交事務任務
commit;

-- 回滾事務操作
rollback;

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

事(shi)務的(de)生命周期操作(zuo)演示：

# 進行測試數據庫查詢數據
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；

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

都進行顯示的執行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數據庫生效

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

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

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

在進行事務操(cao)作時(shi)，需要(yao)注意操(cao)作語句(ju)必須(xu)都是DML語句(ju)，如(ru)果中(zhong)間插(cha)入了DDL語句(ju)，也會(hui)造(zao)成(cheng)之前的事務操(cao)作自動提交；

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

隱式自動提交方式語句：

在出(chu)現隱(yin)式(shi)自動提交時，可能導(dao)致(zhi)提交的非事(shi)務語句有：

序號 語句類型 涉及命令
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

說明：在(zai)多個數(shu)據(ju)庫會話窗(chuang)(chuang)口(kou)中(zhong)，A窗(chuang)(chuang)口(kou)的(de)所(suo)有事(shi)務性(xing)DML操作，不(bu)會受(shou)到B窗(chuang)(chuang)口(kou)的(de)非(fei)事(shi)務語(yu)句影響，同一會話窗(chuang)(chuang)口(kou)會有影響；

隱式自動回滾情況分析：

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

數據庫存儲事務隔離級別

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

然后可以讀(du)取數(shu)(shu)據(ju)頁(ye)中相應數(shu)(shu)據(ju)行的能力(li)，并(bing)且不(bu)同事務(wu)之間的數(shu)(shu)據(ju)頁(ye)讀(du)操(cao)作相互隔離；

可以(yi)簡單理(li)解(jie)為(wei)：一(yi)個(ge)(ge)事務在對數(shu)據頁(ye)中數(shu)據行做更新(xin)操作時，在沒有更新(xin)提交前，另(ling)一(yi)個(ge)(ge)事務此時是不(bu)能讀(du)取數(shu)據頁(ye)中數(shu)據行內容(rong)的；

對于數(shu)據庫存(cun)儲事務(wu)隔離級別(bie)(bie)包括4種，可以(yi)通過操(cao)作命令查看(kan)獲取當前使(shi)用(yong)的(de)隔離級別(bie)(bie):

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

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

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

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

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

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

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

可以防止(zhi)臟讀(du)（當前內存(cun)讀(du)），防止(zhi)不可重復讀(du)問(wen)題(ti)，防止(zhi)會出現(xian)的(de)幻讀(du)問(wen)題(ti)，但是并發能力較差；

會使用next lock鎖(suo)進制，來防(fang)止(zhi)幻讀問題(ti)，但是引入鎖(suo)進制后，鎖(suo)的代(dai)價會比(bi)較(jiao)高，比(bi)較(jiao)耗費CPU資(zi)源，占(zhan)用系(xi)統性能；

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

隔離性(xing)比較高，可以實現(xian)串(chuan)行(xing)化讀取(qu)數(shu)據，但是事務的并(bing)發度就(jiu)沒有(you)了；

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

事務隔(ge)離級別官方鏈接(jie)：

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

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

# 設置事務隔離級別
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 |
+---------------------+

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

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會話窗口，即不同的事務查看的數據是否一致的；
  • 名詞解讀分析一：臟讀

臟讀(du)主要表示(shi)在(zai)一個事務窗(chuang)口中，沒有數據修(xiu)改提(ti)交操(cao)作前，另一個事務就可以(yi)看到(dao)內存中數據頁的(de)修(xiu)改；

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

利用隔離級別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窗口查詢的數據又恢復了
  • 名詞解讀分析二：不可重復讀

不(bu)可重(zhong)復讀表示在一(yi)個事務中(zhong)，利(li)用相同(tong)的(de)(de)語句多次查(cha)詢(xun)，獲取(qu)的(de)(de)數據信息是不(bu)同(tong)的(de)(de)；

利用隔離級別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
-- 區域間隙鎖 < 左閉右開(可用臨界值)  ;  區域間隙鎖 > 左開右閉（不可用臨界值）

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

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

最經典(dian)的例子就是轉(zhuan)賬，你(ni)要給朋友小王(wang)轉(zhuan) 100 塊錢(qian)，而此(ci)時你(ni)的銀行卡只有 100 塊錢(qian)。

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

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

如果銀行這么整，不(bu)就(jiu)亂了么？這時(shi)就(jiu)要用到(dao)“事務”這個概念(nian)了。

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

在(zai) MySQL 中，事務(wu)(wu)支持(chi)是在(zai)引擎層實現的(de)。MySQL 是一個(ge)支持(chi)多引擎的(de)系統(tong)，但并(bing)不是所有(you)的(de)引擎都(dou)支持(chi)事務(wu)(wu)。

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

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

隔離性與隔離級別

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

我們就來說說其中 I，也(ye)就是“隔離(li)性(xing)”。

當數據庫(ku)上有(you)多個(ge)事(shi)務同(tong)時(shi)執行的時(shi)候，就可能出現以(yi)下問(wen)題：

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

為了(le)解決這(zhe)些問(wen)題(ti)，就有了(le)“隔(ge)(ge)離(li)(li)級別”的概念(nian)。在談隔(ge)(ge)離(li)(li)級別之前，首(shou)先(xian)要知道(dao)，隔(ge)(ge)離(li)(li)得(de)越(yue)嚴實，效(xiao)率就會越(yue)低(di)。

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

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

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

假設數(shu)據表 T 中(zhong)只(zhi)有一列，其中(zhong)一行的(de)值為(wei) 1，下(xia)面是按(an)照時(shi)間(jian)順序執行兩個(ge)事務的(de)行為(wei)。

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 會有哪些不同的返(fan)回結(jie)果，也就是(shi)圖里面 V1、V2、V3 的返(fan)回值分別是(shi)什么。

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

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

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

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

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

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

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

則在事務 B 執(zhi)行(xing)“將 1 改成 2”的時(shi)候，會(hui)被鎖住(zhu)。直(zhi)到事務 A 提(ti)交后，事務 B 才可(ke)以繼續執(zhi)行(xing)。

所以從(cong) A 的(de)角度(du)看， V1、V2 值(zhi)是 1，V3 的(de)值(zhi)是 2。

數據庫存儲事務工作流程

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

事(shi)務(wu)工作流(liu)程名字解(jie)釋：

  • 名詞解釋一：redo log-Disk

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

文件存(cun)儲表項(xiang)為：ib_logfile0~N 默(mo)認(ren)48M，輪詢(xun)使用

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

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

事(shi)務修改提(ti)交后(hou)：redo log buffer -> redo log，表示寫入數據(ju)到redo log；

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

  • 名詞解釋三：tablespace file-disk

表(biao)示存儲表(biao)數據(ju)行和索引等信息(xi)的文(wen)(wen)件，含(han)有(you)表(biao)空間所有(you)數據(ju)文(wen)(wen)件；ibd

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

表示數據緩(huan)沖區，主要(yao)用于緩(huan)沖事務要(yao)處理的數據和索(suo)引信(xin)息，tablespace文件(jian)與buffer pool是(shi)有IO關(guan)系(xi)的；

  • 名詞解釋五：LSN

表(biao)示日(ri)志序列號，在(zai)buffer pool中有數據頁(ye)信息的變化就會(hui)記錄到redo log buffer中，主要(yao)記錄變化了多少(shao)字節(jie)量；

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

MySQL每次(ci)數(shu)(shu)據(ju)庫啟動(dong)，都會比較磁盤(pan)數(shu)(shu)據(ju)頁和redolog的LSN，必須(xu)要求兩者一致，數(shu)(shu)據(ju)庫才能(neng)正(zheng)常啟動(dong)；

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

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

  • 名詞解釋七：Dirty page

表(biao)示(shi)在內(nei)存進(jin)行修改的數據(ju)(ju)頁，在redo buffer中會記錄(lu)數據(ju)(ju)頁的數據(ju)(ju)量的變化，此時在數據(ju)(ju)頁還未最(zui)終(zhong)寫入到磁盤中時；

就稱之為臟頁(ye)，所以(yi)一般(ban)所謂的臟讀(du)就是讀(du)取臟頁(ye)的數據頁(ye)信息；

  • 名詞解釋八：CheckPoint

表示為(wei)檢查點(dian)，就是將臟頁(ye)刷寫(xie)到磁(ci)盤的動作；

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

表示為事(shi)務ID號，InnoDB會(hui)為每一個(ge)(ge)事(shi)務生(sheng)(sheng)成一個(ge)(ge)事(shi)務號（由事(shi)務管(guan)理(li)(li)器管(guan)理(li)(li)TM），伴隨著(zhu)整個(ge)(ge)事(shi)務生(sheng)(sheng)命周期

其中事(shi)務ID號碼信息，在redo和undo日(ri)志文件中都會有(you)相應的(de)標(biao)識；

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

表示回滾(gun)指(zhi)針(zhen)，在rollback時(shi)會使用undo日志回滾(gun)已修改的數據，DB_ROLL_PTR會指(zhi)向此次(ci)事務(wu)的回滾(gun)業務(wu)點；

從而找到undo上(shang)的相應的日(ri)志(zhi)信(xin)息(xi)；

數(shu)據庫名詞解釋(shi)官方參考：

事務工作流程具體解讀：

簡單事務情況舉例：

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

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

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

  • 將(jiang)在內(nei)存中(zhong)發生(sheng)數據(ju)頁(ye)修改操作（A=1改為A=2），形成數據(ju)頁(ye)臟頁(ye)，更改中(zhong)數據(ju)頁(ye)的變(bian)化(hua)會記錄到redo buffer中(zhong)；

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

  • 當執行(xing)事務(wu)提交操作(zuo)的(de)時候，基(ji)于WAL機制(zhi)，等到(dao)redo buffer中(zhong)的(de)日志完(wan)全落盤(pan)到(dao)ib_logfileN-redo log中(zhong)，即commit正(zheng)式完(wan)成；

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

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

特殊情(qing)景分(fen)析：當此時，redo落(luo)盤(pan)了(le)，數據頁沒有落(luo)盤(pan)，出(chu)現(xian)宕(dang)機情(qing)況了(le)；

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

知識點補充：

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

為了區分不同(tong)狀態(tai)的redo，日志記錄(lu)時會(hui)標(biao)記是否commit；

redo保證了(le)ACID哪些特(te)性：

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

Redo Log日志文件生成流程：

1668703457452

Redo Log日志文件應用流程：

1668703613790

說明：利用redo Log重做日(ri)(ri)志功能(neng)可(ke)以(yi)保證事務的(de)D特性，基(ji)于可(ke)以(yi)丟內(nei)存數據，但是不可(ke)以(yi)丟操作事務日(ri)(ri)志的(de)原(yuan)則；

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

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配置信息

存(cun)儲引(yin)擎序(xu)號號碼信息查看：

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號碼信息

存儲引擎(qing)redo buffer落盤的(de)機制策(ce)略：****

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：表示在事務提交后先生成日志緩存信息，然后再按照每秒鐘進行一次寫入磁盤操作；不安全
-- 參考官方鏈接：//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命令(ling)時(shi)，根據(ju)數(shu)據(ju)頁的DB_TRX_ID+DB+ROLL_PTR信息(xi)，找到(dao)undo日(ri)志并進行回滾；

特殊情景分析02：

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

假設：undo 有(you)；redo 沒有(you)

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

假設：undo 有(you)；redo 也(ye)有(you)（沒(mei)有(you)commit標簽）

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

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

undo保證了ACID哪些(xie)特性(xing)：

主要保證事務的(de)A的(de)特(te)性(xing)，同時C和(he)I的(de)特(te)性(xing)也(ye)有(you)關系；

undo Log日志文件生成流程：

1668741592412

undo Log日志文件應用流程：

1668741660424

說明：利(li)用(yong)undo Log重(zhong)做日(ri)志功能(neng)可以保證事務(wu)的A特性，基于先進(jin)行數(shu)據頁(ye)(ye)前滾(gun)操(cao)作(zuo)(zuo)恢復臟頁(ye)(ye)，在進(jin)行回滾(gun)操(cao)作(zuo)(zuo)恢復操(cao)作(zuo)(zuo)前事務(wu)；

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

InnoDB crash recovery：數(shu)據庫意外(wai)宕機時刻(ke)，通過redo前滾+undo回滾保證數(shu)據的(de)最(zui)終一致(zhi)；

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

redo日志只能恢(hui)復(fu)好的數(shu)據頁的內容(rong)，但是不能恢(hui)復(fu)已經有異常(chang)的數(shu)據頁內容(rong)；

可以參考官方資料：

DWB文件信息生成流程(cheng)：

1668751315005

DWB文件信息應用(yong)流程(cheng)：

1668751396397

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

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

  • 保證讀隔離性

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

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

方式(shi)二：利用MVCC機制隔(ge)離(li)（只能保證讀的隔(ge)離(li)）

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

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

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

對(dui)于(yu)隔(ge)離級(ji)(ji)別(bie)而言，只有RC和RR級(ji)(ji)別(bie)可以使(shi)用到MVCC機制的(de)，實現一(yi)種快照讀機制，而RU和SR級(ji)(ji)別(bie)是不會使(shi)用到MVCC機制的(de)；

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

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

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

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

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

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

MVCC進行多版(ban)本控(kong)制時(shi)，會應(ying)用的兩(liang)種鎖機制：樂(le)觀(guan)鎖/悲觀(guan)鎖

每個事務(wu)操作都要經(jing)歷(li)兩(liang)個階段(duan)：

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

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

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

RC隔離級別快照(zhao)應用：

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

RR隔(ge)離級(ji)別快照應用：

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

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

MVCC技術總結：

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

02 在RC級(ji)別下(xia)，事(shi)務(wu)中(zhong)可以(yi)立即讀取到其(qi)它事(shi)務(wu)提交過(guo)的readview數據快照信(xin)息；

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

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

  • 保證寫隔離性

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

在應用不同(tong)隔離級別(bie)時也會有不同(tong)的鎖機(ji)制

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

方式(shi)二：利(li)用鎖進(jin)制隔(ge)離（保(bao)護并發(fa)訪問資(zi)源）

類型 鎖機制 簡述說明
內存資源鎖 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級別存在，防止幻讀；

從功能應用(yong)方面進行鎖(suo)分(fen)類：了解

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

官方參考資料鏈接：

posted @ 2025-10-29 09:36  講文張字  閱讀(174)  評論(0)    收藏  舉報
返回頂部