探讨MySQL的各類鎖

參考文檔：http://blog.csdn.net/soonfly/article/details/70238902

鎖是計算機協調多個程序或純線程并發通路某一資源的機制。在資料庫中，除了傳統的計算機資源，如CPU、RAM外，資料也是多個使用者共享的資源。如何保證資料并發通路的一緻性、有效性是所有資料庫必須解決的一個問題。本文探讨的是MySQL的鎖機制。

MySQL最常使用的兩種存儲引擎是InnoDB和MyISAM，他們有在很多方面的差別，分别有不同的應用場景，由于本文隻探讨資料庫的鎖，是以目前隻需要了解的是：

1. MyISAM不支援事務安全，InnoDB支援事務安全。（InnoDB實作了SQL标準的四種隔離級别）
2. MyISAM鎖的粒度是表級的，而InnoDB支援行級鎖。

根據鎖的粒度，MySQL大緻可分為以下三種鎖：

1. 表級鎖：對涉及到的表單整個加鎖。開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。
2. 行級鎖：對表單中的某一行加鎖。開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度最高。
3. 頁面鎖：對某一行及其周圍的行加鎖，開銷，加鎖時間和鎖定粒度介于表級和行級之間；會出現死鎖；并發度一般。

MyISAM表級鎖

表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。鎖模式的相容情況如下：

• 對MyISAM的讀操作，不會阻塞其他使用者對同一表請求，但會阻塞對同一表的寫請求；
• 對MyISAM的寫操作，則會阻塞其他使用者對同一表的讀和寫操作；
• MyISAM表的讀操作和寫操作之間，以及寫操作之間是串行的。

如何加表鎖

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要使用者幹預，是以使用者一般不需要直接用LOCK TABLE指令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了友善而已，并非必須如此。

給MyISAM表顯示加鎖，一般是為了一定程度模拟事務操作，實作對某一時間點多個表的一緻性讀取。例如，有一個訂單表orders，其中記錄有訂單的總金額total，同時還有一個訂單明細表order_detail，其中記錄有訂單每一産品的金額小計subtotal，假設我們需要檢查這兩個表的金額合計是否相等，可能就需要執行如下兩條SQL：

1 SELECT SUM(total) FROM orders;
2 SELECT SUM(subtotal) FROM order_detail;      

這時，如果不先給這兩個表加鎖，就可能産生錯誤的結果，因為第一條語句執行過程中，order_detail表可能已經發生了改變。是以，正确的方法應該是：

LOCK tables orders read local,order_detail read local;
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
Unlock tables;      

要特别說明以下兩點内容。

• 上面的例子在LOCK TABLES時加了‘local’選項，其作用就是在滿足MyISAM表并發插入條件的情況下，允許其他使用者在表尾插入記錄。即允許讀寫并發進行，但是寫隻能在表尾進行。
• 在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，并且MySQL不支援鎖更新。也就是說，在執行LOCK TABLES後，隻能通路顯式加鎖的這些表，不能通路未加鎖的表；同時，如果加的是讀鎖，那麼隻能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的 情況下也基本如此，MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因。

一個session使用LOCK TABLE 指令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或通路其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖等待。

當使用LOCK TABLE時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的别名鎖多少次，否則也會出錯！

并發插入（Concurrent Insert）

在一定條件下，MyISAM也支援查詢和插入操作的并發進行，即使用上文提到的read local語句。

MyISAM存儲引擎有一個系統變量concurrent_insert，專門用以控制其并發插入的行為，其值可以分别為0,1或2。

• 當concurrent_insert設定為0時，不允許并發插入。
• 當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被删除的行），MyISAM允許在一個程序讀表的同時，另一個程序從表尾插入記錄。這也是MySQL的預設設定。
• 當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發插入記錄。

可以利用MyISAM存儲引擎的并發插入特性，來解決應用中對同一表查詢和插入的鎖争用。例如，将concurrent_insert系統變量設為2，總是允許并發插入；同時，通過定期在系統空閑時段執行 OPTIMIZE TABLE語句來整理空間碎片，收回因删除記錄而産生的中間空洞。

MyISAM的鎖排程

前面講過，MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。那麼，一個程序請求某個 MyISAM表的讀鎖，同時另一個程序也請求同一表的寫鎖，MySQL如何處理呢？答案是寫程序先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求後 到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫請求一般比讀請求要重要。這也正是MyISAM表不太适合于有大量更新操作和查詢操作應用的原 因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，進而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設定來調節MyISAM 的排程行為。

• 通過指定啟動參數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。
• 通過執行指令

  SET LOW_PRIORITY_UPDATES=1

  ，使該連接配接發出的更新請求優先級降低。
• 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級。

雖然上面3種方法都是要麼更新優先，要麼查詢優先的方法，但還是可以用其來解決查詢相對重要的應用（如使用者登入系統）中，讀鎖等待嚴重的問題。 

另外，MySQL也提供了一種折中的辦法來調節讀寫沖突，即給系統參數

max_write_lock_count

設定一個合适的值，當一個表的讀鎖達到這個值後，MySQL就暫時将寫請求的優先級降低，給讀程序一定獲得鎖的機會。

上面已經讨論了寫優先排程機制帶來的問題和解決辦法。這 裡還要強調一點：一些需要長時間運作的查詢操作，也會使寫程序“餓死”！是以，應用中應盡量避免出現長時間運作的查詢操作，不要總想用一條SELECT語 句來解決問題，因為這種看似巧妙的SQL語句，往往比較複雜，執行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解”，使每 一步查詢都能在較短時間完成，進而減少鎖沖突。如果複雜查詢不可避免，應盡量安排在資料庫空閑時段執行，比如一些定期統計可以安排在夜間執行。

InnoDB行級鎖

如本文開頭所說，InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，事務的引入也帶來了一些之前讨論過的髒讀等問題。首先，我們先看一下InnoDB行級鎖的模式。

InnoDB實作了以下兩種類型的鎖：

• 共享鎖（S）：又稱讀鎖。允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖。若事務T對資料對象A加上S鎖，則事務T可以讀A但不能修改A，其他事務隻能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。
• 排他鎖（X）：又稱寫鎖。允許擷取排他鎖的事務更新資料，阻止其他事務取得相同的資料集共享讀鎖和排他寫鎖。若事務T對資料對象A加上X鎖，事務T可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到T釋放A上的鎖。

• 注意：對于共享鎖大家可能很好了解，就是多個事務隻能讀資料不能改資料。 

  對于排他鎖大家的了解可能就有些差别，以為排他鎖鎖住一行資料後，其他事務就不能讀取和修改該行資料，其實不是這樣的。排他鎖指的是一個事務在一行資料加上排他鎖後，其他事務不能再在其上加其他的鎖。mysql InnoDB引擎預設的修改資料語句：update,delete,insert都會自動給涉及到的資料加上排他鎖，select語句預設不會加任何鎖類型，如果加排他鎖可以使用select …for update語句，加共享鎖可以使用select … lock in share mode語句。是以加過排他鎖的資料行在其他事務中是不能修改資料的，也不能通過for update和lock in share mode鎖的方式查詢資料，但可以直接通過select …from…查詢資料，因為普通查詢沒有任何鎖機制。

另外，為了允許行鎖和表鎖共存，實作多粒度鎖機制，InnoDB還有兩種内部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

• 意向共享鎖（IS）：事務打算給資料行共享鎖，事務在給一個資料行加共享鎖前必須先取得該表的IS鎖。
• 意向排他鎖（IX）：事務打算給資料行加排他鎖，事務在給一個資料行加排他鎖前必須先取得該表的IX鎖。

InnoDB行鎖模式相容性清單

注意：這邊的X和S指的是表鎖而不是行鎖，關于為什麼要存在意向鎖，請看：https://www.zhihu.com/question/51513268

如果一個事務請求的鎖模式與目前的鎖相容，InnoDB就請求的鎖授予該事務；反之，如果兩者不相容，該事務就要等待鎖釋放。 

意向鎖是InnoDB自動加的，不需使用者幹預。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖（X)；對于普通SELECT語句，InnoDB不會加任何鎖。 

事務可以通過以下語句顯式給記錄集加共享鎖或排他鎖：

• 共享鎖（S）：

  SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

  。
• 排他鎖（X）：

  SELECT * FROM table_name WHERE ... FOR UPDATE

用

SELECT ... IN SHARE MODE

獲得共享鎖，主要用在需要資料依存關系時來确認某行記錄是否存在，并確定沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果目前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對于鎖定行記錄後需要進行更新操作的應用，應該使用SELECT… FOR UPDATE方式獲得排他鎖。

InnoDB行鎖實作方式

InnoDB行鎖是通過給索引上的索引項加鎖來實作的，這一點MySQL與Oracle不同，後者是通過在資料塊中對相應資料行加鎖來實作的。InnoDB這種行鎖實作特點意味着：隻有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB将使用表鎖！ 

在實際應用中，要特别注意InnoDB行鎖的這一特性，不然的話，可能導緻大量的鎖沖突，進而影響并發性能。

• 在不通過索引條件查詢的時候，InnoDB确實使用的是表鎖，而不是行鎖。
• 由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，是以雖然是通路不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。
• 當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對資料加鎖。 
• 即便在條件中使用了索引字段，但是否使用索引來檢索資料是由MySQL通過判斷不同執行計劃的代價來決 定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB将使用表鎖，而不是行鎖。是以，在分析鎖沖突 時，别忘了檢查SQL的執行計劃，以确認是否真正使用了索引。 

間隙鎖（Next-Key鎖）

當我們用範圍條件而不是相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的 索引項加鎖；對于鍵值在條件範圍内但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖 （Next-Key鎖）。

舉例來說，假如emp表中隻有101條記錄，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update      

是一個範圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖。

InnoDB使用間隙鎖的目的，是為了防止幻讀，以滿足相關隔離級别的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大于100的任何記錄，那麼本事務如果再次執行上述語句，就會發生幻讀。

很顯然，在使用範圍條件檢索并鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍内鍵值的并發插入，這往往會造成嚴重的鎖等待。是以，在實際應用開發中，尤其是并發插入比較多的應用，我們要盡量優化業務邏輯，盡量使用相等條件來通路更新資料，避免使用範圍條件。

還要特别說明的是，InnoDB除了通過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

小結

本文重點介紹了MySQL中MyISAM表級鎖和InnoDB行級鎖的實作特點，并讨論了兩種存儲引擎經常遇到的鎖問題和解決辦法。

對于MyISAM的表鎖，主要讨論了以下幾點： 

（1）共享讀鎖（S）之間是相容的，但共享讀鎖（S）與排他寫鎖（X）之間，以及排他寫鎖（X）之間是互斥的，也就是說讀和寫是串行的。 

（2）在一定條件下，MyISAM允許查詢和插入并發執行，我們可以利用這一點來解決應用中對同一表查詢和插入的鎖争用問題。 

（3）MyISAM預設的鎖排程機制是寫優先，這并不一定适合所有應用，使用者可以通過設定LOW_PRIORITY_UPDATES參數，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調節讀寫鎖的争用。 

（4）由于表鎖的鎖定粒度大，讀寫之間又是串行的，是以，如果更新操作較多，MyISAM表可能會出現嚴重的鎖等待，可以考慮采用InnoDB表來減少鎖沖突。

對于InnoDB表，本文主要讨論了以下幾項内容： 

（1）InnoDB的行鎖是基于索引實作的，如果不通過索引通路資料，InnoDB會使用表鎖。 

（2）介紹了InnoDB間隙鎖（Next-key)機制，以及InnoDB使用間隙鎖的原因。 

在不同的隔離級别下，InnoDB的鎖機制和一緻性讀政策不同。

在了解InnoDB鎖特性後，使用者可以通過設計和SQL調整等措施減少鎖沖突和死鎖，包括：

• 盡量使用較低的隔離級别； 精心設計索引，并盡量使用索引通路資料，使加鎖更精确，進而減少鎖沖突的機會；
• 選擇合理的事務大小，小事務發生鎖沖突的幾率也更小；
• 給記錄集顯式加鎖時，最好一次性請求足夠級别的鎖。比如要修改資料的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易産生死鎖；
• 不同的程式通路一組表時，應盡量約定以相同的順序通路各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；
• 盡量用相等條件通路資料，這樣可以避免間隙鎖對并發插入的影響； 不要申請超過實際需要的鎖級别；除非必須，查詢時不要顯示加鎖；
• 對于一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能。