1. 概念梳理

根據(jù)加鎖的范圍，MySQL里面的鎖大致可以分為：全局鎖、表級鎖、行鎖三類。

1.1. 全局鎖

全局鎖就是對整個數(shù)據(jù)庫實(shí)例加鎖。MySQL提供了一個加全局讀鎖的方法，命令是 flush tables with read lock (FTWRL)。當(dāng)你需要讓整個庫處于只讀狀態(tài)的時候，可以使用這個命令，之后其他線程的以下語句會被阻塞：數(shù)據(jù)更新語句（增刪改）、數(shù)據(jù)定義語句（包括建表、修改表結(jié)構(gòu)等）、更新類事務(wù)的提交語句。

全局鎖的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都select出來存成文本。

以前使用FTWRL備份，不好的點(diǎn)是：

1. 如果你在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺；
2. 如果你在從庫上備份，那么備份期間從庫不能執(zhí)行主庫同步過來的binlog，會導(dǎo)致主從延遲。

FTWRL不好，為什么有時還需要呢？因?yàn)镸yISAM引擎不支持事務(wù)，無法做到細(xì)粒度的鎖，這時只能使用FTWRL命令。

1.2. 表級鎖

MySQL里面表級別的鎖有兩種：表鎖、元數(shù)據(jù)鎖（metadata lock，MDL)。

1.2.1. 表鎖

表鎖的語法是 lock tables xxx read/write ，與FTWRL類似，可以用unlock tables主動釋放鎖，也可以在客戶端斷開的時候自動釋放。

舉例, 如果在某個線程A中執(zhí)行 lock tables t1 read, t2 write; 這個語句，則其他線程寫t1、讀寫t2的語句都會被阻塞。同時，線程A在執(zhí)行unlock tables之前，也只能執(zhí)行讀t1、讀寫t2的操作。連寫t1都不允許，自然也不能訪問其他表。

還沒有出現(xiàn)更細(xì)粒度的鎖的時候，表鎖是最常用的處理并發(fā)的方式。而對于InnoDB這種支持行鎖的引擎，一般不使用lock tables命令來控制并發(fā)，畢竟鎖住整個表的影響面還是太大。

1.2.2. MDL-(metadata lock)

場景：如果一個查詢正在遍歷一個表中的數(shù)據(jù)，而執(zhí)行期間另一個線程對這個表結(jié)構(gòu)做變更，刪了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對不上。

為解決這種問題，MySQL在5.5版本中引入了MDL。MDL不需要顯式使用，在訪問一個表的時候會被自動加上，其作用是保證讀寫的正確性。

當(dāng)對一個表做增刪改查操作的時候，加MDL讀鎖；當(dāng)要對表做結(jié)構(gòu)變更操作的時候，加MDL寫鎖。

• 讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。
• 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。

1.3. 行鎖

行鎖就是針對數(shù)據(jù)表中行記錄的鎖。

MySQL的行鎖是在引擎層由各個引擎自己實(shí)現(xiàn)的。但并不是所有的引擎都支持行鎖，比如MyISAM引擎就不支持行鎖。不支持行鎖意味著并發(fā)控制只能使用表鎖，對于這種引擎的表，同一張表上任何時刻只能有一個更新在執(zhí)行，這就會影響到業(yè)務(wù)并發(fā)度。InnoDB是支持行鎖的，這也是MyISAM被InnoDB替代的重要原因之一。

下面討論 InnoDB 的行鎖。

1.3.1. 兩階段鎖

兩階段鎖協(xié)議：在InnoDB事務(wù)中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時才釋放。

樣例（id為主鍵）：

事務(wù)B的update語句會被阻塞，直到事務(wù)A執(zhí)行commit之后，事務(wù)B才能繼續(xù)執(zhí)行。事務(wù)A持有的兩個記錄的行鎖，都是在commit的時候才釋放的。

所以在使用事務(wù)時，如果你的事務(wù)中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放。

1.3.2 事務(wù)

1.3.2.1. 事務(wù)基本要素（ACID）

• 原子性（Atomicity）：事務(wù)開始到結(jié)束，中間所有操作為一個整體的原子操作，不可分割，要么全部成功，要么全部失敗。
• 一致性（Consistency）：事務(wù)開始前和結(jié)束后，數(shù)據(jù)庫的完整性約束沒有被破壞 。比如A向B轉(zhuǎn)賬10元，A減少和B增加數(shù)值一樣，不多不少。
• 隔離性（Isolation）：同一時間，只允許一個事務(wù)請求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒有任何干擾。比如賬戶A正在取錢減少，再次過程結(jié)束前，其他賬戶不能向A匯入金額。
• 持久性（Durability）：事務(wù)完成后，事務(wù)對數(shù)據(jù)庫的所有更新將被保存到數(shù)據(jù)庫，不能回滾。

1.3.2.2. 事務(wù)讀現(xiàn)象問題

• 丟失更新 （事務(wù)ACID保證不會發(fā)?）
• 臟讀：讀到了其他事務(wù)未提交的修改數(shù)據(jù)，這些數(shù)據(jù)可能回滾
• 不可重復(fù)讀：A事務(wù)第一次讀取數(shù)據(jù)，B事務(wù)修改了該數(shù)據(jù)并提交，A再讀取拿到B修改后數(shù)據(jù)，兩次數(shù)據(jù)不一樣
• 幻讀：A事務(wù)第一次查到符合條件的m條數(shù)據(jù)，B事務(wù)新增或刪除了符合條件的n條數(shù)據(jù)，A再查詢獲得m±n條數(shù)據(jù)

不可重復(fù)讀與幻讀的區(qū)別是不可重復(fù)讀是針對修改，幻讀是針對插入和刪除。

1.3.2.3. 事務(wù)隔離級別

• 讀未提交（Read-Uncommitted，RU）：可讀取未提交的事務(wù)的操作數(shù)據(jù)。（很少用到）
• 讀已提交（Read-Committed，RC）：一個事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變，存在不可重復(fù)讀和幻讀。（其他數(shù)據(jù)庫默認(rèn)，非MySQL）（常用）
• 可重復(fù)讀（Repeatable-Read，RR）：同一事務(wù)的多個實(shí)例在并發(fā)讀取數(shù)據(jù)時，會看到同樣的數(shù)據(jù)行。對讀取到的記錄加鎖（?鎖），存在幻讀現(xiàn)象，InnoDB通過MVCC解決了該問題。（MySQL默認(rèn)級別）（常用）
• 可串行化（Serializable）：從MVCC并發(fā)控制退化為基于鎖的并發(fā)控制。所有的讀操作都為當(dāng)前讀，讀加讀鎖（S鎖），寫加寫鎖（X鎖）。Serializable隔離級別下，讀寫沖突，并發(fā)性能急劇下降（基本不會用）

1.3.3. InnoDB 鎖類型

1. Shared Locks and Exclusive Locks - 共享鎖（S鎖）和排他鎖（X鎖）

   • 共享鎖（S Lock）：允許事務(wù)讀取一行數(shù)據(jù)，多個事務(wù)可以拿到一把S鎖（即讀讀并行），例如 select * from xx where a=1；
   • 排他鎖（X Lock）：允許事務(wù)刪除或更新一行數(shù)據(jù)，多個事務(wù)有且只有一個事務(wù)可以拿到X鎖（即寫寫/寫讀互斥），例如 select * from xx where a=1 for update；

   S 和 S 兼容， X 和 S 互斥， X 和 X 互斥

2. Intention Locks - 意向鎖
   InnoDB 為了支持多粒度鎖（multiple granularity locking），引入意向鎖（一種表鎖），它允許行級鎖與表級鎖共存。

   • 意向共享鎖（IS Lock）：事務(wù)想要獲得一張表中某幾行的共享鎖；
   • 意向排他鎖（IX Lock）：事務(wù)想要獲得一張表中某幾行的排他鎖；

   事務(wù)在請求S鎖和X鎖前，需要先獲得對應(yīng)的IS、IX鎖，表明“某個事務(wù)正在某一行上持有了鎖，或者準(zhǔn)備去持有鎖”。

   舉個例子，事務(wù)1在表1上加了S鎖后，事務(wù)2想要更改某行記錄，需要添加IX鎖，由于不兼容，所以需要等待S鎖釋放；如果事務(wù)1在表1上加了IS鎖，事務(wù)2添加的IX鎖與IS鎖兼容，就可以操作，這就實(shí)現(xiàn)了更細(xì)粒度的加鎖

3. Record Locks - 記錄鎖（locks rec but not gap）
   記錄鎖是的單個行記錄上的鎖，鎖在索引記錄上。會阻塞其他事務(wù)對其插入、更新、刪除；

4. Gap Locks - 間隙鎖
   間隙鎖鎖定記錄的一個間隔范圍，但不包含記錄本身。間隙鎖都是左開右閉原則。

   例子： 數(shù)據(jù)庫已有id為2,6兩條記錄，已有三個間隙鎖(-∞,2] (2,6] (6,+∞]。事務(wù)A現(xiàn)在想要在(4,10)之間更新數(shù)據(jù)的時候（id=5的數(shù)據(jù)），會加上間隙鎖select * from t where id>4 and id<10 for update，鎖住(2,6] (6,+∞]； 如果不加間隙鎖，事務(wù)B有可能會在(4,10)之間插入一條數(shù)據(jù)，這個時候事務(wù)A再去更新，發(fā)現(xiàn)在(4,10)這個區(qū)間內(nèi)多出了一條“幻影”數(shù)據(jù)。

   間隙鎖就是防止其他事務(wù)在間隔中插入數(shù)據(jù)，以導(dǎo)致“不可重復(fù)讀”。

5. Next-Key Locks - 臨鍵鎖=Gap Lock + Record Lock
   臨建鎖是記錄鎖與間隙鎖的組合。即：既包含索引記錄，又包含索引區(qū)間，主要是為了解決幻讀。

6. Insert Intention Locks - 插入意向鎖
   插入意向鎖是間隙鎖的一種，專門針對insert的。即多個事務(wù)在同一個索引、同一個范圍區(qū)間內(nèi)插入記錄時，如果插入的位置不沖突，則不會阻塞彼此；

   例子：在可重復(fù)讀隔離級別下，對PK id為10-20的數(shù)據(jù)進(jìn)行操作：
   事務(wù)A在10-20的記錄中插入了一行： insert into t value(11, xx)
   事務(wù)B在10-20的記錄中插入了一行： insert into t value(12, xx)
   由于兩條插入的記錄不沖突，所以會使用插入意向鎖，且事務(wù)B不會阻塞。

7. Auto-inc Locks - 自增鎖
   自增鎖是一種特殊的表級別鎖，專門針對事務(wù)插入AUTO-INCREMENT類型的列。 即一個事務(wù)正在往表中插入記錄時，其他事務(wù)的插入必須等待，以便第1個事務(wù)插入的行得到的主鍵值是連續(xù)的。

8. Predicate Locks for Spatial Index - 空間索引的謂詞鎖（忽略）

兼容矩陣（?-兼容，?-互斥）：

1.3.4. MVCC

MVCC(Mutil-Version Concurrency Control，多版本并發(fā)控制)，是一種并發(fā)控制的方法，一般在數(shù)據(jù)庫管理系統(tǒng)中，實(shí)現(xiàn)對數(shù)據(jù)庫的并發(fā)訪問。

InnoDB引擎中就是只在已提交讀(RC)和可重復(fù)讀(RR)這兩種隔離級別下的事務(wù)對于SELECT操作會訪問版本鏈中的記錄的過程。這就使得別的事務(wù)可以修改某行記錄，每次修改都會在版本鏈中記錄，SELECT時可以去版本鏈中拿記錄，這就實(shí)現(xiàn)了讀-寫，寫-讀的并發(fā)執(zhí)行，提升了系統(tǒng)的性能。

1.3.4.1. ROW記錄格式

InnoDB內(nèi)部為每一行添加了兩個隱藏列：DB_TRX_ID和DB_ROLL_PTR（MySQL另外還有一個隱藏列DB_ROW_ID，這是在InnoDB表沒有主鍵的時候用此作為主鍵）

ROW記錄格式.png

• DB_TRX_ID：長度為6字節(jié)，該行記錄最后一次更改的事務(wù)ID
• DB_ROLL_PTR：長度為7字節(jié)，回滾指針，指向?qū)懭牖貪L段的Undo log記錄

1.3.4.2. ROW和Undo關(guān)系

ROW和Undo關(guān)系.png

1.3.4.3. ReadView判斷

讀已提交（RC）和可重復(fù)讀（RR）的區(qū)別就在于它們生成ReadView的策略不同。

事務(wù)版本ID是遞增的

ReadView原理主要是其中有個列表來存儲系統(tǒng)中當(dāng)前活躍著的讀寫事務(wù)（事務(wù)begin，還未commit）。通過這個列表和某行記錄的版本鏈比較，判斷出該記錄的哪個版本對當(dāng)前事務(wù)可見。計(jì)較判斷遵循從尾部向前查找。

舉個例子（以ID=1的記錄為例）：

ReadView查詢示例.png

①在事務(wù)trx20時候修改成name=A，并已提交；事務(wù)trx30修改成name=B，還未提交；
②來了一個事務(wù)trx35 select這個記錄，查詢前先生成一個ReadView，此時里面有[30]（注意如果還有其他事務(wù)修改ID1，ReadView列表就有兩個值，以此類推）；
③比較判斷，發(fā)現(xiàn)trx30在ReadView，對本事務(wù)不可見，根據(jù)DB_ROLL_PTR查詢上一個事務(wù)，找到trx20，發(fā)現(xiàn)trx20<ReadView中的30，可見，select的結(jié)果是name=A；
RC與RR區(qū)別
④如果trx30提交，再起一個事務(wù)trx40，修改name=C，并未提交；
⑤trx35 再次select查詢：RC，此時會重新生成一個ReadView，里面是[40]，可查到name=B；RR，此時的ReadView是第一次查詢是生成的，查到的name=A；

讀已提交隔離級別下的事務(wù)在每次查詢的開始都會生成一個獨(dú)立的ReadView，而可重復(fù)讀隔離級別則在第一次讀的時候生成一個ReadView，之后的讀都復(fù)用這個ReadView。

2. 死鎖

當(dāng)并發(fā)系統(tǒng)中不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導(dǎo)致這幾個線程都進(jìn)入無限等待的狀態(tài)，稱為死鎖。

2.1. 發(fā)生死鎖的條件

• 2個或2個以上事務(wù)（線程）
• 不同方向
• 相同鎖資源

2.2. 解決死鎖的方法

• 超時等待：設(shè)置innodb_lock_wait_timeout(InnoDB默認(rèn)50s)當(dāng)兩個事務(wù)互相等待時，當(dāng)一個事務(wù)等待時間超過設(shè)置的閾值時，就將其回滾，另外事務(wù)繼續(xù)進(jìn)行。（缺點(diǎn)：如果回滾的事務(wù)更新了很多行，占用了較多的undo log，那么在回滾的時候花費(fèi)的時間比另外一個正常執(zhí)行的事務(wù)花費(fèi)的時間可能還要多，就不太合適；還有等待時間設(shè)置大了服務(wù)等待過長，設(shè)小了會誤傷正常的鎖等待事務(wù)）

• 等待圖（wait-for graph），死鎖碰撞檢測：是一種較為主動的死鎖檢測機(jī)制，要求數(shù)據(jù)庫保存鎖的信息鏈表和事務(wù)等待鏈表兩部分信息，通過這兩個部分信息構(gòu)造出一張圖，在每個事務(wù)請求鎖并發(fā)生等待時都會判斷是否存在回路，如果在圖中檢測到回路，就表明有死鎖產(chǎn)生，這時候InnoDB存儲引擎會選擇回滾undo量最小的事務(wù)。將參數(shù)innodb_deadlock_detect=on表示開啟這個邏輯。（缺點(diǎn)是當(dāng)并發(fā)很高時，死鎖檢測會消耗大量CPU資源，導(dǎo)致CPU利用率很高，但是每秒?yún)s執(zhí)行事務(wù)數(shù)很少）

一般大多采用第二種死鎖碰撞檢測。對于這種由熱點(diǎn)行更新導(dǎo)致的性能問題怎么解決呢？

• 關(guān)閉死鎖檢測：暴力解決辦法，前提是你能確保這個業(yè)務(wù)一定不會出現(xiàn)死鎖。但這種操作本身帶有一定的風(fēng)險(xiǎn)，因?yàn)闃I(yè)務(wù)設(shè)計(jì)的時候一般不會把死鎖當(dāng)做一個嚴(yán)重錯誤，畢竟出現(xiàn)死鎖就回滾，然后通過業(yè)務(wù)重試一般就沒問題了，這是業(yè)務(wù)無損的。而關(guān)掉死鎖檢測意味著可能會出現(xiàn)大量的超時，這是業(yè)務(wù)有損的。不推薦。

• 并發(fā)控制：如果并發(fā)很低的情況下，死鎖檢測的成本很低。一般在服務(wù)器業(yè)務(wù)端限制對同一表、同一行或范圍并發(fā)操作。

3. 案例分析

3.1. 案例一

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-02-26 18:04:40 0x7fcfdbcfe700
*** (1) TRANSACTION:
TRANSACTION 12570689, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7405652, OS thread handle 140522483459840, query id 144925248 10.20.30.31 root updating
DELETE FROM t WHERE a='a1' AND b='b1' AND c='c1' AND d='d1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 81 page no 4970 n bits 136 index PRIMARY of table `db_name`.`t` trx id 12570689 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12570690, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 0
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 7403815, OS thread handle 140530722793216, query id 144925250 10.20.30.32 root updating
DELETE FROM t WHERE a= 'a2' AND b='b2' AND c='c1' AND d='d1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 81 page no 4970 n bits 136 index PRIMARY of table `db_name`.`t` trx id 12570690 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 81 page no 17 n bits 384 index idx_cd of table `db_name`.`t` trx id 12570690 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------ 

檢測到死鎖，記錄兩個事務(wù)，事務(wù)1和事務(wù)2，最終事務(wù)1回滾

• 事務(wù)1

事務(wù)ID=12570689，活躍了0s
使用了1個表，持有1個鎖
有2個行鎖
線程ID=7405652，IP=10.20.30.31，用戶=root，更新操作（增刪改都是updating）
當(dāng)前事務(wù)的SQL操作: DELETE FROM t WHERE a='a1' AND b='b1' AND c='c1' AND d='d1'
上面SQL等待的鎖信息：行鎖，加在表db_name.t的PRIMARY（主鍵）索引上，索引模式X locks rec but not gap（記錄排他鎖）

• 事務(wù)2

事務(wù)ID=12570690，活躍了0s
使用了1個表，持有1個鎖
有2個行鎖
線程ID=7403815，IP=10.20.30.32，用戶=root，更新操作（增刪改都是updating）
當(dāng)前事務(wù)的SQL操作: DELETE FROM t WHERE a= 'a2' AND b='b2' AND c='c1' AND d='d1'
已持有的鎖西信息：行鎖，加在表db_name.t的PRIMARY索引上，索引模式X locks rec but not gap（記錄排他鎖）
上面SQL等待的鎖信息：
行鎖，加在表db_name.t的idx_cd索引上，索引模式X locks rec but not gap（記錄排他鎖）

• 表索引結(jié)構(gòu)

PRIMARY KEY (id),
UNIQUE KEY uniq_uk (a,c) USING BTREE,
KEY idx_ab (a,b) USING BTREE,
KEY idx_cd (c,d) USING BTREE

很明顯，時因?yàn)閯h除操作，要把X locks rec but not gap加到idx_cd索引上，兩個操作涉及到idx_cd索引的同一行，所以出現(xiàn)死鎖。索引設(shè)計(jì)不合理，優(yōu)化索引。

3.1. 案例二

TRANSACTION 9012724, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 43 row lock(s), undo log entries 21
MySQL thread id 12643211, OS thread handle 140223917704960, query id 137518665 10.20.28.40 root update
INSERT INTO DAM_DS_RDB_TABLE_FIELD (pid, ds_id, field_name, field_type, is_null, default_value, expand, comment, `position`, create_time, ID)
VALUES (117309, 1001123, 'ID', 'bigint(20)', '0', NULL, 'auto_increment', 'ID', 0, '2020-06-16 02:01:10.451', 9331810),
    (117309, 1001123, 'CONTRACT_NO', 'varchar(64)', '1', NULL, '', '額度協(xié)議號', 2, '2020-06-16 02:01:10.451', 9331812)
RECORD LOCKS space id 146 page no 16 n bits 416 index uniq_uk of table `db_name`.`dam_ds_rdb_table_field` trx id 9012724 lock mode S waiting

TRANSACTION 9012726, ACTIVE 0 sec inserting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 44 row lock(s), undo log entries 24
MySQL thread id 12643272, OS thread handle 140223928846080, query id 137518674 10.20.28.41 root update
INSERT INTO DAM_DS_RDB_TABLE_FIELD (pid, ds_id, field_name, field_type, is_null, default_value, expand, comment, `position`, create_time, ID)
VALUES (117310, 1001020, 'ID', 'bigint(20)', '0', NULL, '', '主鍵', 0, '2020-06-16 02:01:10.472', 9331790),
    (117310, 1001020, 'AUTH_ID', 'varchar(32)', '1' NULL, '', '商戶協(xié)議號', 2, '2020-06-16 02:01:10.472', 9331872)
RECORD LOCKS space id 146 page no 16 n bits 416 index uniq_uk of table `db_name`.`dam_ds_rdb_table_field` trx id 9012726 lock_mode X locks rec but not gap
RECORD LOCKS space id 146 page no 16 n bits 424 index uniq_uk of table `damassetspre_007`.`dam_ds_rdb_table_field` trx id 9012726 lock_mode X locks gap before rec insert intention waiting

事務(wù)1批量插入，id in (9331810, 9331812)，等待 uniq_uk 的S共享鎖。
事務(wù)2批量插入，id in (9331790, 9331872)，已持有 uniq_uk 的記錄排他鎖，等待插入意向范圍鎖X locks gap before rec insert intention。

根據(jù)業(yè)務(wù)代碼，是多線批量插入，id范圍沖突。解決辦法，可接受情況下，改成單線程操作。

參考

https://blog.csdn.net/weixin_41850404/article/details/84653909

https://blog.csdn.net/noaman_wgs/article/details/82529908