目錄
一.并發控制
1.鎖機制
2.加鎖與釋放鎖
二.事務（transactions）
1.事物的概念
2.ACID特性
3.事務隔離級別
三.日志
1.事務日志
2.錯誤日志
3.通用日志
4.慢查詢日志
5.二進制日志 備份
一.并發控制
在 MySQL 中，并發控制是確保多個用戶同時訪問數據庫時數據一致性和完整性的重要機制。
1.鎖機制
鎖類型
讀鎖：共享鎖，也稱為 S 鎖,只讀不可寫（包括當前事務） ，多個讀互不阻塞 只能讀 不能寫 別人也能看
寫鎖：獨占鎖，排它鎖，也稱為 X 鎖,寫鎖會阻塞其它事務（不包括當前事務）的讀和寫 寫鎖 別人看都看不了
鎖特征：S 鎖和 S 鎖是兼容的，X 鎖和其它鎖都不兼容，舉個例子，事務 T1 獲取了一個行 r1 的 S 鎖，另外事務 T2 可以立即獲得行 r1 的 S 鎖，此時 T1 和 T2 共同獲得行 r1 的 S 鎖，此種情況稱為鎖兼容，但是另外一個事務 T2 此時如果想獲得行 r1 的 X 鎖，則必須等待 T1 對行 r1 鎖的釋放，此種情況也稱為鎖沖突
鎖粒度
表鎖：最基本的鎖機制，對整個表進行鎖定，其他會話無法同時修改整個表。
行級鎖：允許不同會話同時修改表中的不同行，提高并發性能。MySQL 支持的行級鎖包括讀取鎖（Shared Lock）和寫入鎖（Exclusive Lock）。
實現
存儲引擎：自行實現其鎖策略和鎖粒度
服務器級：實現了鎖，表級鎖，用戶可顯式請求
分類：
隱式鎖：由存儲引擎自動施加鎖
顯式鎖：用戶手動請求
鎖策略：在鎖粒度及數據安全性尋求的平衡機制
當開啟鎖時在兩個終端同時發送一個命令時只有一臺能夠實現成功
update students set teacherid=1 where stuid=1;
2.加鎖與釋放鎖
lock tables 表名 read; #加讀鎖
lock tables 表名 write; #加寫鎖
unlock tables; #釋放鎖
flush tables with read lock; #整個數據庫加鎖二.事務（transactions）
1.事物的概念
事務是一種機制、一個操作序列，包含了一組數據庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組數據庫命令要么都執行，要么都不執行。
事務是一個不可分割的工作邏輯單元，在數據庫系統上執行并發操作時，事務是最小的控制單元。
事務適用于多用戶同時操作的數據庫系統的場景，如銀行、保險公司及證券交易系統等等。
事務通過事務的整體性以保證數據的一致性。
事務能夠提高在向表中更新和插入信息期間的可靠性。
2.ACID特性
A：atomicity 原子性；整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾
C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀態,類似于質量守恒定律(A1wB 0 A1w 給 B轉1w 始終保持A+B=1w)
I： Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現并發.(不最后提交看不到，臟數
D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中
事務結構視圖：
begin #開啟事務
set autocommit=0/1 #是否自動提交事務 退出后要再加commit或者rollback
commit 保存退出 rollback 回滾示例：
（1）將自動提交事務關閉，當執行增刪改查操作時需要輸入commit或者rollback保存事務
關閉自動提交事務
select @@autocommit; # 查看自動提交事務變量是否開啟
set autocommit=0; # 關閉自動提交事務 1 為開啟
使用insert命令給teachers表中增加一個數據，先不使用commit命令在另一個終端查看
insert teachers values(5,'cxk',32,'M');
select * from teachers;
使用commit命令保存后查看
（2）當事務自動提交關閉時，刪除teachers表中所有數據，沒有使用commit提交，使用rollback回滾，查看表中數據，依然還存在
delete from teachers;
select * from teachers;
rollback; #回滾 create drop alter 這些撤回不了
select * from teachers;
數據庫事務死鎖是指兩個或多個事務在執行過程中因互相持有對方所需的資源而無法繼續執行的情況。這種情況會導致數據庫系統中的事務無法完成，從而影響系統的正常運行。
發生原因
-
資源互斥：事務在操作數據庫對象（如行、表）時會申請鎖，若多個事務同時請求同一資源且彼此互斥，可能導致死鎖。
-
循環等待：多個事務形成一個循環等待資源的鏈條，每個事務都在等待下一個事務所持有的資源，從而造成死鎖。
具體場景與解決方法
-
示例場景：
- 事務A：持有資源R1，請求資源R2。
- 事務B：持有資源R2，請求資源R1。 這種情況下，如果事務A和事務B都不能釋放當前持有的資源，它們將無法繼續執行下去，造成死鎖。
-
解決方法：
- 超時機制：數據庫系統可以設置一個超時時間，當事務持有鎖的時間超過設定閾值時，系統可以選擇終止其中一個事務，從而打破死鎖。
- 死鎖檢測與回滾：數據庫系統可以周期性地檢測是否存在死鎖，并在檢測到死鎖時，選擇其中一個事務進行回滾，釋放資源，允許其他 [Something went wrong, please try again later.]
BEGIN;
#開啟事務
update students set classid=10;
#終端1
update students set classid=20;
#終端2
show engine innodb status;
#在第三個會話中執行3.事務隔離級別
MySQL 支持四種隔離級別，事務隔離級別從上至下更加嚴格
1）未提交讀( Read Uncommitted(RU))
允許臟讀，即允許一個事務可以看到其他事務未提交的修改。2）提交讀(Read Committed(RC))
允許一個事務只能看到其他事務已經提交的修改，未提交的修改是不可見的，防止臟讀。3）可重復讀(Repeatable Read(RR))—mysql默認的隔離級別
確保如果在一個事務中執行兩次相同的SELECT語句，都能得到相同的結果，不管其他事務是否提交這些修改;可以防止臟讀和不可重復讀。
4）串行讀(Serializable)—相當于鎖表
完全串行化的讀，將一個事務與其他事務完全地隔離;每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞;
可以防止臟讀，不可重復讀取和幻讀，(事務串行化)會降低數據庫的效率。
| 隔離級別 | 臟讀 | 可重復讀 | 幻讀 | 加讀鎖 |
|---|---|---|---|---|
| 讀未提交 | 可以出現 | 可以出現 | 可以出現 | 否 |
| 讀提交 | 不允許出現 | 可以出現 | 可以出現 | 否 |
| 可重復讀 | 不允許出現 | 不允許出現 | 可以出現 | 否 |
| 序列化 | 不允許出現 | 不允許出現 | 不允許出現 | 是 |
mysql默認的事務處理級別是 repeatable read?，而Oracle和SQL Server是 read committed 。
MVCC和事務的隔離級別：
MVCC（多版本并發控制機制）只在READ COMMITTED和REPEATABLE READ兩個隔離級別下工作。其他兩個隔離級別都和MVCC不兼容,因為READ UNCOMMITTED總是讀取最新的數據行，而不是符合當前事務版本的數據行。而SERIALIZABLE則會對所有讀取的行都加鎖
select @@tx_isolation; #系統隔離級別，是系統自帶變量
示例：
（1）可重復讀(Repeatable Read(RR))—mysql默認的隔離級別
vim /etc/my.cnf
[mysqld]
transaction-isolation=REPEATABLE-READ終端1
begin;
insert teachers values(null,'cxk',32,'M');
select * from teachers;
commit;終端2
begin; #需要在終端1增加數據前操作
select * from teachers;
commit; #提交 最后操作在終端2，使用commit命令提交前，查看不到teachers表中新增的命令
當終端2使用commit命令提交后，查看teachers表中數據，可以看到增加了tid為7的數據
第一隔離級別 可看見臟讀
vim /etc/my.cnf
[mysqld]
transaction-isolation=READ-UNCOMMITTEDsystemctl restart mysqldselect @@tx_isolation; #查看隔離級別 begin;
#在其中一個終端上插入數據在未提交前,另一終端也可以看見
insert teachers values(6,'c',80,'M');
select * from teachers;串行化 最嚴格的隔離級別
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
systemctl restart mysqldbegin;
#開啟事務兩邊可以同時讀表，會互相鎖
select * from teachers;
delete from teachers where tid=5;
#無法刪除加鎖 并發性較差
三.日志
MySQL 支持豐富的日志類型，如下：
事務日志：transaction log
事務日志的寫入類型為"追加"，因此其操作為"順序IO"；通常也被稱為：預寫式日志 write ahead logging事務日志文件： ib_logfile0， ib_logfile1
錯誤日志 error log
通用日志 general log
慢查詢日志 slow query log
二進制日志 binary log
中繼日志 reley log，在主從復制架構中，從服務器用于保存從主服務器的二進制日志中讀取的事件
語法：
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body?
proc_parameter : [IN|OUT|INOUT] parameter_name type
1.事務日志
事務日志：transaction log
-
redo log：實現 WAL（Write Ahead Log) ,數據更新前先記錄redo log
-
undo log：保存與執行的操作相反的操作,用于實現rollback
事務型存儲引擎自行管理和使用，建議和數據文件分開存放
Innodb事務日志相關配置：
show variables like '%innodb_log%'; #查看與 InnoDB 存儲引擎的日志相關的配置變量
innodb_log_file_size ? 50331648 #每個日志文件大小 字節
innodb_log_files_in_group 2 ? ? #日志組成員個數
innodb_log_group_home_dir ./ #事務文件路徑#LIKE 是 SQL 的一個條件匹配操作符，用于篩選滿足指定模式的字符串。
% 是通配符，表示零個或多個字符。
'innodb_log%' 是一個模式，表示以 innodb_log 開頭的字符串。ll -h /var/lib/mysql事務日志性能優化
innodb_flush_log_at_trx_commit=0|1|2
select @@innodb_flush_log_at_trx_commit;
#查看默認值1 此為默認值，日志緩沖區將寫入日志文件，并在每次事務后執行刷新到磁盤。 這是完全遵守ACID特性
0 提交時沒有寫磁盤的操作; 而是每秒執行一次將日志緩沖區的提交的事務寫入刷新到磁盤。 這樣可提供更好的性能，但服務器崩潰可能丟失最后一秒的事務
2 每次提交后都會寫入OS的緩沖區，但每秒才會進行一次刷新到磁盤文件中。 性能比0略差一些，但操作系統或停電可能導致最后一秒的交易丟失
| 級別 | 0 | 1 | 2 |
|---|---|---|---|
| 安全性 | 較高 | 最高 | 最高 |
| 性能 | 最高 | 最差 | 較高 |
高并發業務行業最佳實踐，是使用第三種折中配置（=2）：
1.配置為2和配置為0，性能差異并不大，因為將數據從Log Buffer拷貝到OS cache，雖然跨越用戶態與內核態，但畢竟只是內存的數據拷貝，速度很快
2.配置為2和配置為0，安全性差異巨大，操作系統崩潰的概率相比MySQL應用程序崩潰的概率，小很多，設置為2，只要操作系統不奔潰，也絕對不會丟數據
雙1設置：
說明：
-
設置為1，同時sync_binlog = 1表示最高級別的容錯 （二進制日志）
-
innodb_use_global_flush_log_at_trx_commit=0 時，將不能用SET語句重置此變量（ MariaDB 10.2.6 后廢棄）
修改參數：
set global innodb_flush_log_at_trx_commit=1;
select @@innodb_flush_log_at_trx_commit;
call sp_testlog;
set global innodb_flush_log_at_trx_commit=1;
- 這是一個MySQL的系統變量設置命令。
innodb_flush_log_at_trx_commit?是控制InnoDB存儲引擎如何處理事務提交時的日志刷新行為的參數。- 設置為?
1?表示每次事務提交時都會強制刷新事務日志到磁盤，這是最安全的設置，可以確保事務的持久性（即事務提交后，數據不會丟失）。- 該設置通常用于要求高數據安全性和一致性的場景，但也可能會對性能產生一定影響。
select @@innodb_flush_log_at_trx_commit;
- 這條語句是用來查詢當前?
innodb_flush_log_at_trx_commit?參數的設置值的命令。- 如果在上一步設置成功的話，這里應該會返回?
1，表示已經將參數設置為每次事務提交時都刷新日志到磁盤。
call sp_testlog;
- 這是調用一個名為?
sp_testlog?的存儲過程（Stored Procedure）的命令。- 存儲過程是一組預先編譯好的SQL語句集合，可以在需要時通過一個單獨的調用來執行。
sp_testlog?是一個自定義的存儲過程，它可能被用來進行一些特定的測試或者操作，具體的功能需要查看存儲過程的定義來確定。
2.錯誤日志
錯誤日志
-
mysqld啟動和關閉過程中輸出的事件信息
-
mysqld運行中產生的錯誤信息
-
event scheduler運行一個event時產生的日志信息
-
在主從復制架構中的從服務器上啟動從服務器線程時產生的信息
SHOW GLOBAL VARIABLES LIKE 'log_error' ;yum ?安裝
cat /var/log/mysqld.log記錄哪些警告信息至錯誤日志文件*
#CentOS7 mariadb 5.5 默認值為1
#CentOS8 mariadb 10.3 默認值為2
log_warnings=0|1|2|3... ? ? ? ? #MySQL5.7之前
log_error_verbosity=0|1|2|3... ? #MySQL8.03.通用日志
通用日志：記錄對數據庫的通用操作，包括:錯誤的SQL語句
通用日志可以保存在：file（默認值）或 table（mysql.general_log表）
通用日志相關設置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE示例：
范例: 啟用通用日志并記錄至文件中select @@general_log; #默認沒開啟
set global general_log=1; #開啟SHOW GLOBAL VARIABLES LIKE 'log_output';
#默認通用日志存放在文件中
select @@general_log_file;
#通用日志存放的文件路徑4.慢查詢日志
慢查詢日志：記錄執行查詢時長超出指定時長的操作
慢查詢相關變量
slow_query_log=ON|OFF #開啟或關閉慢查詢，支持全局和會話，只有全局設置才會生成慢查詢文件
long_query_time=N #慢查詢的閥值，單位秒,默認為10s
slow_query_log_file=HOSTNAME-slow.log ?#慢查詢日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查詢類型且查詢時長超過long_query_time，則記錄日志
log_queries_not_using_indexes=ON ?#不使用索引或使用全索引掃描，不論是否達到慢查詢閥值的語
句是否記錄日志，默認OFF，即不記錄
log_slow_rate_limit = 1 #多少次查詢才記錄，mariadb特有
log_slow_verbosity= Query_plan,explain #記錄內容
log_slow_queries = OFF ? ?#同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已刪除set global slow_query_log=1;
#開啟
set long_query_time=1;select sleep(10)5.二進制日志 備份
-
記錄導致數據改變或潛在導致數據改變的SQL語句
-
記錄已提交的日志
-
不依賴于存儲引擎類型
功能：通過"重放"日志文件中的事件來生成數據副本
注意：建議二進制日志和數據文件分開存放
二進制日志記錄三種格式
基于"語句"記錄：statement，記錄語句，默認模式（ MariaDB 10.2.3 版本以下 ），日志量較少
基于"行"記錄：row，記錄數據，日志量較大，更加安全，建議使用的格式,MySQL8.0默認格式
混合模式：mixed, 讓系統自行判定該基于哪種方式進行，默認模式（ MariaDB 10.2.4及版本以上）
show variables like 'binlog_format';二進制日志文件格式
有兩類文件
1.日志文件：mysql|mariadb-bin.文件名后綴，二進制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,記錄當前已有的二進制日志文件列表
二進制日志相關的服務器變量：
sql_log_bin=ON|OFF：
#是否記錄二進制日志，默認ON，支持動態修改，系統變量，而非服務器選項
log_bin=mysql-bin 默認是關閉
#指定文件位置；默認OFF，表示不啟用二進制日志功能，上述兩項都開啟才可以
binlog_format=STATEMENT|ROW|MIXED：
#二進制日志記錄的格式，mariadb5.5默認STATEMENT
max_binlog_size=1073741824：
#單個二進制日志文件的最大體積，到達最大值會自動滾動，默認為1G
#說明：文件達到上限時的大小未必為指定的精確值
binlog_cache_size=4m
#此變量確定在每次事務中保存二進制日志更改記錄的緩存的大小（每次連接）
max_binlog_cache_size=512m
#限制用于緩存多事務查詢的字節大小。
sync_binlog=1|0：
#設定是否啟動二進制日志即時同步磁盤功能，默認0，由操作系統負責同步日志到磁盤
expire_logs_days=N：
#二進制日志可以自動刪除的天數。 默認為0，即不自動刪除在線查看 ?二進制
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
例子：
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3離線查看二進制日志
mysqlbinlog：二進制日志的客戶端命令工具，支持離線查看二進制日志
命令格式：
mysqlbinlog [OPTIONS] log_file…--start-position=# 指定開始位置--stop-position=#--start-datetime= ?#時間格式：YYYY-MM-DD hh:mm:ss--stop-datetime= --base64-output[=name]-v -vvv# at 328
#151105 16:31:40 server id 1 end_log_pos 431 ? Query ? thread_id=1 ? ?
exec_time=0 ? ? error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/; ?
事件發生的日期和時間：151105 16:31:40
事件發生的服務器標識：server id 1
事件的結束位置：end_log_pos 431
事件的類型：Query
事件發生時所在服務器執行此事件的線程的ID：thread_id=1
語句的時間戳與將其寫入二進制文件中的時間差：exec_time=0
錯誤代碼：error_code=0
事件內容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID例子
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog ?--start-datetime="2018-01-30 20:30:10" ? --stop-datetime="2018-01-
30 20:35:22" mariadb-bin.000003 -vvv二進制日志事件的格式：
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 ? Query ? thread_id=1 ? ?
exec_time=0 ? ? error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/; ?
事件發生的日期和時間：151105 16:31:40
事件發生的服務器標識：server id 1
事件的結束位置：end_log_pos 431
事件的類型：Query
事件發生時所在服務器執行此事件的線程的ID：thread_id=1
語句的時間戳與將其寫入二進制文件中的時間差：exec_time=0
錯誤代碼：error_code=0
事件內容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID?刪除二進制日志
purge binary logs to 'mysql-bin.000002';
#代表刪除002 之前的 日志徹底清空二進制日志
reset master;刷新日志
flush logs;
