前言
換了工作之後，接近半年沒有發部落格了（一直加班），emmmm.....今天好不容易有時間，記錄下工作中遇到的一些問題，接下來應該重拾知識點了。因為新公司工作中MySQL庫經常出現查詢慢，鎖等待，節點掛掉........等一系列問題。導致每個程式設計師頭都很大，一味抱怨“為什麼我就查一條資料這麼卡”，"我TM加了索引的啊，怎麼還怎麼慢"...........我想默默說的是，大部分MySQL出現鎖等待，查詢奇慢的情況基本都是因為SQL寫的不好（有坑），或者資料表設計的不完善。對，不用想！這些所有的坑很大一部分都是自己造成的。那麼是什麼原因造成的，大部分只是抱怨，而不去關注MySQL的一些細節問題，比如：MySQL行鎖的細節，什麼情況下會使用表鎖等。所以今天先討論記錄下InnoDB特有的行鎖的一些細節，加強認識。
InnoDB不同於MyISAM最大的兩個特點就是：一是支援事務，二是支援行鎖；毋庸置疑，因為這兩個特性大部分都採用InnoDB引擎，其中的支援行鎖就是InnoDB適合多併發優勢所在，但是行鎖的一些細節沒有深入理解過的話，可能會造成一定的誤解，造成“看似命中索引，走行鎖，結果卻是表鎖，最終導致鎖等待情況”。
一、InnoDB行鎖的實現方式
通過給索引上的索引項加鎖來實現的，也就意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。這一點在實際應用中特別需要注意，不然的話可能導致大量的鎖衝突，從而影響引發併發效能。
實驗一：對沒有索引的加鎖，導致表鎖
1）準備工作：建tab_no_index表，表中無任何索引，並插入資料
2）Session_1: 我們給id=1的行加上排它鎖（for update），由於id沒有索引，實際上是表級鎖；
3）Session_2：我們給id=2的行加上排它鎖（for update），由於id沒有索引，所以去申請表級鎖，但是卻出現了鎖等待！原因就是在沒有索引的情況下，InnoDB只能使用表鎖。
備註：MySQL中的for update 僅適用於InnoDB（因為是隻有此引擎才有行級鎖），並且必須開啟事務，在begin與commit之間才生效。for update是在資料庫中上鎖用的，可以為資料庫中的行上一個排它鎖。當一個事務的操作未完成時候，其他事務可以對這行讀取但是不能寫入或更新，只能等該事務Rollback, Commit, Lost connection…
實驗二：對有索引的鍵值加鎖，會對所有涉及到的資料行加鎖
1）準備工作：對id建索引如下
2）Session_1：此時id是有索引的，我們對id=1 and name=1的一行加排它鎖；
3）Session_2：訪問不同於Session_1的id=1, name=5行，但是索引鍵值是一樣的，照樣等待鎖，鎖衝突了。
實驗三：多個索引時，不同的事務可以使用不同的索引鎖定不同的行，不論什麼索引，InnoDB都會使用行鎖對資料加鎖（對有索引的行資料）。
1）準備工作：對tab_no_index追加name索引：alter table tab_no_index add index name(name);
2）Session_1：開啟事務對id=1的行加排它鎖，即對name=1與name=5兩個資料加鎖。
3）Session_2：開啟事務對name=2行加鎖，因為該資料沒有被加鎖，索引可以獲得鎖
4）Session_3：再對name=5的資料進行加鎖，由於該資料記錄已被Session_1鎖定，所以等待獲得鎖。
注意事項：即便使用了索引，但還是要看MySQL具體對SQL的執行計劃，不一定能使用到
如我們對實驗三對name='2'進行加鎖，誤以為name是int型別，本來name是有索引的，但是最後結果導致表鎖：
具體請看MySQL的索引情況。具體可以參考之前我的一篇博文MySQL優化（1）--------常用的優化步驟、MySQL優化(2)--------常用優化
二、間隙鎖（Next-Key鎖）
當用範圍條件而不是相等條件檢索資料，並請求共享或者排它鎖的時候，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於不在範圍內的但並不存在的記錄，叫做“間隙(GAP)”，InnoDB也會對這個間隙加鎖，這就是所謂的間隙鎖。
如：select * from where id>100 for update 對id大於100的資料對加鎖，但是此時資料中id只有1,2….100,101，不僅對存在的101的記錄加鎖，還會對大於101不存在的資料的間隙加鎖。
此外，對使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖，如下：
Session_1：對不存在的id=6的記錄加鎖
Session_2：插入id=6的記錄，也會出現鎖等待
三、什麼時候使用表鎖？
對於InnoDB表，在絕大部分情況下都應該使用行鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由，但在個別情況下也使用表級鎖；
1）事務需要更新大部分或全部資料，表又比較大，如果使用預設的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間等待和鎖衝突；
2）事務涉及多個表，比較複雜，很可能引起死鎖，造成大量事務回滾。
使用表鎖需要注意幾點：
1）使用LOCK TABLES雖然可以給InnoDB加表級鎖，表級鎖不是InnoDB儲存引擎層管理的，而是由其上一層MySQL Server負責的
2）在用LOCK TABLES對InnoDB表加鎖時需要注意，要將Autocommit設定為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK_TABLES隱含提交事務；COMMIT或ROLLBACK並不能釋放用LOCK TABLES加表級鎖。
SET AUTOCOMMIT=0;
LOCK TABLES table1 WRITE, table2 READ,...；
[do something....]
COMMIT;
UNLOCK TABLES;
總結：
-
從設計之初，就應該建立良好的索引機制，避免對關鍵欄位搜尋時造成表鎖；
-
避免長時間事務未提交等情況，導致鎖衝突，死鎖等情況；
-
不要老是抱怨資料庫有問題，應該從自身寫的SQL分析出發，學會分析（資料庫不行大部分是因為SQL寫的有問題，沒錯，是自身問題）；
-
不要總是覺得這是DBA該做的事，開發者應該學會基本的SQL常識（如MySQL的最左索引，回表，索引覆蓋等知識），學會基本的優化步驟。
主要參考資料：《深入淺出MySQL》（有需要PDF電子書的夥伴可以評論或者私信我）