MySQL - 锁机制初探
文章目錄
- 生猛干貨
- Pre
- 鎖的分類
- InnoDB 中的鎖
- 行鎖
- InnoDB 行鎖的三種算法實現(xiàn)
- Record Lock 鎖
- Gap Lock 鎖
- Next-key Lock 鎖
- 表鎖
- 表鎖的分類 IS | IX | AUTO-INC Locks
- InnoDB 自增鎖
- InnoDB 鎖關系矩陣
- InnoDB死鎖問題排查思路
- 基于資源爭用導致死鎖的情況
- Metadata lock（即元數(shù)據(jù)鎖）導致的死鎖的情況
- 開發(fā)建議
- InnoDB 加鎖行為驗證
- 主鍵 + RR
- 唯一鍵 + RR
- 非唯一鍵 + RR
- 無索引 + RR
- 搞定MySQL
生猛干貨
帶你搞定MySQL實戰(zhàn)，輕松對應海量業(yè)務處理及高并發(fā)需求，從容應對大場面試
Pre
MySQL - 解讀MySQL事務與鎖機制
MySQL - 共享鎖和排它鎖初探
MySQL - 無索引行鎖升級為表鎖
MySQL - 鎖等待及死鎖初探
鎖的分類
在 MySQL 中有三種級別的鎖：頁級鎖、表級鎖、行級鎖
- 表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。 會發(fā)生在：MyISAM、memory、InnoDB、BDB 等存儲引擎中
- 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高。會發(fā)生在：InnoDB 存儲引擎
- 頁級鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。會發(fā)生在：BDB 存儲引擎
三種級別的鎖分別對應存儲引擎關系如上圖。
Note：MySQL 中的表鎖包括讀鎖和寫鎖
InnoDB 中的鎖
在 MySQL InnoDB 存儲引擎中，鎖分為行鎖和表鎖。
行鎖
其中行鎖包括兩種鎖
-
共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數(shù)據(jù)集的排他鎖。
-
排他鎖（X）：允許獲得排他鎖的事務更新數(shù)據(jù)，阻止其他事務取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。
InnoDB 行鎖的三種算法實現(xiàn)
InnoDB 行鎖是通過對索引數(shù)據(jù)頁上的記錄（record）加鎖實現(xiàn)的。主要實現(xiàn)算法有 3 種：Record Lock、Gap Lock 和 Next-key Lock。
Record Lock 鎖
- Record Lock 鎖：單個行記錄的鎖（鎖數(shù)據(jù)，不鎖 Gap）。
Gap Lock 鎖
- Gap Lock 鎖：間隙鎖，鎖定一個范圍，不包括記錄本身（不鎖數(shù)據(jù)，僅僅鎖數(shù)據(jù)前面的Gap）。
Next-key Lock 鎖
- Next-key Lock 鎖：同時鎖住數(shù)據(jù)，并且鎖住數(shù)據(jù)前面的 Gap。
表鎖
另外，為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
表鎖的分類 IS | IX | AUTO-INC Locks
表鎖又分為三種
-
意向共享鎖（IS）：事務計劃給數(shù)據(jù)行加行共享鎖，事務在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的 IS 鎖
-
意向排他鎖（IX）：事務打算給數(shù)據(jù)行加行排他鎖，事務在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的 IX 鎖
-
自增鎖（AUTO-INC Locks）：特殊表鎖，自增長計數(shù)器通過該“鎖”來獲得子增長計數(shù)器最大的計數(shù)值
在加行鎖之前必須先獲得表級意向鎖，否則等待 innodb_lock_wait_timeout 超時后根據(jù)innodb_rollback_on_timeout 決定是否回滾事務。
InnoDB 自增鎖
在 MySQL InnoDB 存儲引擎中，我們在設計表結構的時候，通常會建議添加一列作為自增主鍵。
這里就會涉及一個特殊的鎖：自增鎖（即：AUTO-INC Locks），它屬于表鎖的一種，在 INSERT 結束后立即釋放。
我們可以執(zhí)行 show engine innodb status\G 來查看自增鎖的狀態(tài)信息。
在自增鎖的使用過程中，有一個核心參數(shù)，需要關注，即 innodb_autoinc_lock_mode,它有0、1、2 三個值。保持默認值即可。
InnoDB 鎖關系矩陣
+ 表示兼容，- 表示不兼容InnoDB死鎖問題排查思路
在 MySQL 中死鎖不會發(fā)生在 MyISAM 存儲引擎中，但會發(fā)生在 InnoDB 存儲引擎中，因為 InnoDB 是逐行加鎖的，極容易產(chǎn)生死鎖。那么死鎖產(chǎn)生的四個條件是什么呢？
-
互斥條件：一個資源每次只能被一個進程使用；
-
請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放；
-
不剝奪條件：進程已獲得的資源，在沒使用完之前，不能強行剝奪；
-
循環(huán)等待條件：多個進程之間形成的一種互相循環(huán)等待資源的關系。
在發(fā)生死鎖時，InnoDB 存儲引擎會自動檢測，并且會自動回滾代價較小的事務來解決死鎖問題。但很多時候一旦發(fā)生死鎖，InnoDB 存儲引擎的處理的效率是很低下的或者有時候根本解決不了問題，需要人為手動去解決。
排查 InnoDB 鎖問題通常有 2 種方法。
- 打開 innodb_lock_monitor 表，注意使用后記得關閉，否則會影響性能。
- 在 MySQL 5.5 版本之后，可以通過查看 information_schema 庫下面的 innodb_locks、innodb_lock_waits、innodb_trx 三個視圖排查 InnoDB 的鎖問題
基于資源爭用導致死鎖的情況
【基于資源爭用導致死鎖的情況】
session1 首先拿到 id=1 的鎖，session2 同期拿到了 id=5 的鎖后，兩者分別想拿到對方持有的鎖，于是產(chǎn)生死鎖。
Metadata lock（即元數(shù)據(jù)鎖）導致的死鎖的情況
【Metadata lock（即元數(shù)據(jù)鎖）導致的死鎖的情況】
session1 和 session2 都在搶占 id=1 和 id=6 的元數(shù)據(jù)的資源，產(chǎn)生死鎖。
查看 MySQL 數(shù)據(jù)庫中死鎖的相關信息，可以執(zhí)行 show engine innodb status\G 來進行查看，重點關注 “LATEST DETECTED DEADLOCK” 部分。
開發(fā)建議
-
更新 SQL 的 where 條件時盡量用索引
-
加鎖索引準確，縮小鎖定范圍
-
減少范圍更新，尤其非主鍵/非唯一索引上的范圍更新
-
控制事務大小，減少鎖定數(shù)據(jù)量和鎖定時間長度 （innodb_row_lock_time_avg）
-
加鎖順序一致，盡可能一次性鎖定所有所需的數(shù)據(jù)行
更多案例參考：MySQL - 鎖等待及死鎖初探
InnoDB 加鎖行為驗證
下面舉一些例子分析 InnoDB 不同索引的加鎖行為。分析鎖時需要跟隔離級別聯(lián)系起來，我們以 RR 為例，主要是從四個場景分析。
主鍵 + RR
假設條件是：
-
update t1 set name=‘XX’ where id=10
-
id 為主鍵索引。
加鎖行為：僅在 id=10 的主鍵索引記錄上加 X鎖。
唯一鍵 + RR
假設條件是：
-
update t1 set name=‘XX’ where id=10
-
id 為唯一索引。
加鎖行為：
-
先在唯一索引 id 上加 id=10 的 X 鎖。
-
再在 id=10 的主鍵索引記錄上加 X 鎖，若 id=10 記錄不存在，那么加間隙鎖。
非唯一鍵 + RR
假設條件是：
-
update t1 set name=‘XX’ where id=10
-
id 為非唯一索引
加鎖行為：
-
先通過 id=10 在 key(id) 上定位到第一個滿足的記錄，對該記錄加 X 鎖，而且要在 (6,c)~(10,b) 之間加上 Gap lock，為了防止幻讀。然后在主鍵索引 name 上加對應記錄的X 鎖；
-
再通過 id=10 在 key(id) 上定位到第二個滿足的記錄，對該記錄加 X 鎖，而且要在(10,b)~(10,d)之間加上 Gap lock，為了防止幻讀。然后在主鍵索引 name 上加對應記錄的X 鎖；
-
最后直到 id=11 發(fā)現(xiàn)沒有滿足的記錄了，此時不需要加 X 鎖，但要再加一個 Gap lock： (10,d)~(11,f)。
無索引 + RR
假設條件是：
-
update t1 set name=‘XX’ where id=10。
-
id 列無索引。
加鎖行為：
表里所有行和間隙均加 X 鎖。
搞定MySQL
總結
以上是生活随笔為你收集整理的MySQL - 锁机制初探的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL - 并发事务问题及解决方案
- 下一篇： MySQL - 高效的设计MySQL库表