針對多執行緒的併發訪問，任何一個資料庫都有其鎖定機制，它的優劣直接關係著資料的一致完整性與資料庫系統的高併發處理效能。鎖定機制也因此成了各種資料庫的核心技術之一。不同資料庫儲存引擎的鎖定機制是不同的，本文將從MySQL最常見的儲存引擎MyISAM與InnoDB的鎖定機制說起。
一、MyISAM的鎖機制——表級鎖定
MySQL表級鎖定的常見型別主要分為兩種，一種是讀鎖，一種是寫鎖。
誰持有讀鎖？誰持有寫鎖？誰在等待讀鎖資源？誰在等待寫鎖資源？資料庫系統都是要記錄的。MySQL中，主要通過如下4個佇列來儲存相關資訊：
讀鎖持有佇列：Current read-lock queue(lock->read)——存放所有正在鎖定的讀鎖資訊
寫鎖持有佇列：Current write-lock queue(lock->write)——存放所有正在鎖定的寫鎖資訊
讀鎖等待佇列：Pending read-lock queue(lock->read_wait)——存放所有等待對資源加讀鎖的執行緒資訊
寫鎖等待佇列：Pending write-lock queue(lock->write_wait)——存放所有等待對資源加寫鎖的執行緒資訊
為保證資料一致完整性，多執行緒可以為同一份資源加多個讀鎖，而同一份資源只能加一個寫鎖，讀鎖與寫鎖也不能同時加在一份資源上。
1、讀鎖定
客戶端請求獲取讀鎖定資源時，如果滿足如下兩個要求，則請求通過，進入讀鎖持有佇列；否則，請求失敗，進入讀鎖等待佇列。
（1）請求鎖定的資源當前沒有寫鎖定；
（2）寫鎖等待佇列中沒有優先順序更高的寫鎖定在等待。
2、寫鎖定
客戶端請求獲取寫鎖定的時候：
（1）先通過寫鎖持有佇列檢查這份資源是否已經被加上寫鎖定，如果有，自然暫停自身執行緒進入寫鎖等待佇列等待，如果沒有，進行第（2）步
（2）檢查寫鎖等待佇列中是否有執行緒同樣在等待獲取這份資源的寫鎖定，如果有，則進入寫鎖等待佇列等待，如果沒有，進行第（3）步
（3）通過讀鎖持有佇列檢查這份資源是否已經被加上讀鎖定，如果有，則進行寫鎖等待佇列等待，如果沒有，可以獲取寫鎖定，進入寫鎖持有佇列中
請注意：對於MySQL使用者，展現出來的鎖定型別只有讀鎖定與寫鎖定兩種，但實際上，MySQL內部實現中卻有11種列舉出來的鎖定型別，因為表面與實現的差異，上述請求過程會有特例，在此不再贅述，如想深入瞭解，可參看簡朝陽《MySQL效能調優與架構設計》。
那我們說，MyISAM在對錶的操作上只能是序列處理，不能並行操作嗎？並不是，MyISAM有一個很重要的機制就是併發插入（Concurrent Insert）特性，我們在下面第三部分MyISAM表級鎖定優化建議再詳細介紹。
二、InnoDB的鎖機制——行級鎖定
不光InnoDB儲存引擎，MySQL的分散式儲存引擎NDB Cluster都使用行級鎖定。InnoDB的行級鎖定同樣分為兩種，一種是共享鎖，一種是排它鎖。
1、當一個事務需要給某份資源加鎖的時候，主要情況有如下
（1）如果遇到一個共享鎖正鎖定著資源，那麼事務只能再加上一個共享鎖，而不能加排它鎖。
（2）如果遇到一個排他鎖正鎖定著資源，那麼事務只能等待該鎖定釋放資源後他才能獲得資源並新增自己的鎖定。
2、InnoDB鎖機制的實現與弊端
InnoDB鎖機制是基於索引實現的，通過在指向資料記錄的第一個索引鍵之前與最後一個索引鍵之後的空域空間（間隙或著說是範圍）標記鎖定資訊實現，被稱為間隙鎖。
間隙鎖的弊端：會在執行範圍查詢時，對範圍內所有鍵值加鎖，即使鍵值不存在，這會造成在加鎖後無法插入鎖定鍵值範圍內的任何資料，影響效能。比如：
SELECT * FROM user WHERE user_id BETWEEM 1 AND 100
執行這個查詢時，會對1-100範圍內所有索引鍵值（1-100）加間隙鎖，即使並不存在user_id為10的使用者資訊，所以在加鎖後，要想插入一條user_id為10的使用者資訊是不可行的，這對於行級鎖來說並不符合常理。InnoDB給出的解釋是：為了防止幻讀的出現。
當沒有索引時或無法利用索引時，InnoDB會棄用行級鎖，改用表級鎖，併發處理效能降低。
另外，因為InnoDB的行級鎖與事務處理特性，一定會產生死鎖現象，對於如何降低死鎖產生概率，我在第四部分InnoDB行級鎖定優化建議中詳述。
三、MyISAM表級鎖定優化建議
因為表級鎖的鎖定顆粒較大，其實現難度複雜性行都降低了，成本自然降低，但是付出了高併發處理效能較低的代價，所以表級鎖的優化就從如何提高併發處理效能說起。
1、縮短鎖定時間
（1）降低查詢複雜度，將複雜的查詢劃分成幾個簡單的查詢分步進行。
（2）建立合適的索引加快查詢效率。
（3）優化表結構，只存放必要的資訊，且控制欄位型別與欄位長度（等長最優）。
2、利用MyISAM併發插入特性（Concurrent Insert）,通過設定concurrent_insert引數實現
（1）concurrent_insert=2，無論MyISAM表資料檔案的中間部分是否有因為刪除資料留下的空閒空間，都允許在資料檔案尾部進行併發插入。
（2）concurrent_insert=1，當MyISAM表資料檔案中間不存在空閒空間時，才允許在資料檔案尾部進行併發插入。
（3）concurrent_insert=0，無論MyISAM表資料檔案的中間部分是否有因為刪除資料留下的空閒空間，都不允許在資料檔案尾部進行併發插入。
如果資料被刪除的可能性比較小，而且對暫時性浪費並不在乎的話，可以嘗試把concurrent_insert設定為2；但當刪除量不是很小，查詢時需要讀取更多的空域空間時，推薦設定為1。
3、合理利用讀寫優先順序
預設情況下，寫優先順序要高於讀優先順序。
（1）當資料庫系統以讀為主，要優先保證查詢效能時，可通過low_priority_updates=1設定讀優先順序高於寫優先順序。
（2）當資料庫系統需要保證寫入效能，則不用設定low_priority_updates引數。
四、InnoDB行級鎖定優化建議
InnoDB的行級鎖最大的優勢就是增強了高併發的處理能力，缺點就是複雜性較高、易死鎖，且基於索引實現有一定弊端。我們要做的就是揚長避短，合理利用InnoDB行級鎖定，為此我們就應該做的：
1、儘可能讓所有的資料檢索都通過索引實現，因為InnoDB行級鎖是基於索引實現的，沒有索引或無法使用索引系統會改為使用表級鎖。
2、合理設計索引，以縮小加鎖範圍，避免“間隙鎖”造成不該鎖定的鍵值被鎖定。
3、儘量控制事務的大小，因為行級鎖的複雜性會加大資源量以及鎖定時間。
4、使用較低階別的事務隔離，以減少因實現事務隔離而付出的成本。
5、避免死鎖，可以通過如下方式實現：
（1）類似的業務模組中，儘可能按照相同的訪問順序來訪問，防止產生死鎖。
（2）同一個事務中，儘量做到一次性鎖定需要的所有資源。
（3）對於易產生死鎖的業務部分，增大處理顆粒度，升級為表級鎖以降低死鎖產生的概率。
更多MySQL的鎖相關知識，參閱：MySQL鎖詳解