MySQL鎖機制

1 鎖的類型

1.1 行級鎖

MySQL提供兩種標準級別的行級鎖

• 共享鎖 (S Lock)：允許事務讀取一行數據
• 排他鎖 (X Lock)：允許事務刪除或更新一行數據

排他鎖與共享鎖的兼容性

1.2 意圖鎖

InnoDB支持多細粒度鎖定，允許在行級上的鎖和表級上的鎖同時存在

InnoDB還支持額外的鎖方式，稱之意向鎖，是表級別的鎖，用于在一個事務中揭示下一行將被請求的鎖的類型

• 意向共享鎖(IS Lock)：事務想獲取表中某幾行的共享鎖
• 意向排他鎖(IX Lock)：事務想獲取表中某幾行的排他鎖

意圖鎖需遵循以下協議：

• 在事務獲取某行的共享鎖前，首先需獲取IS鎖或更強的鎖
• 在事務獲取某行的獨占鎖前，首先在表上獲取IX鎖

X鎖與任何的鎖都不兼容，S鎖與IX鎖不兼容，剩下的均兼容

意向鎖只會阻塞表級別的鎖，并不會阻塞行級別的鎖

InnoDB存儲引擎的兩種加鎖操作：

• select … for update：對讀取的行加一個X鎖，其他事務加鎖將會被阻塞
• select … lock in share mode：對讀取的行記錄加一個S鎖，其他事務可以加S鎖，但對于X鎖將會被阻塞

1.3 自增鎖

事務插入到具有AUTO-INC列的一種特殊表級鎖，當事務插入時，必須獲取自增鎖以獲取自增列的值

innodb_autoinc_lock_mode參數

• 值0：在5.1版本之前的自增長實現方式，通過表鎖的AUTO-INC Locking方式
• 值1(默認)：對于Simple Inserts，使用互斥量對內存的計數器進行累加，對于Bulk Inserts，使用傳統表鎖的AUTO-INC Locking方式
• 值2：此模式任何時候都可以使用Row-Base Replication，保證了最大并發性能和Replication數據的同步

1.4 外鍵的鎖機制

外鍵的插入更新需先查詢父表記錄，若該行記錄被另一個事務加X鎖未提交，則后續插入操作首先會加入S鎖，則插入操作會被阻塞，提交后也會產生數據不一致的情況

2 鎖算法

InnoDB三個行鎖算法：

• Record Lock： 單行記錄的鎖
• Gap Lock：間隙鎖，鎖定一個范圍，不包含記錄本身
• Next-Key Lock：鎖定一個范圍，包含記錄本身，在可重復讀隔離級別下的默認行記錄鎖定算法

3 鎖問題

• 丟失更新

  多個事務同時修改某個行數據，導致某個事務的修改被覆蓋

• 臟讀

  臟數據概念：緩沖區已被修改的頁，尚未刷寫會磁盤的數據

  臟讀：不同的事務下，讀取到其他事務未提交的數據，存在于讀未提交隔離級別下

• 不可重復讀

  同一個事內，若數據被另一個事務修改提交，會導致兩次讀取的數據不一致，違反了事務一致性的原則

  在InnoDB里，通過Next-Key Lock算法避免了不可重復讀的問題，在mysql里定義為Phantom Problem(幻讀)，Next-key算法會鎖定掃描到的以及間隙，避免了范圍內的插入操作，避免了不可重復讀的問題

4 阻塞

一個事務中的鎖需要等待另一個事務中的鎖釋放占用的資源，使用mutex數據結構實現阻塞機制

InnoDB使用參數innodb_lock_wait_timeout控制鎖的等待時間，參數innodb_rollback_on_timeout設定是否進行回滾，默認OFF表示不回滾，lock參數可在數據庫運行時動態調整，而rollback參數是靜態的，僅可以在啟動時調整

5 死鎖

數據庫在并行情況下可能會發生死鎖，存在于兩種資源之間的相互等待

InnoDB在偵測到死鎖后，會回滾事務。如oracle，死鎖多發生于未對外鍵添加索引，而InnoDB引擎會自動進行添加，不可人為刪除外鍵索引，因此較好的解決了這種問題

6 鎖升級

定義：將當前鎖的粒度降低，例如把一個表的100個行鎖升級成一個頁鎖，或頁鎖升級成表鎖，為避免鎖的開銷，會頻繁出現鎖升級的現象

mssql在05版之后支持行鎖，當一條SQL在一個對象的鎖數量超過閾值5000或鎖資源占用的內存超過40%會發生鎖升級

InnoDB中不存在鎖升級的問題，MySQL里鎖的開銷與數量無關，類似Oracle的設計