MySQL常用引擎的鎖機制

標籤：缺點   常見   自動   get   升級   語句   ...   table   而且   

一、引言                                                                                                                         

MySQL引擎預設的鎖層級：

MyISAM和MEMORY採用表級鎖(table-level locking)。 

BDB採用頁面鎖(page-level locking)或表級鎖，預設為頁面鎖。 

InnoDB支援行級鎖(row-level locking)和表級鎖，預設為行級鎖。

 

二、Innodb引擎中的行鎖與表鎖                                                                                    

在Innodb引擎中既支援行鎖也支援表鎖，那麼什麼時候會鎖住整張表，什麼時候或只鎖住一行呢？ 

InnoDB行鎖是通過給索引上的索引項目加鎖來實現的，這一點MySQL與Oracle不同，Oracle者是通過在資料區塊中對相應資料行加鎖來實現的。

InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響並發效能。 

行級鎖都是基於索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。

行級鎖的缺點是：如果並發請求大量的鎖資源，所以速度慢，記憶體消耗大。

 

1、行級鎖與死結

MyISAM中是不會產生死結的，因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼全部等待。

在InnoDB中，鎖是逐步獲得的，就造成了死結的可能。 

在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。

索引分為主鍵索引和非主鍵索引兩種：

如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；

如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。 

在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的索引值，即所謂的next-key locking。

當兩個事務同時執行，一個鎖住了主鍵索引，在等待其他相關索引。另一個鎖定了非主鍵索引，在等待主鍵索引。這樣就會發生死結。 

發生死結後，InnoDB一般都可以檢測到，並使一個事務釋放鎖回退，另一個擷取鎖完成事務。

有多種方法可以避免死結，我們來介紹常見的三種 

• 如果不同程式會並發存取多個表，盡量約定以相同的順序訪問表，可以大大降低死結機會。 
• 在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死結產生機率； 
• 對於非常容易產生死結的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死結產生的機率；

 

2、表鎖

表鎖分為表共用讀鎖（共用鎖定）與表獨佔寫鎖（獨佔鎖定）

• 共用鎖定(Share Lock)又稱讀鎖，是讀取操作建立的鎖。其他使用者可以並發讀取資料，但任何事務都不能對資料進行修改（擷取資料上的獨佔鎖定），直到已釋放所有共用鎖定。 

如果事務T對資料A加上共用鎖定後，則其他事務只能對A再加共用鎖定，不能加獨佔鎖定。獲准共用鎖定的事務只能讀資料，不能修改資料。 

使用：SELECT ... LOCK IN SHARE MODE; 

在查詢語句後面增加LOCK IN SHARE MODE， Mysql會對查詢結果中的每行都加共用鎖定，當沒有其他線程對查詢結果集中的任何一行使用獨佔鎖定時，可以成功申請共用鎖定，否則會被阻塞。其他線程也可以讀取使用了共用鎖定的表，而且這些線程讀取的是同一個版本的資料。

 

• 獨佔鎖定（exclusive Lock）又稱寫鎖，如果事務T對資料A加上獨佔鎖定後，則其他事務不能再對A加任任何類型的鎖。獲准獨佔鎖定的事務既能讀資料，又能修改資料。 

使用：SELECT ... FOR UPDATE; 

在查詢語句後面增加FOR UPDATE， Mysql會對查詢結果中的每行都加獨佔鎖定，當沒有其他線程對查詢結果集中的任何一行使用獨佔鎖定時，可以成功申請獨佔鎖定，否則會被阻塞。

 

3、意圖鎖定

意圖共用鎖（IS）：表示事務準備給資料行加入共用鎖定，也就是說一個資料行加共用鎖定前必須先取得該表的IS鎖 

意向獨佔鎖定（IX）：類似上面，表示事務準備給資料行加入獨佔鎖定，說明事務在一個資料行加獨佔鎖定前必須先取得該表的IX鎖。 

意圖鎖定是InnoDB自動加的，不需要使用者幹預。 

 

三、總結                                                                                                                                          

對於insert、update、delete，InnoDB會自動給涉及的資料加獨佔鎖定；

對於一般的Select語句，InnoDB不會加任何鎖。

事務可以通過以下語句給顯示加共用鎖定或獨佔鎖定：

共用鎖定：SELECT ... LOCK IN SHARE MODE; 

獨佔鎖定：SELECT ... FOR UPDATE;

 

 

MySQL常用引擎的鎖機制