212_mysql_innodb_6_lock1

1? 解決并發事物帶來問題的兩種基本方式

1.1讀 – 讀情況 本身讀操作，對記錄無影響

1.2 寫 – 寫情況: 多個事物對同一條記錄修改，不能有臟寫，就得排隊，一種鎖結構（內存中）

Trx 信息：表示這個鎖結構與哪個事物關聯

Is_waiting : 表示當前是否是否在等待

T1事物修改該記錄，生成一個鎖結構與記錄關聯，之前沒有別的事物給這個記錄加鎖，is_waiting false 表示獲取鎖成功, 如果是 true 表示獲取鎖失敗

1.3 讀寫/寫讀情況

通過隔離級別進行限制 避免 臟讀/幻讀/不可重復讀，也通過以下兩種方式

MVCC

讀寫操作通過鎖方式

1.4 一致性讀（consistent read）

事物利用MVCC進行的讀取操作稱為一致性讀（快照讀/一致性無鎖讀），所有的select在（RC/RR模式下）都是一致性讀，不會對表中記錄加鎖，其它事物可以對記錄進行改動

1.5 鎖定讀

有時候讀取記錄時就希望獲得X鎖，避免其他事物的讀寫 稱之為 鎖定讀

1.5.1 共享鎖&獨占鎖

a) 共享鎖（shared lock） S 鎖，事物讀取一條記錄時候，先獲取該記錄的S鎖 對事物加 S鎖select xxx lock in share mode;

b) 獨占鎖 (exclusive lock) X 鎖，事物改動一條記錄， 先獲取該記錄的 X鎖 ??對事物加 X 鎖select xxx? for update;

1.6 寫操作 (delete / update / insert)

Delete： 1 先在B+樹中定位到記錄 2 獲取該記錄X鎖 3 執行delete mark 操作

Update：

a 存儲空間不變：1先定位 2 再獲取X鎖，

b 存儲空間改變：1 先定位B+位置 2獲取X鎖， 3刪除該記錄（加入垃圾鏈表） 4 插入新記錄

c 修改了鍵值： 1 先進行delete操作 2 再進行 insert操作

insert 新插入一條記錄受隱式鎖保護 不在內存中生成內存鎖結果

2 多粒度鎖

行鎖/表鎖? &? S / X 鎖

意向鎖（intention lock）

意向共享鎖（intention shared lock）當事物為某條記錄加S鎖，優先加IS鎖 ?屬于表級鎖

意向獨占鎖（intention exclusive lock）當事物為某條記錄加X鎖，優先IX 鎖屬于表級鎖

兼容性

X

IX

S

IS

X

不兼容

不兼容

不兼容

不兼容

IX

不兼容

兼容

不兼容

兼容

S

不兼容

不兼容

兼容

兼容

IS

不兼容

兼容

兼容

兼容

3 Mysql中的行鎖和表鎖

3.1Myisam memory merge 只有表鎖

Innodb 表級別 S/X鎖，如果dml(select/insert/delete/update)不會有表鎖（S/X）， 如果DML &DDL 會有鎖，且是MDL鎖

某個事物對某個表 執行 select insert delete update時候，其它回話執行 ddl語句會發生阻塞，是在server層通過 MDL(Metadata lock) 實現的，不會用innodb的 S/X鎖

3.2 Innodb表級鎖比較雞肋，但是提供了手動方式

a LOCK TABLES t READ?? # innodb會對t表加 S 鎖

b LOCK TBALES t WRITE? # innodb 會對 t 表加X 鎖

3.3 表級別AUTO_INC鎖 （針對auto_increment修飾的列遞增實現的兩種方式）

A 采用 AUTO-INC鎖 插入表級鎖 AUTO-INC鎖，然后給每天待插入AUTO_INCREMENT修飾的列分配遞增的值，語句執行后，釋放AUTO-INC鎖 （未釋放，其它事物等待） B 采用輕量級的鎖 未插入語句生成 AUTO_INCREMENT 修飾的列的值時獲取這個輕量級鎖（這個自增列更新完就釋放鎖）

參數? show VARIABLES like "%innodb_autoinc_lock_mode%"

innodb_autoinc_lock_mode 0 采用AUTO_INC鎖 innodb_autoinc_lock_mode 2 采用輕量鎖 innodb_autoinc_lock_mode 1 混著用

3.4 innodb中的行鎖

A Record lock 記錄鎖,僅僅把一條記錄鎖上(LOCK_REC_NOT_GAP) S/X 兩種record lock

B Gap lock? ?僅僅是為了防止插入幻讀記錄而設計的鎖，記錄加了GAP鎖不會阻礙其它事物對該記錄加recordlock或者繼續加gap

在 id=30 上加了gap 鎖，意味著 （1,30）區間內不允許立即插入新記錄，直到gap鎖釋放,

MVCC+GAP_LOCK 很大程度上解決了RR模式下的幻讀, 同時注意，為了防止（100, +無窮）在supreMum上加gap鎖

C Next-key Lock （LOCK_ORDINARY）record + gap lock，既鎖住某條記錄，同時鎖住前面的間隙 , id=30 上加了NKL 鎖，意味著(1,30]上鎖

D Insert Intention Lock (lock_insert_intention插入意向鎖)

事物A 插入記錄時候，需要判斷 gap 和 next-key-lock 鎖，如果有需要等待到釋放，處于等待狀態也需要一種鎖（insert intention lock） 當T1 提交后，T2 /T3 就能獲得插入意向鎖 （本質就是獲得把 插入意向鎖對應的鎖結構的 is_waiting 屬性改為false）

E 隱式鎖 一般正常情況下 insert不會產生鎖

為了避免臟寫現象，trx_id發揮作用 1 聚簇索引： 如果A事物插入一條記錄，該記錄的trx_id就是A事物id, 如果B事物想對該記錄加S/X鎖 A 先看該記錄的trx_id 是否是活躍事物 B 如果是活躍事物，就給當前A事物創建鎖結構（is_waiting 屬性為false）, 然后為自己B創建一個鎖結構（is_waiting屬性為false）進入等待狀態 2 二級索引： 無trx_id,但二級索引頁 page_header有 PAGE_MAX_TRI_ID屬性 功能同trx_id A 如果PAGE_MAX_TRI_ID < B事物id ,說明該頁面已經提交 B 如果 PAGE_MAX_TRI_ID > B 事物ID，說明活躍事物,需要定位到具體聚簇索引后回表根據聚簇索引的 trx_id情況進行后續操作

綜上 insert不會主動生成鎖，但是由于trx_id存在，如果針對某條記錄其它事物要做修改，其它事物B 替 A 生成了鎖，B自己進入鎖等待狀態

隱式鎖起到了延遲生成鎖的作用，且對用戶透明

4 innodb 鎖的內存結構

符合下面條件的，這些記錄的鎖可以放在同一個鎖結構中

A 在同一個事物中進行加鎖操作 B 被加鎖的記錄在同一個頁面中 C 加鎖的類型是一樣的 D 等待狀態是一樣的

1） ? ?鎖所在事物信息： 無論行鎖/標鎖，一個鎖都屬于一個事物

2） ? ?索引信息： 對于行級鎖，記錄鎖的記錄屬于哪個索引

3） ? ?表/行鎖信息： a) 行鎖記載：表空間+頁號+比特（一條記錄對應一個bit位）， ?b) 表鎖 記錄具體的表+其它信息

4） ? ?Type_mode 32比特： lock_mode + lock_type + rec_lock_type

例： T1事物想給 表空間77號，13頁面上，id =115加S鎖，

事物信息 就是T1 TRX_ID

在聚簇索引加鎖，索引信息就是 primary索引

行鎖 （spaceid 77， pageNo 113, n_bit 具體某條記錄） n_bits = (1 + (n_rec + LOCK_PAGE_BITMAP_MARGIN 64 ) /8 ) * 8

Type mode: lock_mode S鎖， lock_type: LOCK_REC; rec_log_type: LOCK_REC_NOT_GAP, IS_WAITING: FALSE

比特位：記錄具體某條記錄位置

5 語句加鎖分析

不同隔離級別加鎖行為

RC: 大部分是記錄鎖,在有外鍵時可能有所出入。

RR : 加鎖的粒度是NKL

在RR級別下的加鎖細節

原則 1：加鎖的基本單位是 next-key lock。并且next-key lock 是前開后閉區間。(5,10]

原則 2：查找過程中訪問到的索引才會加鎖

原則 3：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖(前提是這個數據在表中有)

原則 4：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖(GAP)

8019之前bug: 唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止

5.1 普通select語句

A RUC/RC 不加鎖 B RR 不加鎖，第一次select生成 readview 很大程度上避免，有一類情況特殊； readview 不能阻止A事物執行update/delete 來改動這個B事物插入的記錄 A 事物 set autocommit = 0; SELECT * from t_name where id = 3; UPDATE t_name set name = "sumi2" where id =3; SELECT * FROM t_name where id =3 ; commit; B 事物 insert into t_name values (3, “sumi”) C SERIALIZABLE a）autocommit=0時 普通select 會被轉成 select xxx lock in share Mode 語句，讀取前加S鎖， b）autocommit =1 普通select僅使用MVCC生成readview來讀取記錄, 因為自動提交以原則，一個事物只有一條SQL

5.2 鎖定讀的語句

Sql1 & sql2 是傳統鎖定讀的SQL語句， sql3 & sql4 首先定位到要修改的記錄，也是一種鎖定讀的模式 Sql1 select xxx lock in share mode; Sql2 select xxx for update; Sql3 update xxx Sql4 delete xxx

匹配模式? &? 唯一性搜索

A 匹配模式 (精確匹配 & 非精確/區間匹配) B 唯一性搜索 掃描區間最多只包含一條記錄（精確匹配, 主鍵/二級唯一索引（搜索條件不能是 索引列 is null）， 聯合索引（每個列都要用到 uk（a,b） where a=xx and b=xx）） 給記錄加鎖很多限制（隔離級別，涉及的索引，是否精確匹配，是否唯一性搜索， 具體SQL（select insert，update delete等））具體流程 1） B+樹中 定位到掃描區間中第一條記錄（可能是二級索引/ 聚簇索引等） 2） 為該記錄加鎖 小等于RC 會給記錄加 record-lock , 大等于RR 會加 next-key lock 3） 判斷索引下推條件是否成立（索引下推/減少IO）只有select 涉及索引下推且僅限二級索引, 如果符合ICP(INDEX CONDITION PUSHDOWN)就下推，不符合繼續鏈表下一個尋找 4） 執行回表操作，獲取聚簇索引記錄并給聚簇索引添加 record-lock 5） 判斷邊界條件是否成立，如果成立執行 6 ，否則 在小等于RC時，立即釋放該記錄上的record-lock, 大等于RR 不釋放record-lock, 6） Server層判斷其余條件是否滿足條件，如果滿足 返回客戶端，否則 在小等于RC時，立即釋放該記錄上的record-lock, 大等于RR 不釋放record-lock 7） 獲取鏈表下一條記錄，重復步驟2

Select * from t_name where id >1 and id <100 lock in share mode;

如果涉及二級索引

a) Rc:二級索引+ S 鎖， 聚簇索引+S鎖（不符合條件的均先加鎖/后釋放，對于邊界條件不符合下推的條件的二級索引，不會釋放鎖， innodb 無權利自動解鎖，只有server層有權利） b) RR: 二級索引 + S next-key鎖， 聚簇索引+ S record-lock (二級索引不符合條件的記錄均未釋放 next-key鎖)

Update/delete SQL 加鎖方式 與 ?SELECT XXX FOR UPDATE 語句類似，如果更新了二級索引，二級索引需要X鎖

Udpate t_name set name =”sumi” where id > 1 and id <=15 and name =25(name是索引)

掃描聚簇索引（1 ,15] 加 next-keylock，對應的二級索引 加 record lock

備注：

如果精準匹配，小等于rc不會為掃描區間后面的下一條記錄加鎖, 大等于rr 會為掃描區間后面下一條記錄加gap鎖

如果是非精準匹配，大于等rr級別 會給下一條+ next-key鎖

5.3 半一致讀的語句（Semi-consistent Read）夾在一致性讀和鎖定讀之間的讀取方式

當 小等于RC 且執行update 將使用半一致性讀, 減少阻塞，當A 事物持有 id=8的X鎖，B事物需要update id=8, innodb會將A事物 id =8 最新版本返回給server判斷，如果不符合條件，就放棄為該行上X鎖

5.4 INSERT語句

Insert 一般不會產生鎖，特殊情況：

1 遇到重復鍵（duplicate key） 會有報錯 ，報錯前還得加鎖 小等于RC 加S record lock, 大等于RR 加 next-lock

2 外鍵檢查 (檢查成功，給父表加 S record-lock， 檢查失敗 rc 不加鎖，rr 加gap鎖)

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。