數據庫事務機制功能介紹
1.事務功能作用
功能作用：事務功能主要是保證數據庫中數據的安全性
事務機制：ACID
詳細ACID參考鏈接：https://dev.mysql.com/doc/refman/8.4/en/mysql-acid.html
A: atomicity（原子性）
在一個事務中，做的所有語句操作，要麼都成功，要麼都失敗，不能有部分成功部分失敗的情況
銀行轉賬 賬户A -100-> 賬户B
賬户表中
賬户名稱 賬户金額
賬户A 500
賬户B 0
update 賬户表 set 賬户金額=賬户金額-100 where 賬户名稱='賬户A'； 400元
update 賬户表 set 賬户金額=賬户金額+100 where 賬户名稱='賬户B'； 100元
C: consistency （一致性）
在一個事務處理數據過程中，數據庫正常運行或數據庫出現異常重啓後，數據信息要保證一致性
銀行轉賬 賬户A -100-> 賬户B
update 賬户表 set 賬户金額=賬户金額-100 where 賬户名稱='賬户A'；
update 賬户表 set 賬户金額=賬户金額+100 where 賬户名稱='賬户B'；
select * from 賬户表;
賬户名稱 賬户金額
賬户A 400
賬户B 100
commit; -- 將內存查看信息保存到磁盤（自動提交/手動提交）
重啓數據庫
賬户名稱 賬户金額
賬户A 500
賬户B 0
doublewrite buffer -- 雙寫緩衝區/雙寫文件中（#ib_16384.dblwr）
mysql 存儲數據過程中：
需要處理的數據都會加載到內存中
內存區域 <-IO消耗加載數據- 磁盤區域
賬户A 400 賬户A 500 -- page01
賬户B 100 賬户B 0
page01
內存區域 提交保存數據信息-> 磁盤區域
賬户A 400 賬户A 500 -- page01
賬户B 100 賬户B 0
page01
page01 16kB block-01 4kB
block-02 4kB
數據庫進程停止了 page01 - 16KB -- 壞頁
..
block-04 4kB
page01 16kB 4KB 4KB 4KB 4KB -- doublewrite buffer -- #ib_16384.dblwr (完整內存中16KB數據信息)
page01 - 16KB -- 壞頁
數據庫異常停止，再次重新啓動時，會自動觸發災難恢復機制（InnoDB crash recovery）
加載磁盤中可能存在壞頁： page01 - 16KB -- 壞頁 -- 修復壞頁 -- 加載#ib_16384.dblwr 將雙頁文件中數據修復到壞頁中
I：Isolation （隔離性）
在一個事務中應用隔離特性，主要用於保證事務的操作過程，不會受到其他事務對相同數據操作的影響
銀行轉賬 賬户A -100-> 賬户B 賬户A - 500 -> 賬户C
事務01 賬户A -100-> 賬户B
update 賬户表 set 賬户金額=賬户金額-100 where 賬户名稱='賬户A'； 賬户A - 行 Row Lock -- unLock
update 賬户表 set 賬户金額=賬户金額+100 where 賬户名稱='賬户B'；
commit;
事務02 賬户A - 500 -> 賬户C
update 賬户表 set 賬户金額=賬户金額-500 where 賬户名稱='賬户A'； 賬户A - 行 阻塞
update 賬户表 set 賬户金額=賬户金額+500 where 賬户名稱='賬户C'；
-- 可以應用鎖機制（寫衝突問題）和隔離級別設置（讀衝突問題）確保並行事務操作相同數據不會產生衝突
D: durability （持久性）
事務持久特性，主要是用於保證當數據庫內存中沒有保存到磁盤的數據釋放後，可以有效進行內存數據恢復
mysql 存儲數據過程中：
需要處理的數據都會加載到內存中
內存區域 <-IO消耗加載數據- 磁盤區域
賬户A 400 賬户A 500 -- page01
賬户B 100 賬户B 0
page01
commit
內存區域 提交保存數據信息-> 磁盤區域
賬户A 400 賬户A 500 -- page01
賬户B 100 賬户B 0
page01
數據庫停止了 （沒有做雙寫操作 沒有將數據寫入磁盤）
內存區域 磁盤區域
賬户A 400 賬户A 500 -- page01
賬户B 100 賬户B 0
page01
update 記錄信息 -- redo文件（記錄對數據頁中數據的操作信息）
page01 賬户A 500->400 賬户B 0->100
commit
內存區域 磁盤區域
賬户A 500 -- page01
賬户B 0
數據庫異常停止，再次重新啓動時，會自動觸發災難恢復機制（InnoDB crash recovery）
會加載redo文件信息 根據redo文件中數據頁信息和磁盤中對應數據頁數據對對比
redo page01 （LSN信息做對比 01 LSN=200） 磁盤 page01 （LSN信息做對比 01 LSN=100）
賬户A 500->400 400 賬户A 500
賬户B 0->100 100 賬户B 0
將磁盤中原有數據頁重新加載到內存中，並根據redo文件中的操作記錄，重新對數據信息進行處理
內存區域 <-IO消耗加載數據- 磁盤區域
賬户A 400 賬户A 500 -- page01
賬户B 100 賬户B 0
數據庫ACID機制
數據庫ACID機制：
1）介紹ACID機制功能作用
A -- 在一個事務中，做的所有語句操作，要麼都成功，要麼都失敗，不能有部分成功部分失敗的情況
C -- 在一個事務處理數據過程中，數據庫正常運行或數據庫出現異常重啓後，數據信息要保證一致性
I -- 在一個事務中應用隔離特性，主要用於保證事務的操作過程，不會受到其他事務對相同數據操作的影響
D -- 事務持久特性，主要是用於保證當數據庫內存中沒有保存到磁盤的數據釋放後，可以有效進行內存數據恢復
2) ACID機制特性如何實現
A -- 實現原子性，需要藉助redo（記錄對數據的操作信息）和 undo文件信息（記錄操作前的數據信息）
C -- 實現一致性，需要藉助doublewrite buffer（緩衝區/文件）和 InnoDB crash recovery 機制
I -- 實現隔離性，需要藉助數據庫鎖功能（寫隔離/避免寫衝突）和藉助隔離級別（Transaction isolation levels）功能設置 （讀隔離/讀衝突）
D -- 實現持久性，需要藉助redo（記錄對數據的操作信息）和 InnoDB crash recovery 機制
會間接應用 doublewrite buffer 和 undo文件信息
2.數據庫事務功能應用
方式一： 手動開啓應用事務功能
begin（start transaction）; 對庫或表或數據可以進行SQL語句操作
DDL DCL 在事務中只能做一個語句操作；commit（自動觸發）
DML DQL 在事務中可以做多個語句操作；commit/rollback
PS：當手動開啓事務，必須進行手動提交，否則數據庫會話斷開或數據庫服務重啓，事務操作會自動撤銷
設置數據庫配置項，實現手動事務功能：autocommit=0;
DML 操作，必須在操作之後，進行手動提交事務
DDL DCL 操作，可以實現操作後，進行自動提交事務
方式二： 自動觸發應用事務功能
設置數據庫配置項，實現自動事務功能：autocommit=1;
操作數據庫執行DDL DCL語句也會實現自動提交事務功能
應用數據庫到底是實現手動事務功能還是自動事務功能
手動應用事務：
優勢：可以更好保證多個DML語句操作過程中，實現原子性
缺陷：可能由於沒有手動觸發commit操作，導致數據丟失
自動應用事務：
優勢：會在完成所有SQL語句後，都會自動進行數據提交保存，可以避免數據都是
缺陷：無法實現多個DML語句操作過程中，業務邏輯的原子性
補充：事務的自動回滾功能在什麼時候會自動觸發；
- 情況一：在事務操作過程中，會話窗口自動關閉了，會進行隱式自動回滾；
- 情況二：在事務操作過程中，數據庫服務被停止了，會進行隱式自動回滾； （避免大的數據庫事務應用-大事務應用）
- 情況三：在事務操作過程中，出現事務衝突死鎖了，會進行隱式自動回滾； （若死鎖不做回滾，會導致大量並行事務阻塞）
3.事務隔離機制中中應用的隔離級別
RU READ-UNCOMMITTED （讀未提交級別） 默認具有 髒讀問題 不可重複讀問題 幻讀問題 並行事務處理能力最強
RC READ-COMMITTED （讀已提交級別） 默認具有 不可重複讀問題 幻讀問題 並行事務處理能力一般
RR REPEATABLE-READ （可重複讀級別） 默認沒有以上讀取數據問題 （默認級別） 可以有效保證並行處理能力
SR SERIALIZABLE （事務串行化級別） 默認沒有以上讀取數據問題 並行事務處理能力最差
並行事務讀取數據可能出現的衝突問題：髒讀問題 不可重複讀問題 幻讀問題
髒讀： 並行事務之間讀取數據，可以讀取到事務中未提交數據
不可重複讀： 單個事務進行數據查詢時，每次查詢的數據內容都不一致
幻讀： 單個事務進行數據查詢時，每次讀取數據量不一致
創建測試數據：
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;
begin;
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');
commit;
set global transaction_isolation='READ-UNCOMMITTED';
select @@transaction_isolation;
開啓事務A
修改數據信息
開啓事務B
看到修改後但未提交數據
直接操作未提交數據，或出現數據異常情況
-- 以上情況出現，表示髒讀問題沒有避免
set global transaction_isolation='READ-COMMITTED';
select @@transaction_isolation;
開啓事務A
修改數據信息，並做事務提交
開啓事務B
對錶中數據做統計分析，但是每次分析的結果都不一致
-- 以上情況出現，表示不可重複讀問題沒有避免
開啓事務A
修改數據信息（範圍信息），並做事務提交
事務A核對修改數據信息時，出現異常數據內容
開啓事務B
對錶中數據插入操作，可以插入符合A事務範圍中的數據
-- 以上情況出現，表示幻讀問題沒有避免
set global transaction_isolation='REPEATABLE-READ';
select @@transaction_isolation;
-- 利用RR級別可以有效避免 髒讀 不可重複讀 以及幻讀問題 並且在某個數據需要合理並行操作時，不會降低並行事務處理能力
RR級別並行事務操作處理能力
事務A 事務B
讀操作 讀操作 --- 沒有阻塞 可以並行處理
讀操作 寫操作 --- 沒有阻塞 可以並行處理
寫操作 寫操作 --- 沒有阻塞 可以並行處理（同時寫操作數據不能有關聯）
set global transaction_isolation='SERIALIZABLE';
select @@transaction_isolation;
SE級別並行事務操作處理能力
事務A 事務B
讀操作 讀操作 --- 沒有阻塞 可以並行處理 但會影響後續的事務中寫操作
寫操作 讀操作 --- 會有阻塞 不能並行處理
寫操作 寫操作 --- 沒有阻塞 可以並行處理（同時寫操作數據不能有關聯） 但會影響後續數據檢查
-- 串行級別事務中的操作，基本都是採用串行方式執行（串行級別中應用的鎖基本都是表級鎖）
數據庫服務日誌管理知識
1.數據庫日誌分類和日誌記錄信息作用
錯誤日誌：記錄數據庫啓動異常和運行過程中錯誤信息
通用日誌：記錄用户登錄數據信息/記錄登錄成功後用户的操作行為 審計信息
二進制日誌： 記錄完整事務中操作語句信息(DQL語句不記錄),以時間信息記錄內容 用於修復數據/主從同步數據
慢查詢記錄：記錄數據庫的執行操作慢的語句 select/insert/update/delete 優化改進數據庫服務性能
2.數據庫日誌應用管理配置
錯誤日誌：
log_error 可以用於創建錯誤日誌和指定日誌存儲以及名稱信息
vim /etc/my.cnf
[mysqld]
log_error=/data/3307/log/error.log
通用日誌：
general_log 是否開啓通用日誌
general_log_file 當通用日誌功能開啓，指定日誌存儲路徑和名稱信息
慢查詢日誌（-- 瞭解基礎配置和查看即可）：
slow_query_log -- 是否開啓慢查詢日誌功能
slow_query_log_file -- 當慢查詢日誌功能開啓，指定慢查詢日誌存儲路徑和名稱信息
long_query_time -- 表示記錄慢查詢信息條件，當查詢操作時間超過配置項設置的數值，就會記錄語句到日誌中
log_queries_not_using_indexes -- 表示記錄慢查詢信息條件，查詢操作沒有應用索引的語句都會記錄到日誌中
二進制日誌
1）如何應用二進制日誌
log_bin -- 可以應用配置項開啓二進制日誌功能並設置存儲路徑和名稱信息
2）如何查看二進制日誌
方法一：查看日誌信息-利用命令查看日誌
mysqlbinlog binlog.000001
查看日誌文件關注內容：
# at xxx數值 -- 完成某些事務中事件位置點信息 便於修復指定數據或進行主從同步
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; -- ？？？
DDL DCL DML 語句信息 -- 語句信息
方法二：查看日誌信息-利用SQL語句查看
show binary logs; -- 查看數據庫服務可以加載的日誌信息
show master status; -- 查看日誌的應用狀態信息
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
File -- 此時數據庫服務正在應用哪個binlog日誌文件
Position -- 日誌內容記錄的位置點變化情況
Binlog_Do_DB -- 日誌記錄白名單設置
配置文件中 binlog_do_db=xiaoA 對xiaoA數據庫的所有SQL操作都會記錄日誌 其他數據庫SQL操作不記錄
Binlog_Ignore_DB -- 日誌記錄黑名單設置
配置文件中 binlog_ignore_db=xiaoA 對xiaoA數據庫的所有SQL操作都不會記錄日誌 其他數據庫SQL操作會記錄
Executed_Gtid_Set -- 記錄數據事務編號信息
show binlog events in '日誌名稱' -- 直接查看指定日誌中的內容
總結
課程知識內容梳理：
01 數據庫服務事務功能應用
1）數據庫事務特性：ACID 每個特性作用 每個特性實現方法
2）數據庫事務應用：手動方式應用 begin;DDL/DML/DCL;commit/rollback 自動方式應用 autocommit=ON
3) 事務隔離級別： 有什麼隔離級別 RU RC RR SR 每個隔離級別特性
瞭解並行事務讀取數據問題：髒讀？ 不可重複讀？ 幻讀？
02 數據庫服務日誌管理方法
1）數據庫日誌類型： 通用日誌 錯誤日誌 慢查詢日誌 二進制日誌 每個日誌作用/每個日誌記錄什麼信息
2）數據庫日誌管理： 通用日誌 錯誤日誌 慢查詢日誌 -- 創建日誌/查看日誌
二進制日誌 創建日誌/查看日誌
01 數據庫InnoDB存儲引擎體系結構？
InnoDB 默認的數據存儲引擎（MySQL5.5之後的）
應用場景：適用於讀多寫多，可以保證數據存儲安全性和一致性，如：遊戲 金融 銀行
應用特點：支持事務（保證數據存儲安全性）
支持行級鎖（提高併發處理能力）
支持MVCC機制（可以實現熱備數據，並且備份期間不影響數據庫正常存儲業務）
支持外鍵功能
MyISAM 早期的數據存儲引擎（MySQL5.5之前的）
應用場景：適用於讀多寫少，數據完整性要求不高的場景，如博客，新聞網站等
應用缺陷：不支持事務（無法保證數據存儲安全性）
無法支持行級鎖，但可以支持表級鎖（改善數據庫併發處理能力）
不支持MVCC多版本併發控制機制（在數據備份時，可以實現不影響業務進行備份數據）
不支持外鍵約束功能
02 數據庫事務的兩階段提交是什麼？ commit; -- 數據庫接收到提交指令，會完成2個事務操作處理 最終才會將提交數據信息寫入磁盤
03 數據庫事務相關雙一配置是什麼？ 雙一配置 innodb_flush_log_at_trx_commit=1 sync_binlog=1
04 數據庫中什麼是大事務，如何避免大事務出現？ 大事務出現會造成什麼影響？
什麼是大事務？
執行時間過長或一次性操作數據量過大的事務（通常指超過秒級，或涉及萬級以上行操作）。
如何避免（3招）
剔除非DB操作：事務內嚴禁調用 HTTP 接口、文件 IO 或複雜計算，只保留純 SQL。
分批提交：大批量增刪改時，拆分為小批次（如每次 500-1000 條），每批提交一次。
異步處理：耗時任務改為通過消息隊列後台異步執行，不阻塞主流程。
核心危害（3點）
鎖阻塞：長時間持有行鎖，導致其他業務排隊等待，甚至引發死鎖或超時。
主從延遲：從庫必須串行回放該事務，導致主從數據同步嚴重滯後。
資源爆炸：Undo Log 無法清理（版本鏈變長），內存（Buffer Pool）被擠佔，且回滾極慢（可能比執行還久）。
一句話總結：事務內只做最短的 DB 操作，大批量任務必須拆分或異步。
