文章目錄

• 前言：
• 共享鎖和排它鎖
• LOCK TABLES 和 UNLOCK TABLES 語句
• 意向鎖
• 記錄鎖Record Locks
• 間隙鎖 Gap Locks
• 下一鍵鎖定 next-key
• 插入意圖鎖

前言：

與sql標準不同的地方在于innodb存儲引擎在可重讀事務隔離級別下使用的是Next-Key Lock鎖算法，因此可以避免幻讀的產生，與其他數據庫系統是不同的，所以innodb的可重復讀已經可以完全保證事務的隔離性要求，即達到了sql標準的可串行化隔離級別。但是在分布式事務的情況下innodb一般用到可串行化隔離級別。
MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀）。我們可以通過SELECT @@tx_isolation;命令來查看，MySQL 8.0 該命令改為SELECT @@transaction_isolation

共享鎖和排它鎖

InnoDB實現標準的行級鎖定，其中有兩種類型的鎖：共享(S)鎖和排他(X)鎖。

共享(S)鎖允許持有鎖的事務讀取一行。

排他(X)鎖允許持有鎖的事務更新或刪除行。

如果事務T1在行r上持有共享(S)鎖，則來自某些不同事務T2的對行r的鎖請求將按以下方式處理：

T2對S鎖定的請求可以立即獲得批準。結果，T1和T2都在r上保持S鎖定。

T2對X鎖定的請求無法立即獲得批準。

如果事務T1在行r上擁有排他(X)鎖，則不能立即批準來自某個不同事務T2的對r上任一類型的鎖的請求。相反，事務T2必須 await 事務T1釋放對行r的鎖定
隔離級別與鎖的關系：

在Read Uncommitted級別下，讀操作不加S鎖；

在Read Committed級別下，讀操作需要加S鎖，但是在語句執行完以后釋放S鎖；

在Repeatable Read級別下，讀操作需要加S鎖，但是在事務提交之前并不釋放S鎖，也就是必須等待事務執行完畢以后才釋放S鎖。

在Serialize級別下，會在Repeatable Read級別的基礎上，添加一個范圍鎖。保證一個事務內的兩次查詢結果完全一樣，而不會出現第一次查詢結果是第二次查詢結果的子集

LOCK TABLES 和 UNLOCK TABLES 語句

LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] …

lock_type: {
READ [LOCAL] | [LOW_PRIORITY] WRITE }

UNLOCK TABLES

MySQL 使 Client 端會話能夠顯式地獲取 table 鎖，以便與其他會話協作訪問 table，或者防止其他會話在會話需要互斥訪問期間修改 table。會話只能為其自身獲取或釋放鎖。一個會話無法獲取另一會話的鎖，也不能釋放另一會話持有的鎖。

在更新 table 時，鎖可用于模擬事務或提高速度。 table 鎖定限制和條件中對此進行了更詳細的說明。

LOCK TABLES明確獲取當前 Client 端會話的 table 鎖。可以為基本 table 或視圖獲取 table 鎖。您必須具有LOCK TABLES特權和SELECT特權才能鎖定每個對象。

對于視圖鎖定，LOCK TABLES將視圖中使用的所有基本 table 添加到要鎖定的 table 集中，并自動鎖定它們。從 MySQL 5.7.32 開始，LOCK TABLES檢查視圖定義器是否對基于視圖的 table 具有適當的特權。

如果使用LOCK TABLES顯式鎖定 table，則觸發器中使用的任何 table 也會隱式鎖定，如鎖定 table 和觸發器中所述。

UNLOCK TABLES明確釋放當前會話持有的所有 table 鎖。 LOCK TABLES在獲取新鎖之前隱式釋放當前會話持有的所有 table 鎖。

意向鎖

InnoDB支持多重粒度鎖定，它允許行鎖和 table 鎖并存。例如，諸如鎖 table（上面LOCK TABLES）之類的語句在指定的 table 上具有排他鎖(X鎖)。為了使在多個粒度級別上的鎖定切實可行，InnoDB使用intention locks。意向鎖是 table 級鎖，指示事務稍后對 table 中的行需要哪種類型的鎖(共享鎖或排他鎖)。有兩種類型的意圖鎖：

意向共享鎖(IS) 表示事務打算對 table 中的各個行設置“共享”鎖。
如果對一個數據對象加IS鎖，表示它的后裔結點擬（意向）加 S鎖。例如，要對某個元組加 S鎖，則要首先對關系和數據庫加 IS鎖。
意向排他鎖(IX) 表示事務打算對 table 中的各個行設置排他鎖
如果對一個數據對象加 IX鎖，表示它的后裔結點擬（意向）加 X鎖。例如，要對某個元組加 X鎖，則要首先對關系和數據庫加 IX鎖。
例如：
update new_table set user_id = 911 where id = 1;
假設我們執行這么一條語句，innodb除了在id=1的這條記錄上增加了行級X鎖之前，還對所在表添加了一個意向排它鎖。

這個時候如果我們有針對 new_table 的表級鎖操作，如：alter table、drop table、lock table 的操作時，會先檢查對應表是否存在意向排它鎖，若存在則等待鎖釋放。

為什么這么做？

沒有意向鎖，你做表鎖的話，要挨行去掃描行鎖吧。

同時意向鎖之間、意向鎖和行鎖也不存在沖突。

比如上面第一條sql不提交，我在另外事務中執行：

update new_table set user_id = 119 where id = 2;

相當于又給new_table加了一個意向排它鎖，但是因為意向X鎖之間也不沖突，所以不會被鎖住。（id=1,2 要切實存在）.

鎖之間的相容與互斥表：

如果鎖與現有鎖兼容，則將其授予請求的事務，但如果與現有鎖沖突，則不授予該請求。事務 await 直到沖突的現有鎖被釋放。如果鎖定請求與現有鎖定發生沖突并且由于會導致deadlock而無法被授予，則會發生錯誤。

記錄鎖Record Locks

記錄鎖定是對索引記錄的鎖定。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其他事務插入，更新或刪除t.c1的值為10的行。

記錄鎖始終鎖定索引記錄，即使沒有定義索引的 table 也是如此。在這種情況下，InnoDB將創建一個隱藏的聚集索引，并將該索引用于記錄鎖定。
記錄鎖定的事務數據在顯示引擎的 INNODB 狀態和InnoDB monitor輸出中看起來類似于以下內容：

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table
test.t trx id 10078 lock_mode X locks rec but not gap Record lock,
heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0:
len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000274f; asc
'O;; 2: len 7; hex b60000019d0110; asc

間隙鎖 Gap Locks

間隙鎖定是對索引記錄之間的間隙的鎖定，或者是對第一個或最后一個索引記錄之前的間隙的鎖定。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;防止其他事務將15的值插入到t.c1列中，無論該列中是否已經有這樣的值，因為該范圍中所有現有值之間的間隙都被鎖定。

間隙可能跨越單個索引值，多個索引值，甚至為空。

間隙鎖是性能和并發性之間權衡的一部分，并且在某些事務隔離級別而非其他級別中使用。

對于使用唯一索引來鎖定唯一行來鎖定行的語句，不需要間隙鎖定。 (這不包括搜索條件僅包含多列唯一索引的某些列的情況；在這種情況下，會發生間隙鎖定.)例如，如果id列具有唯一索引，則以下語句僅使用具有id值 100 的行的索引記錄鎖，其他會話是否在前面的間隙中插入行都沒有關系：

SELECT * FROM child WHERE id = 100;
如果id未構建索引或索引不唯一，則該語句會鎖定前面的間隙。

在這里還值得注意的是，可以通過不同的事務將沖突的鎖保持在間隙上。例如，事務 A 可以在間隙上保留一個共享的間隙鎖(間隙 S 鎖)，而事務 B 可以在同一間隙上保留排他的間隙鎖(間隙 X 鎖)。允許沖突的間隙鎖的原因是，如果從索引中清除記錄，則必須合并由不同事務保留在記錄上的間隙鎖。

InnoDB中的間隙鎖是“完全禁止的”，這意味著它們的唯一目的是防止其他事務插入間隙。間隙鎖可以共存。一個事務進行的間隙鎖定不會阻止另一事務對相同的間隙進行間隙鎖定。共享和獨占間隙鎖之間沒有區別。它們彼此不沖突，并且執行相同的功能。

間隙鎖定可以顯式禁用。如果將事務隔離級別更改為READ COMMITTED或啟用innodb_locks_unsafe_for_binlog系統變量(現已棄用)，則會發生這種情況。在這種情況下，將禁用間隙鎖定進行搜索和索引掃描，并且僅將其用于外鍵約束檢查和重復鍵檢查。

使用READ COMMITTED隔離級別或啟用innodb_locks_unsafe_for_binlog還有其他效果。 MySQL 評估WHERE條件后，將釋放不匹配行的記錄鎖。對于UPDATE語句，InnoDB進行“半一致”讀取，以便將最新的提交版本返回給 MySQL，以便 MySQL 可以確定該行是否與UPDATE的WHERE條件匹配

下一鍵鎖定 next-key

下一鍵鎖定是索引記錄上的記錄鎖定和索引記錄之前的間隙上的間隙鎖定的組合。

InnoDB以這種方式執行行級鎖定：即當它搜索或掃描 table 索引時，它將在遇到的索引記錄上設置共享或互斥鎖。因此，行級鎖實際上是索引記錄鎖。索引記錄上的下一鍵鎖定也會影響該索引記錄之前的“間隙”。即，下一鍵鎖定是索引記錄鎖定加上索引記錄之前的間隙上的間隙鎖定。如果一個會話在索引中的記錄R上具有共享或排他鎖，則另一會話無法按照索引 Sequences 在R之前的間隙中插入新的索引記錄。

假定索引包含值 10、11、13 和 20.此索引的可能的下一鍵鎖定涵蓋以下間隔，其中，圓括號 table 示排除區間端點，方括號 table 示包括端點：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
對于最后一個時間間隔，next-key 鎖定將間隙鎖定在索引中的最大值之上，并且“ supremum”偽記錄的值高于索引中實際的任何值。最高不是 true 的索引記錄，因此，實際上，此下一鍵鎖定僅鎖定跟隨最大索引值的間隙。

默認情況下，InnoDB以REPEATABLE READ事務隔離級別運行。在這種情況下，InnoDB使用 next-key 鎖定進行搜索和索引掃描，這可以防止幻讀

為了防止產生幻讀，InnoDB使用了稱為下一鍵鎖定的算法，該算法將索引行鎖定與間隙鎖定結合在一起。 InnoDB執行行級鎖定的方式是，當它搜索或掃描 table 索引時，會在遇到的索引記錄上設置共享或互斥鎖。因此，行級鎖實際上是索引記錄鎖。此外，索引記錄上的下一鍵鎖定也會影響該索引記錄之前的“間隙”。即，下一鍵鎖定是索引記錄鎖定加上索引記錄之前的間隙上的間隙鎖定。如果一個會話在索引中的記錄R上具有共享或排他鎖，則另一會話不能按索引 Sequences 在R之前的間隙中插入新的索引記錄。

插入意圖鎖

插入意圖鎖定是一種在行插入之前通過INSERT操作設置的間隙鎖定。此鎖發出插入意圖的 signal 是，如果多個事務未插入間隙中的相同位置，則無需 await 彼此插入的多個事務。假設有索引記錄，其值分別為 4 和 7，單獨的事務分別嘗試插入值 5 和 6，在獲得插入行的排他鎖之前，每個事務都使用插入意圖鎖來鎖定 4 和 7 之間的間隙，但不要互相阻塞，因為行是無沖突的。

下面的示例演示了在獲得對插入記錄的排他鎖之前，使用插入意圖鎖的事務。該示例涉及兩個 Client 端 A 和 B。

Client 端 A 創建一個包含兩個索引記錄(90 和 102)的 table，然后啟動一個事務，該事務將排他鎖放置在 ID 大于 100 的索引記錄上。排他鎖在記錄 102 之前包括一個間隙鎖：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; mysql> INSERT INTO child (id) values (90),(102);mysql> START TRANSACTION; mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

±----+
| id |
±----+
| 102 |
±----+
ClientB 開始 Transaction 以將記錄插入空白。事務在 await 獲得排他鎖的同時獲取插入意圖鎖。

mysql> START TRANSACTION; mysql> INSERT INTO child (id) VALUES (101);

插入意圖鎖定的事務數據在顯示引擎的 INNODB 狀態和InnoDB monitor輸出中看起來類似于以下內容：

RECORD LOCKS space id 31 page no 3 n bits 72 index PRIMARY of table
test.child trx id 8731 lock_mode X locks gap before rec insert
intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3;
compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1:
len 6; hex 000000002215; asc " ;; 2: len 7; hex 9000000172011c;
asc r ;;…

總結

以上是生活随笔為你收集整理的mysql 共享锁和排他锁 意向锁 记录锁 Gap Locks Next-Key Locks 插入意向锁介绍的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。