一文總結MySQL各種鎖

概述

對于後端Java開發人員來說，鎖主要有Java鎖和DB鎖。Java鎖，請參考​​一文總結Java開發各種鎖​​。本文所述的DB鎖，可能會局限于MySQL資料庫。

隔離級别與鎖的關系

• 在RU級别下，讀取資料不需要加共享鎖，這樣就不會跟被修改的資料上的排他鎖沖突
• 在RC級别下，讀操作需要加共享鎖，但是在語句執行完以後釋放共享鎖
• 在RR級别下，讀操作需要加共享鎖，但是在事務送出之前并不釋放共享鎖，也就是必須等待事務執行完畢以後才釋放共享鎖
• SERIALIZABLE是限制性最強的隔離級别，因為該級别鎖定整個範圍的鍵，并一直持有鎖，直到事務完成

行鎖，表鎖，頁鎖

鎖粒度：

• 表鎖：table-level locking，MYISAM引擎，開銷小，加鎖快；不會出現死鎖；鎖粒度大，發生鎖沖突機率高，并發度低；
• 行鎖：row-level locking，INNODB引擎，開銷大，加鎖慢；會出現死鎖；鎖粒度小，發生鎖沖突的機率低，并發度高；
• 頁鎖：BDB引擎，鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。取折衷的頁級，一次鎖定相鄰的一組記錄。開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

不同的存儲引擎支援的鎖粒度是不一樣的：

• InnoDB行鎖和表鎖都支援，預設為行級鎖
• MyISAM隻支援表鎖

樂觀鎖、悲觀鎖

樂觀鎖，是一種思想，而不是資料庫層面上的鎖，是需要自己手動去加的鎖。通過版本号（時間戳），加字段或CAS算法方式實作。

共享鎖和排它鎖是悲觀鎖的不同的實作，都是行級鎖。

要使用悲觀鎖，需要關閉MySQL資料庫的自動送出屬性，因為MySQL預設使用autocommit模式，即執行一個更新操作後，MySQL會立刻将結果進行送出。

設定MySQL為非autocommit模式：​​

​set autocommit=0;​

​

共享鎖、排它鎖

共享鎖

share lock，也叫read lock，S鎖，讀鎖。讀取操作建立的鎖。共享鎖指的就是對于多個不同的事務，對同一個資源共享同一個鎖。

其他使用者可以并發讀取資料，但任何事務都不能對資料進行修改（擷取資料上的排他鎖），直到已釋放所有共享鎖。如果事務T對資料A加上共享鎖後，則其他事務隻能對A再加共享鎖，不能加排他鎖。獲得共享鎖的事務隻能讀資料，不能修改資料。

在查詢語句後面加上​

​lock in share mode​

​​，MySQL會對查詢結果中的每行都加共享鎖，當沒有其他線程對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。其他線程也可以讀取使用共享鎖的表，而且這些線程讀取的是同一個版本的資料。

加上共享鎖後，對于​​

​update,insert,delete​

​語句會自動加排它鎖。

排它鎖

exclusive lock，也叫writer lock，X鎖，寫鎖。排它鎖指對于多個不同的事務，對同一個資源隻能有一把鎖。若事務1對資料對象A加上X鎖，事務1 可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到事務1 釋放A上的鎖。這保證其他事務在事務1釋放A上的鎖之前不能再讀取和修改A。排它鎖會阻塞所有的排它鎖和共享鎖。

讀取為什麼要加讀鎖呢：防止資料在被讀取的時候被别的線程加上寫鎖。

對于​

​update, insert, delete​

​​語句會自動給涉及到的資料集加排它鎖。執行語句後面加上​

​for update​

​​就可以。

執行事務時關鍵字​​

​select…for update​

​會鎖定資料，防止其他事務更改資料。但是鎖定資料也是有規則的。查詢條件與鎖定範圍：

1. 具體的主鍵值為查詢條件

   比如查詢條件為主鍵ID=1等等，如果此條資料存在，則鎖定目前行資料，如果不存在，則不鎖定。

2. 不具體的主鍵值為查詢條件

   比如查詢條件為主鍵ID>1等等，此時會鎖定整張資料表。

3. 查詢條件中無主鍵

   會鎖定整張資料表。

4. 如果查詢條件中使用索引為查詢條件

   明确指定索引并且查到，則鎖定整條資料。如果找不到指定索引資料，則不加鎖。

總結

行鎖又分共享鎖和排他鎖。

（當然，說的是InnoDB引擎）行級鎖都是基于索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。

行級鎖的缺點：由于需要請求大量的鎖資源，速度慢，記憶體消耗大。

表鎖下又分為兩種模式：表讀鎖（表共享讀鎖，Table Read Lock）、表寫鎖（表獨占寫鎖，Table Write Lock）

在表讀鎖和表寫鎖的環境下：讀讀不阻塞，讀寫阻塞，寫寫阻塞。讀鎖和寫鎖是互斥的，讀寫操作是串行。

如果某個程序想要擷取讀鎖，同時另外一個程序想要擷取寫鎖。在MySQL裡邊，寫鎖是優先于讀鎖的。寫鎖和讀鎖優先級，可以通過參數調節的：​​

​max_write_lock_count​

​​和​

​low-priority-updates​

​。

InnoDB引擎的行鎖是怎麼實作的？

InnoDB是基于索引來完成行鎖，​​

​select * from tab_with_index where id = 1 for update;​

​​，​

​for update​

​可根據條件來完成行鎖鎖定，要求id是有索引鍵的列。如果 id 不是索引鍵那麼InnoDB将完成表鎖，并發将無從談起。

InnoDB存儲引擎的鎖的算法有三種

• Record lock：單個行記錄上的鎖
• Gap lock：間隙鎖，鎖定一個範圍，不包括記錄本身
• Next-key lock：record+gap 鎖定一個範圍，包含記錄本身

相關知識點：

1. innodb對于行的查詢使用next-key lock
2. Next-locking keying為了解決Phantom Problem幻讀問題
3. 當查詢的索引含有唯一屬性時，将next-key lock降級為record key
4. Gap鎖設計的目的是為了阻止多個事務将記錄插入到同一範圍内，而這會導緻幻讀問題的産生
5. 兩種方式顯式關閉gap鎖：（除了外鍵限制和唯一性檢查外，其餘情況僅使用record lock） A. 将事務隔離級别設定為RC B. 将參數​

   ​innodb_locks_unsafe_for_binlog​

   ​設定為1

間隙鎖GAP

當用範圍條件檢索資料而不是相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合範圍條件的已有資料記錄的索引項加鎖；對于鍵值在條件範圍内但并不存在的記錄，叫做間隙，GAP。InnoDB對間隙加鎖，即間隙鎖。間隙鎖隻會在Repeatable read隔離級别下使用。

間隙鎖的目的：

1. 為了防止幻讀(Repeatable read隔離級别下再通過GAP鎖即可避免幻讀)
2. 滿足恢複和複制的需要

MySQL的恢複機制要求：在一個事務未送出前，其他并發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀

總結

1. MyISAM存儲引擎執行SQL語句自動加鎖。查詢語句給涉及的所有表加讀鎖，更新操作（UPDATE、DELETE、INSERT等）給涉及的表加寫鎖，這個過程并不需要使用者幹預；
2. 樂觀鎖其實是一種思想；
3. 悲觀鎖用的就是資料庫的行鎖；

死鎖

死鎖是指兩個或多個事務在同一資源上互相占用，并請求鎖定對方的資源，進而導緻惡性循環的現象。

常見的解決死鎖的方法

1. 如果不同程式會并發存取多個表，盡量約定以相同的順序通路表，可以大大降低死鎖機會；
2. 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖産生機率；
3. 對于非常容易産生死鎖的業務部分，可以嘗試使用更新鎖定顆粒度，通過表級鎖定來減少死鎖産生的機率；

參考