MySQL事務和鎖機制

MySQL技術——事務和鎖機制

- 一、事務
- - （1）概述
  - （2）ACID特性
  - （3）事務并發存在的問題
  - （4）事務的隔離級別
- 二、鎖機制
- - （1）鎖的力度
  - （2）表的分類：排他鎖、共享鎖
  - （3）串行化解決幻讀（虛讀）問題：間隙鎖
  - （4）意向共享鎖和意向排他鎖
  - （5）死鎖
  - （6）鎖的優化建議
- 三、多版本并發控制（MVCC）
- 四、MySQL優化問題
- 五、總結

一、事務

（1）概述

事務是數據庫區別于文件系統的重要特征之一。在文件系統中，如果正在寫文件，但是操作系統奔潰了，這個文件就很有可能被破壞。當然，有一些機制可以把文件回復到某個時間點。不過，如果需要保證兩個文件同步，這些文件系統可能就顯得無能為力了，例如，當需要更新兩個文件時，更新完一個文件后，在更新完第二個文件之前系統重啟了，就會產生兩個不同步的文件，因此這也就是數據庫系統引入事務的主要目的

一個事務是由一條或者多條數據庫操作SQL語句所組成的一個不可分割的單元，只有當事務中的所有操作都正常執行完畢后，整個事務才會被提交給數據庫；如果由部分事務處理失敗，那么事務就要回退到最初的狀態。因此事務要么全部執行成功，要么全部失敗

（2）ACID特性

每一個事務都必須滿足下面的4個特性：

事務的原子性（Atomic）： 事務是一個不可分割的整體，事務必須具有原子特性，要么全執行，要么全不執行
事務的一致性（Consistency）： 一個事務執行之前和執行之后，數據庫數據必須保持一致性狀態。數據庫的一致性狀態必須由用戶來負責，由并發控制機制實現
事務的隔離性（Isolation）： 當兩個或者多個事務并發執行時，為了保證數據的安全性，將一個事務內部的操作與其他事務的存在隔離起來，不被其他正在執行的事務看到，使得并發執行的各個事務之間不能相互影響 | 鎖 + MVCC
事務的持久性（Durability）： 事務完成(commit)后，DBMS保證它對數據庫中的數據是永久性的，即使數據庫因為故障出錯，也應該能夠快速恢復數據

（3）事務并發存在的問題

如果事務處理不經過隔離，并發執行事務是通常會發生以下問題：

臟讀（Dirty Read）： 一個事務讀取了另一個事務為提交的數據，例如當事務A和事務B并發執行時，當事務A更新后，事務B查詢讀取到事務A尚未提交的數據，此時事務A回滾，則事務B讀到的數據就是無效的臟讀數據（事務B讀取到來事務A未提交的數據）
不可重復讀（NonRepeatable Read）： 一個事務的操作導致另一個事務前后兩處讀取到不同的數據。即當事務A和事務B并發執行時，當事務B查詢讀取到數據后，事務A更新操作更改事務B查詢到的數據，此時事務B再次去讀取該數據，發現前后兩處讀的數據不一樣（事務B讀取了事務A已經提交的數據）
虛讀 / 幻讀（Phantom Read）： 一個事務的操作導致另一個事務前后兩次查詢的結果數據量不同，即當事務A和事務B并發執行時，當事務B查詢讀取數據后，事務A新增或者刪除了一條滿足事務B查詢條件的記錄，此時事務B再去查詢，發現查詢到前一次不存在的記錄，或者前一次查詢的一些記錄不見了（事務B讀取了事務A新增的數據，或者讀不到事務A刪除的數據）

（4）事務的隔離級別

MySQL默認支持四種隔離級別，如下圖：

隔離級別	臟讀	不可重復讀	虛讀 / 幻讀
未提交讀：TRANSACTION_READ_UNCOMMITTED	可以	可以	可以
已提交讀：TRANSACTION_READ_COMMITTED	不可以	可以	可以
可重復讀：TRANSACTION_REPEATABLE_READ	不可以	不可以	update 可以
串行化： TRANSACTION_SERIALIZABLE	不可以	不可以	不可以

串行化是最高的事務級別，由于事務隔離級別越高，為避免沖特所花費的性能也越多
在“可重復讀”級別中，實際上是可以解決部分的虛讀問題，但是不能防止update更新產生的虛讀問題，還是要串行化隔離級別

select @@transation_isolation; mysql默認的隔離級別：可重復讀

我們現在一一列舉各種隔離級別會出現的結果

未提交讀 READ_UNCOMMITTED

已提交讀 READ_COMMITTED

可重復讀 REPEATABLE-READ，這里對于幻讀就不演示了，可以自行驗證

串行化 SERIALIZABLE

二、鎖機制

（1）鎖的力度

鎖的力度分為兩種：

表級鎖：對整張表加鎖，開銷小，加鎖塊，不會出現死鎖；鎖的力度大，發送鎖沖突的概率高，并發度低
行級鎖：對某行記錄加鎖。開銷大，加鎖慢，會出現死鎖；鎖定力度最小，發生鎖沖突的概率最低，并發度高
- InnoDB行鎖是通過給索引上的索引項加鎖來實現的，而不是給表的行記錄加鎖實現的，這就意味著只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則InnoDB將使用表鎖。
- 由于InnoDB的行鎖實現是針對索引字段添加的鎖，不是針對行記錄加的鎖，因此雖然訪問的是InnoDB引擎下表的不同行，但是如果使用相同的索引字段作為過濾條件，依然會發生鎖沖突，只能串行進行，不能并發進行。
- 即使SQL中使用了索引，但是經過MySQL的優化器后，如果認為全表掃描比使用索引效率更高，此時會放棄使用索引，因此也不會使用行鎖，而是使用表鎖，比如對一些很小的表，MySQL就不會去使用索引。

（2）表的分類：排他鎖、共享鎖

共享鎖（Shared）
排他鎖（Exclusive）：又稱X鎖，寫鎖
共享鎖（Shared）：又稱S鎖，讀鎖

X和S鎖之間由以下關系：SS可以兼容，SX、XX、XS之間是互斥的

一個事務對數據對象O加了S鎖，可以對O進行讀取操作但不能進行更新操作。加鎖期間其他事務能對O加S鎖但不能加X鎖
一個事務對數據對象O加了X鎖，就可以對O進行讀取和更新。加鎖期間其他事務不能對O加任何鎖

顯示加鎖語句
select ... lock in share mode;  #強制獲取共享鎖
select ... for update;	   #獲取排他鎖

下面我們來演示一下，兩種鎖的效果

假設我們開啟了兩個事務，默認工作在隔離級別為可重復讀，兩個事務先后執行以下語句 select * from stu where id = 2 for update，根據上述推導，XX是不可兼容的，所以前一個執行sql語句的事務會對當前查詢的記錄加一把排他鎖，后執行sql語句的事務肯定會被阻塞；若后執行sql語句的事務換成執行... lock in share mode，同樣也是阻塞，因為XS也是不兼容的；若后執行的事務獲取其他記錄的鎖，如id = 3 for update，該記錄沒有加X鎖，因此可以執行

我們再來看看另外一條sql語句 select .. name = 'aaa' for update

select 'bbb'竟然也會阻塞！！！
這里注意：InnoDB的行鎖是加在索引項上面的，并不是單純的給行記錄加鎖；如果過濾條件沒有索引的話，name只是普通字段，使用的就是表鎖，而不是行鎖！！！
這里如果我們給name字段添加索引的話，就是加行鎖，使得’bbb’能夠執行

我們來看看隔離級別為 SERIALIZABLE的結果

（3）串行化解決幻讀（虛讀）問題：間隙鎖

當我們用范圍條件而不是等值條件去查詢數據時，并請求共享或者排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對于鍵值在條件范圍內但是并不存在的記錄，叫做“間隙（GAP)”,InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖。

目的：防止幻讀，以滿足川=串行化隔離級別的要求

鎖的三張算法：

record lock：行鎖
gap lock：間隙鎖
next-key lock：record lock + gap lock

示例1 范圍查詢：按過濾條件分為：使用主鍵索引或者輔助索引

示例二等值查詢

（4）意向共享鎖和意向排他鎖

在使用表鎖的時候，涉及到的效率問題
要獲取一張表的S或者X鎖，需要檢查這張表沒有被其他事務獲取過X鎖，這表里的數據沒有被其他事務獲取過行鎖X鎖，那么如果這張表有非常多的數據，是一行一行的檢查？？？
意向共享鎖和意向排他鎖就是用來解決這個問題

意向共享鎖（IS鎖）： 事務計劃給記錄加行共享鎖，事務在給一行記錄加共享鎖前，必須先取得該表的IS鎖
意向排他鎖（IX鎖）： 事務計劃給記錄加行排他鎖，事務在給一行記錄加排他鎖前，必須先取得該表的IX鎖

當我們需要獲取表的X鎖時，不需要再檢查表中的哪些行被（X或者S）鎖占用，只需要檢查IX和IS鎖即可！

如下圖的 X和S都是表鎖

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	兼容	Conflict	兼容
S	Conflict	Conflict	兼容	兼容
IS	Conflict	兼容	兼容	兼容

1、意向鎖是由InnoDB存儲引擎獲取行鎖之前自己獲取的
2、意向鎖之間都是兼容的，不會產生沖突
3、意向鎖存在的意義是為了更高效的獲取表鎖
4、意向鎖是表級鎖，協調表鎖和行鎖的共存關系，主要目的是顯示事務正在鎖定某行或者試圖鎖定某行。

（5）死鎖

MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次獲得所需的所有鎖，要么全部滿足，要么等待，因此不會發生死鎖。但在InnoDB中，出來的單個SQL組成的事務外，鎖是逐步獲得的，即鎖的力度比較小，發生死鎖也是可能的，如下：

死鎖問題一般都是我們自己的應用造成的，和多線程編程的死鎖情況相似，大部分都是由于我們多個線程在獲取多個鎖資源的時候，獲取的順序不同而導致的死鎖問題。因此我們應用在對數據庫的多個表做更新的時候，不同的代碼段，應對這些表按相同的順序進行更新操作，以防止鎖沖突導致死鎖問題。

（6）鎖的優化建議

盡量使用較低的隔離級別
設計合理的所有并盡量使用索引訪問數據，使鎖更加準確，減少鎖沖突的機會提高并發能力
選擇合理的事務大小，小事務發生鎖沖突概率小
不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對于一個表而言，盡可能以固定的順序存取表中的行，這樣也能減少死鎖的發生
盡量用相等條件訪問數據，這樣可以避免間隙鎖對并發插入的影響
不要申請超過實際需要的鎖級別
除非必須，查詢時不要顯示加鎖

三、多版本并發控制（MVCC）

MVCC（Multi-Version Concurrency Control，簡稱MVCC），是MySQL中基于樂觀鎖理論實現隔離級別的方式，用于已提交讀和可重復讀隔離級別的實現，也經常被稱為多版本并發控制。MVCC機制會生成一個數據請求時間點的一致性數據快照（Snapshot），并用這個快照來提供一定級別的一致性讀取。從用戶的角度來看，就是數據庫提供同一組數據的多個版本（系統版本號和事務版本號）。

MVCC多版本控制的讀操作分為兩類：

快照讀：讀的是記錄的可見版本，不用加鎖，如select
當前讀：讀的是記錄的最新版本，并且返回當前的記錄，如insert，delete，select，update，select … lock in share mode / for update

MVCC： 每一行記錄實際上有多個版本，每個版本的記錄除了數據本身之外，增加了其它字段

DB_TRX_ID：記錄當前事務ID
DB_ROLL_PTR：指向undo log日志上數據的指針

已提交讀： 每次執行語句的時候都重新生成一次快照（Read View），每次select查詢時。
可重復讀： 同一個事務開始的時候生成一個當前事務全局性的快照（Read View），第一次select查詢時。

快照讀取原則：

版本未提交無法生成快照
版本已提交，但是在快照創建后提交，無法讀取
版本已提交，但是在快照創建前提交，可以讀取
當前事務內自己更新，可以讀到

在這里插入圖片描述

InnoDB提供了兩個讀取操作：

鎖定讀： S、X鎖
非鎖定讀： 其依賴于 undo log 回滾日志

undo log：回滾日志，保存了事務發生之前的數據的一個版本，用于執行時的回滾操作，同時也是實現多版本并發控制（MVCC）下的關鍵技術
在這里插入圖片描述

redo log：重做日志，用于記錄事務操作的變化，確保事務的持久性。redo log是在事務開始后就開始記錄，不管事務是否提交都會記錄下來，在異常發生時（如數據持久化過程中掉電），InnoDB會使用redo log恢復到掉電前的時刻，保證數據的完整性。InnoDB修改操作數據時，不是直接修改磁盤上的數據，實際只是修改Buffer Pool中的數據。InnoDB總是先把Buffer Pool中的數據改變記錄到redo log中，用來進行崩潰后的數據恢復。優先記錄redo log，然后再由專門的線程將Buffer Pool中的臟數據刷新到磁盤上。
在這里插入圖片描述

四、MySQL優化問題

對于MySQL優化問題分為3種：

SQL和索引的優化： 慢查詢日志 -》根據表的數據量等設置合理的慢查詢時間 -》記錄慢查詢sql -》 explain 分析sql執行計劃 -》優化措施
應用上的優化：
- 連接數據庫：訪問頻繁，引入連接池中間件
- 引入緩存（存儲熱點數據） redis（重點！！！）
  - 緩存數據一致性問題？
  - 緩存穿透
  - 緩存雪崩
  - redis還有哪些功能？

mysql server上的優化： 各種參數配置！
- MySQL的查詢緩存 query_cache_type 、query_cache_size
- 索引和數據緩存， innodb_buffer_pool_size越高，磁盤IO越少
- MySQL線程緩存thread_cache_size
- 并發連接數量和超時時間 max_connections、wait_timeout