第十二章 && 第十三章 && 第十四章：

12.事務

• 可用來維護數據庫的完整性，保證成批的SQL語句要么全部執行，要么都不執行

• 事務用來管理DDL 、DML 、DCL操作，如insert、update、delete語句，默認自動提交

• MySQL中事務由存儲引擎實現，在MySQL只有InnoDB存儲引擎才支持事務

操作：

1. 開啟事務：BEGIN或START TRANSACTION，任何一條DML語句執行，標志著事務的開啟

2. 提交事務：COMMIT TRANSACTION，成功的結束，將所有DML語句操作歷史記錄和底層硬盤數據來一次同步

3. 回滾事務：ROLLBACK TRANSACTION，失敗的結束，將所有DML語句操作歷史記錄全部清空

改變MySQL自動提交模式

語法：set autocommit=0 – 禁止自動提交

        set autocommit=1 -- 開啟自動提交

12.1 特性

• 原子性

• 一致性

• 隔離性

• 持久性

12.2 隔離級別

隔離：將事務與另一個事務隔離開

作用：一個事務正在操作的數據被另一事務修改或刪除，最后執行結果可能無法達到預期。如果沒有隔離性還會導致其他的問題。

• 讀未提交(read uncommitted)：一個事務可以讀取到另一個未提交事務的數據，最低級別，任何情況都無法保證，會造成臟讀。

• 讀已提交(committed)：一個事務要等另一個事務提交后才能讀取數據，可避免到臟讀的發生，會造成不可重復讀。

• 可重復讀(repeatable read)：在開始讀取數據時，不再允許修改操作，可避免臟讀、不可重復值的發生，會造成幻讀。

• 串行化(erializable)：最高的事務隔離級別，在該級別下，事務串行化順序執行，可避免臟讀、不可重復讀和幻讀。但該隔離級別效率低，耗數據庫性能，一般不用。

MySQL默認隔離級別是可重復讀。

事務隔離級別	臟讀	不可重復讀	幻讀
讀未提交	是	是	是
讀已提交	否	是	是
可重復讀	否	否	是
串行化	否	否	否

13.鎖機制

鎖是計算機協調多個進程或線程并發訪問某一資源的機制

鎖沖突也是影響數據庫并發訪問性能的一個重要因素

按數據操作的粒度分類：

• 表鎖：操作時，會鎖定整個表

• 行鎖：操作時，會鎖定當前操作行

按數據操作的類型分類：

讀鎖 (共享鎖) ：針對同一份數據，多個讀操作可同時進行而不會互相影響

寫鎖 (排它鎖) ：當前操作沒完成前，它會阻斷其他寫鎖和讀鎖

13.1 各存儲引擎對鎖的支持狀況：

存儲引擎	表級鎖	行級鎖
MyISAM	支持	不支持
InnoDB	支持	支持
MEMORY	支持	不支持
BDB	支持	不支持

13.2 鎖特性

鎖類型	特性
表級鎖	偏向MyISAM存儲引擎，開銷小，加鎖快，不會出現死鎖；鎖定粒度最大，發生鎖沖突的概率最高，并發度最低
行級鎖	偏向InnoDB存儲引擎，開銷大，加鎖慢，會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度最高

• 表級鎖適用于以查詢為主，只有少量按索引條件更新數據的應用

• 行級鎖適用于有大量按索引條件并發跟新少量不同數據，同時又有并查詢的應用

13.3 MyISAM表鎖

13.3.1 加表鎖

• MyISAM在執行查詢語句前，會自動給涉及的所有表加讀鎖，在執行更新操作前，會自動給涉及的表加寫鎖

lock table table_name read;  -- 加讀鎖lock table table_name write; -- 加寫鎖

13.4 InnoDB行鎖

13.4.1 行鎖特點

• 偏向InnoDB存儲引擎，開銷大，加鎖慢，會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度最高

InnoDB與MyISAM最大不同的兩點：

1. 支持事務

2. 采用行級鎖

兩種類型行鎖：

• 共享鎖 (S) ：又稱讀鎖，簡稱S鎖，共享鎖就是多個事務對于同一數據可共享一把鎖，都能訪問數據，但只能讀不能修改

• 排他鎖 (X) ：又稱寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存

對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖；對于普通SELECT語句，InnoDB不會加任何鎖

13.4.2 行鎖模式

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享
SELECT * FROM table_name WHERE ... FOR UPDATE -- 排他鎖

• 顯示給記錄集加共享鎖或排他鎖

14.日志

作用：記錄著數據庫工作的方方面面，幫助數據庫管理員追蹤數據庫曾經發生過的各種事件

分類：

• 錯誤日志

• 二進制日志

• 查詢日志

• 慢查詢日志

14.1 錯誤日志

它記錄了mysqld啟動和停止時，以及服務器在運行過程中發生任何嚴重錯誤時的相關信息

錯誤日志默認開啟，默認存放目錄為mysql的數據目錄，默認日志文件名為hostname.err(hostname 主機名)

語法：show variables like ‘log_error%’;

• 該指令查看日志位置

14.2 二進制日志

它記錄了所有的DDL語句和DML語句，但不包括數據查詢語句，該日志對災難時的數據恢復起著重要作用，MySQL主從復制，就是通過該日志實現

二進制文件，8.0版本默認已開啟，低版本需通過配置文件開啟，并配置MySQL日志的格式

Windows：my.ini Linux：my.cnf

-- 配置開啟binlog日志，日志文件前綴為mysqlbin
log_bin=mysqlbin-- 配置二進制日志的格式
binlog_format=STATEMENT-- 在my.ini文件中進行配置，配置完后需重啟服務

14.2.1 日志格式

• STATEMENT：記錄的都是SQL語句，每一條對數據進行修改的SQL都會被記錄在日志文件中

• ROW：記錄的是每一行的數據變更

• MIXED：STATEMENT和ROW兩種格式的混合

14.3 查詢日志

查詢日志中記錄了客戶端的所有操作語句

默認情況下，查詢日志未開啟，若需要開啟，可進行配置

-- 該選項用作開啟查詢日志 1：開啟 0：關閉
general_log=1-- 設置日志的文件名，若沒指定，默認文件名為host_name.log
general_log_file=file_name

14.4 慢查詢日志

它記錄了所有執行事件超過參數long_query_time 設置值并且掃描記錄不小于min_examined_row_limit 的所有QL語句的日志。

long_query_time默認為10秒，最小為0，精度可達到微秒

-- 控制慢查詢日志是否開啟 1：開啟 0：關閉
slow_query_log=1-- 指定慢查詢日志的文件名
slow_query_log_file=slow_query.log-- 配置查詢的時間限制，超過該時間將被認定慢查詢，將需要進行日志記錄，默認10秒
long_query_time=10