[ 資料庫 ] MySQL 入門到放棄(六) --- 事務

 🍬 部落客介紹  

• 👨‍🎓 部落客介紹：大家好，我是_PowerShell ，很高興認識大家~
• ✨主攻領域：【滲透領域】【資料通信】 【通訊安全】 【web安全】【面試分析】
• 🎉點贊➕評論➕收藏 == 養成習慣（一鍵三連）😋
• 🎉歡迎關注💗一起學習👍一起讨論⭐️一起進步📝文末有彩蛋
• 🙏作者水準有限，歡迎各位大佬指點，互相學習進步！

我之前學習 MySQL 是在b站看的狂神說MySQL，收獲很大。

初學 Mysql 的話建議大家去看一看。

目錄

​​ 🍬 部落客介紹  ​​

​​我之前學習 MySQL 是在b站看的狂神說MySQL，收獲很大。​​

​​一、存儲引擎介紹​​

​​1. 存儲引擎的概念：​​

​​2. MySQL 檢視存儲引擎​​

​​3. 存儲引擎分類​​

​​1. MyISAM：​​

​​2. InnoDB：​​

​​3. BDB：​​

​​4. Memory ：​​

​​5. Merge ：​​

​​6. Archive ：​​

​​7. Federated：​​

​​8. Cluster/NDB ：​​

​​9. CSV：​​

​​10. BlackHole ：​​

​​11. 自定義​​

​​二、什麼是事務​​

​​1. 概述：​​

​​2. 概念​​

​​3. 特性（事物的四個屬性：ACID）​​

​​1. 原子性（atomicity）：​​

​​2. 一緻性（consistency）：​​

​​3. 隔離性（isolation）：​​

​​4. 持久性（durability）：​​

​​三、事物的類型​​

​​1. 手動事務​​

​​2. 自動事務​​

​​三、隔離産生的問題​​

​​1. 事物的隔離級别​​

​​1. 讀未送出資料（Read uncommitted）​​

​​2. 讀已送出資料（Read committed)​​

​​3. 可重複讀（Repeatable read）​​

​​4. 串行化（Serializable）​​

​​5. 總結​​

​​2. 隔離級别會産生的問題介紹​​

​​1. 髒讀：​​

​​2. 不可重複讀：​​

​​3. 虛讀(幻讀)​​

​​四、執行事務​​

​​紹事務的文法：​​

​​1. mysql 自動開啟事務送出​​

​​2. 手動處理事務​​

​​3. 事務開啟​​

​​4. 送出 ： 持久化(成功)​​

​​5. 復原：  回到原來的樣子（失敗）​​

​​6. 事務結束​​

​​7. 其他​​

​​五、模拟場景​​

​​模拟轉賬：事務​​

​​六、專欄分享​​

一、存儲引擎介紹

MySQL 中的存儲引擎以及支援事務，和不支援事務的存儲引擎 

1. 存儲引擎的概念：

在mysql中的資料用各種不同的技術存儲在檔案（或記憶體）中。

這些技術中的每一種技術都使用不同的存儲機制，索引 技巧，并且最終提供廣泛的不同的功能和能力。

可以通過選擇不同的技術，可以獲得額外的速度或功能，進而改善應用的整體功能。

這些不同的技術以及配套的相關功能在mysql中被稱為存儲引擎（也稱為表類型）。

2. MySQL 檢視存儲引擎

MySQL支援的存儲引擎很多，可以在MySQL用戶端輸入以下指令檢視mysql支援的存儲引擎的詳細資訊。

show engines;      

3. 存儲引擎分類

1. MyISAM：

擁有較高的插入，查詢速度，但不支援事務。

2. InnoDB：

5.5版本後Mysql的預設資料庫，事務型資料庫的首選引擎，支援ACID事務，支援行級鎖定。

3. BDB：

源自Berkeley DB，事務型資料庫的另一種選擇，支援COMMIT和ROLLBACK等其他事務特性

4. Memory ：

所有資料置于記憶體的存儲引擎，擁有極高的插入，更新和查詢效率。但是會占用和資料量成正比的記憶體空間。并且其内容會在Mysql重新啟動時丢失

5. Merge ：

将一定數量的MyISAM表聯合而成一個整體，在超大規模資料存儲時很有用

6. Archive ：

非常适合存儲大量的獨立的，作為曆史記錄的資料。因為它們不經常被讀取。Archive擁有高效的插入速度，但其對查詢的支援相對較差

7. Federated：

将不同的Mysql伺服器聯合起來，邏輯上組成一個完整的資料庫。非常适合​​分布式應用​​

8. Cluster/NDB ：

高備援的存儲引擎，用多台資料機器聯合提供服務以提高整體性能和安全性。适合資料量大，安全和性能要求高的應用

9. CSV：

邏輯上由逗号分割資料的存儲引擎。它會在資料庫子目錄裡為每個資料表建立一個.CSV檔案。這是一種普通文本檔案，每個資料行占用一個文本行。CSV存儲引擎不支援索引。

10. BlackHole ：

黑洞引擎，寫入的任何資料都會消失，一般用于記錄binlog做複制的中繼

11. 自定義

Mysql的存儲引擎接口定義良好，開發者可以通過閱讀文檔編寫自己的存儲引擎。

二、什麼是事務

1. 概述：

事務（Transaction）：一般是指要做的或所做的事情。

在計算機術語中是指通路并可能更新資料庫中各種資料項的一個程式執行單元。

事務通常由進階資料庫操縱語言或程式設計語言（如SQL，C++或Java）書寫的使用者程式的執行所引起，并用形如begin transaction和end transaction語句（或函數調用）來界定。

事務由事務開始(begin transaction)和事務結束(end transaction)之間執行的全體操作組成。

2. 概念

在關系向資料庫中，一個事務可以是一條SQL語句，一組SQL語句或整個程式。

（前面說過了，MySQL是關系型資料庫的一種）

事務由單獨單元的一個或多個SQL語句組成，在這個單元中，每個MySQL語句是互相依賴的。

整個單獨單元作為一個不可分割的整體，如果單元中某條SQL語句一旦執行失敗或産生錯誤，整個單元将會復原。

所有受到影響的資料将傳回到事物開始以前的狀态；如果單元中的所有SQL語句均執行成功，則事務被順利執行。

即開始事務後執行的所有SQL都是一個整體，一個單元，要麼都執行，要麼都不執行。這裡不執行的意思就row back即復原，就是回到事務執行前的狀态。

3. 特性（事物的四個屬性：ACID）

事務是恢複和并發控制的基本機關。

事務應該具有4個屬性：原子性、一緻性、隔離性、持久性。這四個屬性通常稱為ACID特性。

1. 原子性（atomicity）：

一個事務是一個不可分割的工作機關，事務中包括的操作要麼都做，要麼都不做。

簡單來說：要麼都成功，要麼都失敗。

2. 一緻性（consistency）：

事務必須是使資料庫從一個一緻性狀态變到另一個一緻性狀态。一緻性與原子性是密切相關的。

簡單來說：事務前後的資料完整性要保持一緻。

3. 隔離性（isolation）：

一個事務的執行不能被其他事務幹擾。即一個事務内部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相幹擾。

簡單來說：事務一旦送出就不可逆轉，被持久化到資料庫中。

4. 持久性（durability）：

持久性也稱永久性（permanence），指一個事務一旦送出，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

簡單來說：事務産生多并發時，互不幹擾。

三、事物的類型

1. 手動事務

手動事務允許顯式處理若幹過程，這些過程包括：開始事務、控制事務邊界内的每個連接配接和資源登記、确定事務結果（送出或中止）以及結束事務。

盡管此模型提供了對事務的标準控制，但它缺少一些内置于自動事務模型的簡化操作。

例如，在手動事務中資料存儲區之間沒有自動登記和協調。

此外，與自動事務不同，手動事務中事務不在對象間流動。

如果選擇手動控制分布式事務，則必須管理恢複、并發、安全性和完整性。

也就是說，必須應用維護與事務處理關聯的 ACID 屬性所需的所有程式設計方法。

2. 自動事務

.NET 頁、XML Web services方法或 .NET Framework 類一旦被标記為參與事務，它們将自動在事務範圍内執行。 

可以通過在頁、XML Web services 方法或類中設定一個事務屬性值來控制對象的事務行為。

特性值反過來确定執行個體化對象的事務性行為。

根據聲明特性值的不同，對象将自動參與現有事務或正在進行的事務，成為新事務的根或者根本不參與事務。

聲明事務屬性的文法在 .NET Framework 類、.NET 頁和 XML Web services 方法中稍有不同。

聲明性事務特性指定對象如何參與事務，如何以程式設計方式被配置。

盡管此聲明性級别表示事務的邏輯，但它是一個已從實體事務中移除的步驟。

實體事務在事務性對象通路資料庫或消息隊列這樣的資料資源時發生。

與對象關聯的事務自動流向合适的資料總管，諸如 OLE DB、開放是資料庫連接配接 (ODBC) 或 ActiveX 資料對象 (ADO) 的關聯驅動程式在對象的上下文中查找事務，并通過分布式事務處理協調器 (DTC) 在此事務中登記。整個實體事務自動發生。

三、隔離産生的問題

1. 事物的隔離級别

1. 讀未送出資料（Read uncommitted）

定義：

就是一個事務讀取到其他事務未送出的資料，是級别最低的隔離機制。

缺點：

會産生髒讀、不可重複讀、幻讀。

案例：

以前交易所炒股的時候，股民老王購買了5000股，操作員操作錄入（此時開啟事務），操作時手誤，多輸入了一個0，資料儲存但是未送出。此時老王查詢自己的持股資訊，發現自己居然有50000股，瞬間血壓升高，昏倒在地。然後操作員發現自己錄入錯誤，修改成正确的資訊，錄入完成（事務結束）。老王被救醒後，哆嗦這查詢自己的持股，發現隻有5000，沒有增減，他之前看到的就是髒讀資料。

解決方案：

采用更進階的隔離機制，如送出讀。

2. 讀已送出資料（Read committed)

定義：

就是一個事務讀取到其他事務送出後的資料。Oracle預設隔離級别。

缺點：

會産生不可重複讀、幻讀。

案例：

股市升高後，老王檢視自己持有5000股，就想賣掉4000股，在老王賣股票的時候，老王的老婆看股市太高，就登入老王的賬号，賣掉3000股。當老王想賣股票時，發現自己隻有2000股，不是之前看到的5000股，這就是不可重複讀問題。

解決方案：

采用更進階的隔離機制，如可重複讀。

3. 可重複讀（Repeatable read）

定義：

就是一個事務對同一份資料讀取到的相同，不在乎其他事務對資料的修改。MySQL預設的隔離級别。

缺點：

會産生幻讀。

問題解讀：

股市忽漲忽跌，老王焦慮不安，按捺不住，想把持有的多種股票全部抛掉。與此同時，老外老婆聽信磚家所言，使用老王的賬号買了某隻神股。老王抛掉所有股票後，檢視自己的持股，猛然發現自己居然還持有一隻股票，瞬間覺得一臉懵逼，這就是幻讀導緻。

解決方案：

采用更進階的隔離機制，序列化。

4. 串行化（Serializable）

定義：

事務串行化執行，隔離級别最高，犧牲了系統的并發性。

缺點：

可以解決并發事務的所有問題。但是效率地下，消耗資料庫性能，一般不使用。

5. 總結

事務隔離級别	髒讀	不可重複讀	幻讀
讀未送出( read-uncommitted )	是	是	是
不可重複讀( read-committed )	否	是	是
可重複讀( repeatable-read )	否	否	是
串行化( serializable )	否	否	否

2. 隔離級别會産生的問題介紹

1. 髒讀：

指一個事務讀取了另外一個事務未送出的資料。

2. 不可重複讀：

在一個事務内讀取表中的某一行資料，多次讀取結果不同。

（這個不一定是錯誤，隻是某些場合不對）

3. 虛讀(幻讀)

是指在一個事務内讀取到了别的事務插入的資料，導緻前後讀取不一緻。

（一般是行影響，多了一行）

四、執行事務

紹事務的文法：

1. 開啟事務start transaction，可以簡寫為 begin

2. 然後記錄之後需要執行的一組sql

3. 送出commit

4. 如果所有的sql都執行成功，則送出，将sql的執行結果持久化到資料表内。

5. 復原rollback

1. mysql 自動開啟事務送出

SET autocommit=0 -- 關閉
SET autocommit=1 -- 開啟（預設的）      

2. 手動處理事務

SET autocommit =0 -- 關閉自動送出      

3. 事務開啟

START TRANSACTION -- 标記一個事務的開始，從這個之後的SQP都在同一個事務内
INSERT XX
INSERT XX      

4. 送出 ： 持久化(成功)

COMMIT      

5. 復原：  回到原來的樣子（失敗）

ROLLBACK      

6. 事務結束

SET autocommit = 1 -- 開啟自動送出      

7. 其他

SAVEPOINT 儲存點名稱 -- 設定一個事務的儲存點
ROLLBACK TO SAVEPOINT 儲存點名 -- 復原到儲存點
RELEASE SAVEPOINT 儲存點 -- 删除儲存點      

五、模拟場景

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000),('B',10000)      

模拟轉賬：事務

SET autocommit = 0; -- 關閉自動送出
START TRANSACTION -- 開啟事務（一組事務）
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 轉賬給B
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到錢
COMMIT ; -- 送出事務
ROLLBACK ; -- 復原
SET autocommit=1 -- 恢複默值