Mysql鎖機制與最佳化實踐以及MVCC底層原理剖析

圣辉發表於2024-04-29
MySqlMVC

學習來源-圖靈課堂

https://vip.tulingxueyuan.cn

鎖學習參考：

https://juejin.cn/post/7307889500545253395

鎖機制

為了保證資料的一致性，當訪問共享變數的時候我們可以針對共享資料加鎖，但是加鎖要時要注意加鎖的成本，還有加鎖的粒度，還有就是是否會發生死鎖，還有就是發生了死鎖要如何排查，如何解決這個死鎖。

資料的修改，最好是在原來欄位的基礎上進行修改，而不是直接改成某個值，直接修改成一個值可能會導致資料的覆蓋丟失；因為修改是使用當前最新的值進行修改。修改方式

例如要對a的值進行加十操作，最好是使用如下的SQL
update table set a = a + 10 where id = 1;

這種操作是在程式碼中對a加過了十，但是可能此時資料庫的值已經被修改了
update table set a = x where id = 1;

MySQL的鎖

  • 從效能上分為樂觀鎖(用版本對比或CAS機制)和悲觀鎖，樂觀鎖適合讀操作較多的場景，悲觀鎖適合寫操作較多的場景，如果在寫操作較多的場景使用樂觀鎖會導致比對次數過多，影響效能
  • 從對資料操作的粒度分，分為表鎖、頁鎖、行鎖
  • 從對資料庫操作的型別分，分為讀鎖和寫鎖(都屬於悲觀鎖)，還有意向鎖

讀鎖（共享鎖，S鎖(Shared)）：

針對同一份資料，多個讀操作可以同時進行而不會互相影響，比如：

select * from T where id=1 lock in share mode; -- lock in share mode是手動加上讀鎖。

寫鎖（排它鎖，X鎖(eXclusive)）：

當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖，資料修改操作都會加寫鎖，查詢也可以透過for update加寫鎖，比如：
select * from T where id=1 for update; -- for update手動在查詢的時候加上寫鎖。

針對同一條資料來說其讀寫鎖：讀讀共享；讀寫互斥，寫讀互斥，寫寫互斥。共享時是可以併發操作的；互斥時就是一個執行，另外一個或者多個在阻塞中，可能超時死鎖等。

MySQL InnoDB引擎預設update,delete,insert都會自動給涉及到的資料加上排他鎖，select語句預設不會加任何鎖型別

意向鎖（Intention Lock）：

又稱I鎖，針對表鎖，主要是為了提高加表鎖的效率，是mysql資料庫自己加的。當有事務給表的資料行加了共享鎖或排他鎖，同時會給表設定一個標識，代表已經有行鎖了，其他事務要想對錶加表鎖時，就不必逐行判斷有沒有行鎖可能跟表鎖衝突了，直接讀這個標識就可以確定自己該不該加表鎖。而這個標識就是意向鎖。
因為如果沒有意向鎖時，特別是表中的記錄很多時，要對錶加表鎖需要逐行判斷加表鎖的方式效率很低。
意向鎖主要分為：
意向共享鎖，IS鎖，對整個表加共享鎖之前，需要先獲取到意向共享鎖。
意向排他鎖，IX鎖，對整個表加排他鎖之前，需要先獲取到意向排他鎖。

表鎖

每次操作鎖住整張表。開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的機率最高，併發度最低；一般用在整表資料遷移的場景。

手動增加表鎖

lock table 表名稱 read(write),表名稱2 read(write);

例如lock tables t1 read, t2 write; 命令，對t1加表級讀鎖；對t2加表級寫鎖。則其他執行緒寫 t1、讀寫 t2 的語句都會被阻塞。讀t1不阻塞。

同時，在執行 unlock tables 釋放表鎖之前，也只能執行讀 t1、讀寫 t2 的操作。不能在unlock tables之前訪問其他表。表級寫鎖中還是可以進行讀的。

表級別的讀寫鎖是嚴格互斥的。

檢視錶上加過的鎖

show open tables;

刪除表鎖

unlock tables;

頁鎖

只有BDB儲存引擎支援頁鎖，頁鎖就是在頁的粒度上進行鎖定，鎖定的資料資源比行鎖要多，因為一個頁中可以有多個行記錄。當我們使用頁鎖的時候，會出現資料浪費的現象，但這樣的浪費最多也就是一個頁上的資料行。頁鎖的開銷介於表鎖和行鎖之間，會出現死鎖。鎖定粒度介於表鎖和行鎖之間，併發度一般。
一般不使用頁鎖。

行鎖

每次操作鎖住一行資料。開銷大，加鎖慢（因為要對資料進行遍歷，獲取到資料之後才能進行加鎖操作）；會出現死鎖；鎖定粒度最小，發生鎖衝突的機率最低，併發度最高。
InnoDB相對於MYISAM的最大不同有兩點：
  • InnoDB支援事務（TRANSACTION）
  • InnoDB支援行級鎖
注意：innodb的行鎖實際上是是針對索引加的鎖(在索引對應的索引項上做標記)，不是針對整個行記錄加的鎖，這樣粒度小，效率高。但是要注意，索引不能失效；如果update語句，where條件使用了非索引的欄位，或者欄位索引失效，就會行鎖升級為表鎖；整個表都不能再進行寫。這個一定要注意。這個是針對RR級別，RC級別不會。
讀鎖會阻塞寫鎖，但是讀讀共享；寫鎖會阻塞寫鎖和讀鎖。一行資料被加了寫鎖，再顯式的加讀鎖是不行的。
注意，加了寫鎖並不是說就不能讀了，只是不能加讀鎖了，還是可以select的，因為預設的select查詢不會加鎖。
關於RR級別行鎖升級為表鎖的原因分析
因為在RR隔離級別下，需要解決不可重複讀和幻讀問題，所以在遍歷掃描聚集索引記錄時，為了防止掃描過的索引被其它事務修改(不可重複讀問題) 或 間隙被其它事務插入記錄(幻讀問題)，從而導致資料不一致，所以MySQL的解決方案就是把所有掃描過的索引記錄和間隙都鎖上，這裡要注意，並不是直接將整張表加表鎖，因為不一定能加上表鎖，可能會有其它事務鎖住了表裡的其它行記錄。

查詢鎖(行鎖)

類似於行讀鎖寫鎖。直接鎖住了這條資料。

間隙鎖(Gap Lock)

在RR隔離級別下才生效的。

是針對範圍的加鎖，並不鎖邊界，對這個範圍加鎖之後就可以阻止在這個訪問中插入資料，可以有效的防止幻讀的發生。

只要在間隙範圍內鎖了一條不存在的記錄會鎖住整個間隙範圍，不鎖邊界記錄，這樣就能防止其它Session在這個間隙範圍內插入資料，就解決了可重複讀隔離級別的幻讀問題。

SELECT * from account WHERE id = 5 for UPDATE;

這條SQL語句查詢的id是5，因為不存在，就會鎖住5，6，7這三個主鍵值，無法在這三個值中選取主鍵插入表中。

臨鍵鎖(Next-key Locks)

這個是連邊界也鎖住了，類似於大於等於，加上了等於。類似行鎖+間隙鎖。

總結

InnoDB在執行查詢語句SELECT時(非序列隔離級別)，不會加鎖。但是update、insert、delete操作會加行鎖。
另外，讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把讀和寫都阻塞。
Innodb儲存引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的效能損耗可能比表級鎖定會要更高一下，但是在整體併發處理能力方面要遠遠優於MYISAM的表級鎖定的。當系統併發量高的時候，Innodb的整體效能和MYISAM相比就會有比較明顯的優勢了。
但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體效能表現不僅不能比MYISAM高，甚至可能會更差。

鎖等待分析

透過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況

對各個狀態量的說明如下：

  • Innodb_row_lock_current_waits: 當前正在等待鎖定的數量
  • Innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度
  • Innodb_row_lock_time_avg: 每次等待所花平均時間
  • Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花時間
  • Innodb_row_lock_waits: 系統啟動後到現在總共等待的次數

對於這5個狀態變數，比較重要的主要是：

  • Innodb_row_lock_time_avg （等待平均時長）
  • Innodb_row_lock_waits （等待總次數）
  • Innodb_row_lock_time（等待總時長）

尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果著手製定最佳化計劃。

-- 檢視事務 
select * from INFORMATION_SCHEMA.INNODB_TRX; 
-- 檢視鎖 
select * from performance_schema.data_locks; 
-- 檢視鎖等待 
select * from performance_schema.data_lock_waits;
-- 釋放鎖，trx_mysql_thread_id可以從INNODB_TRX表裡檢視到 
kill trx_mysql_thread_id 
-- 檢視鎖等待詳細資訊 
show engine innodb status\G;

注意：版本是MySQL8.0

大多數情況mysql可以自動檢測死鎖並回滾產生死鎖的那個事務，但是有些情況mysql沒法自動檢測死鎖，這種情況我們可以透過日誌分析找到對應事務執行緒id，可以透過kill殺掉。
鎖最佳化實踐
  • 儘可能讓所有資料檢索都透過索引來完成，避免無索引行鎖升級為表鎖
  • 合理設計索引，儘量縮小鎖的範圍
  • 儘可能減少檢索條件範圍，避免間隙鎖
  • 儘量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql儘量放在事務最後執行
  • 儘可能用低的事務隔離級別

使用版本號對比的時候，除了要加上where後面的查詢條件，還要在後面加上一個版本號，如果版本號不一致，就不能進行修改；

CAS機制，就是比較並交換，在while true迴圈中，先對比本執行緒快取的值和要修改的目標值是否相等，如果相等就修改；如果不等，就終止本次修改，去迴圈判斷，直到能成功修改。

MVCC機制

MVCC(Multi-Version Concurrency Control)機制來保證事務的隔離性，可以在不加鎖的情況下保證讀寫互不影響。提高MySQL的整體效率。MySQL的讀已提交RC和可重複讀RR都是實現了MVCC。
可序列化為了絕對的安全，令所有的操作都序列，加鎖，效率很低下。
undo日誌版本鏈是指一行資料被多個事務依次修改過後，在每個事務修改完後，Mysql會保留修改前的資料undo回滾日誌，並且用兩個隱藏欄位trx_id和roll_pointer把這些undo日誌串聯起來形成一個歷史記錄版本鏈
可重複讀RR，就是透過undo log日誌版本鏈和read view來實現的。RR的read view更像是一個歷史的快照版本，當這個時間點進行快照之後，這個快照之後所有的提交預設對這個事務都不可見，所有能保證可重複讀級別。
實際上讀已提交RC也使用了read view；只不過是RC每次讀取都會重置版本鏈中的版本號，會找到最近一條已經commit的記錄開始讀取最新的值。也可以說是當前讀，每次讀取都是拿最新提交的值。
預設MySQL的事務id是單調遞增的。
可以認為read-view檢視中保留了建立快照時最小的一個事務id，和其中所有未提交的事務id，同時還有一個最大的事務id，這些id組成了一個陣列；主要是透過這些事務id來判斷該事務id的修改是否對當前事務可見。小於最小事務id，那麼肯定都是提交的，可見；大於最大事務id，肯定還沒有發生，不可見；在範圍內的，如果匹配上了範圍內的值，當前事務預設該事務永遠未提交，修改不可見，如果沒有匹配上範圍內的值，預設是提交的，可見。
關於readview和可見性演算法的原理解釋
  • readview和可見性演算法其實就是記錄了sql查詢那個時刻資料庫裡提交和未提交所有事務的狀態。
  • 要實現RR隔離級別，事務裡每次執行查詢操作readview都是使用第一次查詢時生成的readview，也就是都是以第一次查詢時當時資料庫裡所有事務提交狀態來比對資料是否可見，當然可以實現每次查詢的可重複讀的效果了。
  • 要實現RC隔離級別，事務裡每次執行查詢操作readview都會按照資料庫當前狀態重新生成readview，也就是每次查詢都是跟資料庫裡當前所有事務提交狀態來比對資料是否可見，當然實現的就是每次都能查到已提交的最新資料效果了。

RR級別下，讀是快照讀，一直都是歷史版本的資料，別的事務的任何修改都不能獲取到；但是如果是本事務進行編輯，那麼就是拿最新的資料去編輯，如果沒有別的事務進行編輯或者別的事務編輯已經提交或回滾，此時該事務就進行編輯，此時鎖就變成了行寫鎖，排他鎖，如果一直不提交，就會阻塞別的事務的寫操作。這個要注意。

相關文章