一序
本文分為兩個部分，第一部分主要基于何登成大神的文章。何博士作為阿裡資料庫核心團隊大神。文章更是深入淺出。膜拜一下：原文位址如下 http://hedengcheng.com/?p=771
第二部分介紹常見的實踐注意事項。
二 背景
MVCC：Snapshot Read vs Current Read
MySQL InnoDB存儲引擎，實作的是基于多版本的并發控制協定——MVCC (Multi-Version Concurrency Control) 在MVCC并發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與目前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是曆史版本)，不用加鎖。目前讀，讀取的是記錄的最新版本，并且，目前讀傳回的記錄，都會加上鎖，保證其他事務不會再并發修改這條記錄。
在一個支援MVCC并發控制的系統中，哪些讀操作是快照讀？哪些操作又是目前讀呢？以MySQL InnoDB為例：
- 快照讀：簡單的select操作，屬于快照讀，不加鎖。(當然，也有例外，下面會分析)
- select * from table where ?;
- 目前讀：特殊的讀操作，插入/更新/删除操作，屬于目前讀，需要加鎖。
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert into table values (…);
- update table set ? where ?;
- delete from table where ?;
其他相關知識點：
聚集索引（主鍵索引） 官網介紹、
2PL：Two-Phase Locking （鎖操作分為兩個階段：加鎖階段與解鎖階段，并且保證加鎖階段與解鎖階段不相交）
隔離級别（官網介紹：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html）
Read Uncommited，Read Committed，Repeatable Read，Serializable，不做重複介紹。
三 加鎖分析case
SQL1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;
需要分析不同的前提，下面是常見的：
- 組合一：id列是主鍵，RC隔離級别
- 組合二：id列是二級唯一索引，RC隔離級别
- 組合三：id列是二級非唯一索引，RC隔離級别
- 組合四：id列上沒有索引，RC隔離級别
- 組合五：id列是主鍵，RR隔離級别
- 組合六：id列是二級唯一索引，RR隔離級别
- 組合七：id列是二級非唯一索引，RR隔離級别
- 組合八：id列上沒有索引，RR隔離級别
- 組合九：Serializable隔離級别
在前面八種組合下，也就是RC，RR隔離級别下，SQL1：select操作均不加鎖，采用的是快照讀，是以在下面的讨論中就忽略了，主要讨論SQL2：delete操作的加鎖。
3.1組合一：id主鍵+RC
這個組合，是最簡單，最容易分析的組合。id是主鍵，Read Committed隔離級别，給定SQL：delete from t1 where id = 10; 隻需要将主鍵上，id = 10的記錄加上X鎖即可。
結論：id是主鍵時，此SQL隻需要在id=10這條記錄上加X鎖即可。
3.2 組合二：id唯一索引+RC
此組合中，id是unique索引，而主鍵是name列。此時，加鎖的情況由于組合一有所不同。由于id是unique索引，是以delete語句會選擇走id列的索引進行where條件的過濾，在找到id=10的記錄後，首先會将unique索引上的id=10索引記錄加上X鎖，同時，會根據讀取到的name列，回主鍵索引(聚簇索引)，然後将聚簇索引上的name = ‘d’ 對應的主鍵索引項加X鎖。為什麼聚簇索引上的記錄也要加鎖？試想一下，如果并發的一個SQL，是通過主鍵索引來更新：update t1 set id = 100 where name = ‘d’; 此時，如果delete語句沒有将主鍵索引上的記錄加鎖，那麼并發的update就會感覺不到delete語句的存在，違背了同一記錄上的更新/删除需要串行執行的限制。
結論：若id列是unique列，其上有unique索引。那麼SQL需要加兩個X鎖，一個對應于id unique索引上的id = 10的記錄，另一把鎖對應于聚簇索引上的[name=’d’,id=10]的記錄。
3.3 組合三：id非唯一索引+RC
根據此圖，可以看到，首先，id列索引上，滿足id = 10查詢條件的記錄，均已加鎖。同時，這些記錄對應的主鍵索引上的記錄也都加上了鎖。與組合二唯一的差別在于，組合二最多隻有一個滿足等值查詢的記錄，而組合三會将所有滿足查詢條件的記錄都加鎖。
結論：若id列上有非唯一索引，那麼對應的所有滿足SQL查詢條件的記錄，都會被加鎖。同時，這些記錄在主鍵索引上的記錄，也會被加鎖。
3.4組合四：id無索引+RC
由于id列上沒有索引，是以隻能走聚簇索引，進行全部掃描。從圖中可以看到，滿足删除條件的記錄有兩條，但是，聚簇索引上所有的記錄，都被加上了X鎖。無論記錄是否滿足條件，全部被加上X鎖。既不是加表鎖，也不是在滿足條件的記錄上加行鎖。
注：在實際的實作中，MySQL有一些改進，在MySQL Server過濾條件，發現不滿足後，會調用unlock_row方法，把不滿足條件的記錄放鎖 (違背了2PL的限制)。這樣做，保證了最後隻會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。
結論：若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，由于過濾是由MySQL Server層面進行的。是以每條記錄，無論是否滿足條件，都會被加上X鎖。但是，為了效率考量，MySQL做了優化，對于不滿足條件的記錄，會在判斷後放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。同時，優化也違背了2PL的限制。
3.5 組合五：id主鍵+RR
上面的四個組合，都是在Read Committed隔離級别下的加鎖行為，接下來的四個組合，是在Repeatable Read隔離級别下的加鎖行為。
組合五，id列是主鍵列，Repeatable Read隔離級别，針對delete from t1 where id = 10; 這條SQL，加鎖與組合一：（id是主鍵，Read Committed隔離級别） 一緻。
結論：id是主鍵時，此SQL隻需要在id=10這條記錄上加X鎖即可。
3.6 組合六：id唯一索引+RR
與組合二：[id唯一索引，Read Committed]一緻。兩個X鎖，id唯一索引滿足條件的記錄上一個，對應的聚簇索引上的記錄一個。
3.7 組合七：id非唯一索引+RR
相對于組合三：[id列上非唯一鎖，Read Committed]看似相同，其實卻有很大的差別。最大的差別在于，這幅圖中多了一個GAP鎖。
其實這個多出來的GAP鎖，就是RR隔離級别，相對于RC隔離級别，不會出現幻讀的關鍵。确實，GAP鎖鎖住的位置，也不是記錄本身，而是兩條記錄之間的GAP。所謂幻讀，就是同一個事務，連續做兩次目前讀 (例如：select * from t1 where id = 10 for update;)，那麼這兩次目前讀傳回的是完全相同的記錄 (記錄數量一緻，記錄本身也一緻)，第二次的目前讀，不會比第一次傳回更多的記錄 (幻象)。如何保證兩次目前讀傳回一緻的記錄，那就需要在第一次目前讀與第二次目前讀之間，其他的事務不會插入新的滿足條件的記錄并送出。為了實作這個功能，GAP鎖應運而生。
如圖中所示，有哪些位置可以插入新的滿足條件的項 (id = 10)，考慮到B+樹索引的有序性，滿足條件的項一定是連續存放的。記錄[6,c]之前，不會插入id=10的記錄；[6,c]與[10,b]間可以插入[10, aa]；[10,b]與[10,d]間，可以插入新的[10,bb],[10,c]等；[10,d]與[11,f]間可以插入滿足條件的[10,e],[10,z]等；而[11,f]之後也不會插入滿足條件的記錄。是以，為了保證[6,c]與[10,b]間，[10,b]與[10,d]間，[10,d]與[11,f]不會插入新的滿足條件的記錄，MySQL選擇了用GAP鎖，将這三個GAP給鎖起來。
Insert操作，如insert [10,aa]，首先會定位到[6,c]與[10,b]間，然後在插入前，會檢查這個GAP是否已經被鎖上，如果被鎖上，則Insert不能插入記錄。是以，通過第一遍的目前讀，不僅将滿足條件的記錄鎖上 (X鎖)，與組合三類似。同時還是增加3把GAP鎖，将可能插入滿足條件記錄的3個GAP給鎖上，保證後續的Insert不能插入新的id=10的記錄，也就杜絕了同一事務的第二次目前讀，出現幻象的情況。
既然防止幻讀，需要靠GAP鎖的保護，為什麼組合五、組合六，也是RR隔離級别，卻不需要加GAP鎖呢？GAP鎖的目的，是為了防止同一事務的兩次目前讀，出現幻讀的情況。而組合五，id是主鍵；組合六，id是unique鍵，都能夠保證唯一性。一個等值查詢，最多隻能傳回一條記錄，而且新的相同取值的記錄，一定不會在新插入進來，是以也就避免了GAP鎖的使用。
結論：Repeatable Read隔離級别下，id列上有一個非唯一索引，對應SQL：delete from t1 where id = 10; 首先，通過id索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然後加主鍵聚簇索引上的記錄X鎖，然後傳回；然後讀取下一條，重複進行。直至進行到第一條不滿足條件的記錄[11,f]，此時，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最後傳回結束。
3.8 組合八：id無索引+RR
結論：在Repeatable Read隔離級别下，如果進行全表掃描的目前讀，那麼會鎖上表中的所有記錄，同時會鎖上聚簇索引内的所有GAP，杜絕所有的并發 更新/删除/插入 操作。當然，也可以通過觸發semi-consistent read，來緩解加鎖開銷與并發影響，但是semi-consistent read本身也會帶來其他問題，不建議使用。
組合九：Serializable
Serializable隔離級别，影響的是SQL1：select * from t1 where id = 10; 這條SQL，在RC，RR隔離級别下，都是快照讀，不加鎖。但是在Serializable隔離級别，SQL1會加讀鎖，也就是說快照讀不複存在，MVCC并發控制降級為Lock-Based CC。
結論：在MySQL/InnoDB中，所謂的讀不加鎖，并不适用于所有的情況，而是隔離級别相關的。Serializable隔離級别，讀不加鎖就不再成立，所有的讀操作，都是目前讀。
普通的并發插入導緻的死鎖
create table t1 (a int primary key); 開啟三個會話執行： insert into t1(a) values (2);
| session 1 | session 2 | session 3 |
|---|---|---|
| BEGIN; INSERT.. | ||
| INSERT (block),為session1建立X鎖，并等待S鎖 | ||
| INSERT (block， 同上等待S鎖) | ||
| ROLLBACK，釋放鎖 | ||
| 獲得S鎖 | 獲得S鎖 | |
| 申請插入意向X鎖，等待session3 | ||
| 申請插入意向X鎖，等待session2 |
上述描述了互相等待的場景，因為插入意向X鎖和S鎖是不相容的。這也是一種典型的鎖更新導緻的死鎖。如果session1執行COMMIT的話，則另外兩個線程都會因為duplicate key失敗。
這裡需要解釋下為何要申請插入意向鎖，因為ROLLBACK時原記錄復原時是被标記删除的。而我們嘗試插入的記錄和這個标記删除的記錄是相鄰的(鍵值相同)，根據插入意向鎖的規則，插入位置的下一條記錄上如果存在與插入意向X鎖沖突的鎖時，則需要擷取插入意向X鎖。
又一個并發插入的死鎖現象
兩個會話參與。在RR隔離級别下
例表如下：
create table t1 (a int primary key ,b int);
insert into t1 values (1,2),(2,3),(3,4),(11,22);
| session 1 | session 2 |
|---|---|
| begin;select * from t1 where a = 5 for update;(擷取記錄(11,22)上的GAP X鎖) | |
| begin;select * from t1 where a = 5 for update; (同上,GAP鎖之間不沖突 | |
| insert into t1 values (4,5); (block，等待session1) | |
| insert into t1 values (4,5);（需要等待session2，死鎖） |
引起這個死鎖的原因是非插入意向的GAP X鎖和插入意向X鎖之間是沖突的。
四 資料庫實踐
4.1 設計階段
表引擎選擇使用innodb不适用myisam:
由于myisam引擎隻支援table lock，在使用myisam引擎表過程中，當資料庫中出現執行時間較長的查詢後就會堵塞該表上的更新動作，是以經常會碰到線程會話處于表級鎖等待（Waiting for table level lock）的情況.
表索引設計：避免索引死鎖或者index merge。
4.2 開發階段
因為已經使用了阿裡雲的RDS資料庫，通常是dba監控慢SQL或者異常。
事務處理時間過長，導緻并發出現鎖等待。 并發事務處理在資料庫中經常看到的應用場景，在這種場景下，需要避免大事務，長事務，複雜事務導緻事務在資料庫中的運作時間加長，事務時間變長則導緻事務中鎖的持有時間變長，影響整體的資料庫吞吐量。
4.3 維護階段
DDL操作被大查詢block。 當應用上線進入維護階段，則開始會有較多的資料庫變更操作，比如：添加字段，添加索引等操作，這一類操作導緻的鎖故障也是非常頻繁的.
盡量設計表保留拓展字段，保留json類型資料。這種字段沒法做索引，也不要太長，隻做必要的業務展示或者中轉。
Metadata lock wait 的含義：為了在并發環境下維護表中繼資料的資料一緻性，在表上有活動事務（顯式或隐式）的時候，不可以對中繼資料進行寫入操作。是以 MySQL 引入了 metadata lock ，來保護表的中繼資料資訊。是以在對表進行上述操作時，如果表上有活動事務（未送出或復原），請求寫入的會話會等待在 Metadata lock wait。
導緻 Metadata lock wait 等待的常見因素包括：活動事務，目前有對表的長時間查詢，顯示或者隐式開啟事務後未送出或復原，比如查詢完成後未送出或者復原，表上有失敗的查詢事務等。
總結：
設計開發階段:
- 表設計要避免使用myisam存儲引擎，改用innodb引擎；
- 為SQL建立合适的索引，避免多個單列索引執行出錯；
- 避免大事務，長事務，複雜事務導緻事務在資料庫中的運作時間加長。
管理運維階段：
- 在業務低峰期執行上述操作，比如建立删除索引；
- 在結構變更前，觀察資料庫中是否存在長時間運作的SQL，未送出的事務；
- 結構變更期間，監控資料庫的線程狀态是否存在lock wait。
參考：
http://mysql.taobao.org/monthly/2016/03/10/
http://hedengcheng.com/?p=771