MySQL的鎖

小強Zzz發表於2021-01-31
MySql

前言

mysql按鎖的範圍分三種

  • 表級鎖：開銷小，加鎖快；不會出現死鎖，鎖定粒度大，發生鎖衝突概率最高，併發度最低。
  • 行級鎖：開銷大，加鎖慢，會出現死鎖，鎖定粒度小，發生鎖衝突的概率最低，併發度最高。

  • 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間，會出現死鎖，鎖定粒度界於表鎖和行鎖之間，併發度一般。
    從上述三種鎖的特點來看，很難說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適。比如，MyISAM和MEMORY引擎採用的是表級鎖；InnoDB引擎既支援行級鎖，也支援表級鎖，但預設情況下采用行級鎖。

    InnoDB的加鎖模式

    InnoDB實現了以下兩種型別的行鎖。

  • 共享鎖（S）：允許一個事務讀一行，阻止其他事務獲得相同資料的排他鎖，也叫讀鎖。
  • 排他鎖（X）：允許獲得排他鎖的事務更新資料，阻止其他事務取得相同資料集的共享鎖與排他鎖，也叫寫鎖。

同時mysql還支援與行共享鎖和行排他鎖類似的表共享鎖和表排他鎖因為鎖的粒度不同，表鎖的範圍覆蓋了行鎖的範圍，所以表鎖和行鎖會產生衝突，例如事務A對錶中某一行資料加了行鎖，然後事務B想加表鎖，正常來說是應該要衝突的。要判斷是否衝突就得遍歷每一行資料了，這樣的效率不高，因此我們就有了意向表鎖。

意向鎖的主要目的是為了使得 行鎖 和 表鎖 共存，事務在申請行鎖前，必須先申請表的意向鎖，成功後再申請行鎖。

意向鎖分為意向共享鎖和意向排他鎖。

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

上述鎖模式的相容情況如下表所示

右側代表請求鎖模式，下側代表當前鎖模式XIXSIS
X衝突衝突衝突衝突
IX衝突相容衝突相容
S衝突衝突相容相容
IS衝突相容相容相容

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

意向鎖是表級鎖，但是卻表示事務正在讀或寫某一行記錄，而不是整個表，所以意向鎖之間不會產生衝突，真正的衝突在加行鎖時檢查。

加鎖方法

意向鎖是InnoDB自動加的，不需要使用者干預。

隱式上鎖

  • 對於UPDATE，DELETE和INSERT語句，InnoDB會自動給設計資料集加排他鎖；
  • 對於普通SELETE語句，INNODB不會加任何鎖；

  • InnoDB會根據隔離級別在需要的時候自動加鎖；

    顯式上鎖

    select * from tableName lock in share mode;//讀鎖
select * from tableName for update;//寫鎖

    解鎖

  • 提交事務（commit）
  • 回滾事務（rollback）

  • kill阻塞程式
    上讀鎖例項

    事務A事務B
    begin;
    select * from teacher where id = 2 lock in share mode;// 上讀鎖
    select * from teacher where id = 2;// 可以正常讀取
    update teacher set name = 3 where id =2;// 可以更新操作update teacher set name = 5 where id =2;// 被阻塞
    commit;
    update teacher set name = 5 where id =2;// 更新操作成功

    上寫鎖例項

    事務A事務B
    begin;
    select * from teacher where id = 2 for update;// 上寫鎖
    select * from teacher where id = 2;// 可以正常讀取
    update teacher set name = 3 where id =2;// 可以更新操作update teacher set name = 5 where id =2;// 被阻塞
    rollback;
    update teacher set name = 5 where id =2;// 更新操作成功

    為什麼上了寫鎖，別的事務還可以讀操作？
    因為InnoDB有MVCC機制（多版本併發控制），可以使用快照讀，而不會被阻塞。

    InnoDB行鎖實現方式

    行鎖（Record Lock）

    行鎖總是會去鎖住索引記錄，如果InnoDB儲存引擎表建立的時候沒有設定任何一個索引，這時InnoDB儲存引擎會使用隱式的聚簇索引來進行鎖定。

    間隙鎖（Gap Lock）

    當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。
    優點：解決了事務併發的幻讀問題
    不足：因為query執行過程中通過範圍查詢的話，他會鎖定爭個範圍內所有的索引鍵值，即使這個鍵值並不存在。
    間隙鎖有一個致命的弱點，就是當鎖定一個範圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成鎖定的時候無法插入鎖定鍵值範圍內任何資料。在某些場景下這可能會對效能造成很大的危害。

    Next-key Lock 鎖

    同時鎖住資料+間隙鎖
    在Repeatable Read隔離級別下，Next-key Lock 是預設的行記錄鎖定演算法。
    假如teacher表中只有101條記錄，其id值分別是1-101，SQL語句如下

    Select * from teacher where id  〉 100 for update；

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

    樂觀鎖與悲觀鎖

  • 樂觀鎖(Optimistic Lock)：假設不會發生併發衝突，只在提交操作時檢查是否違反資料完整性。 樂觀鎖不能解決髒讀的問題。
    樂觀鎖, 顧名思義，就是很樂觀，每次去拿資料的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個資料，可以使用版本號等機制。樂觀鎖適用於多讀的應用型別，這樣可以提高吞吐量，像資料庫如果提供類似於write_condition機制的其實都是提供的樂觀鎖。

  • 悲觀鎖(Pessimistic Lock)：假定會發生併發衝突，遮蔽一切可能違反資料完整性的操作。
    悲觀鎖，就是很悲觀，每次去拿資料的時候都認為別人會修改，所以每次在拿資料的時候都會上鎖，這樣別人想拿這個資料就會被阻塞直到它拿到鎖。傳統的關係型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，都是在做操作之前先上鎖。

    總結

鎖和多版本資料（MVCC）是 InnoDB 實現一致性讀和四種隔離隔離級別的手段。

因此，在不同的隔離級別下，InnoDB 處理 SQL 時需要的鎖是不同的。

相關文章