innodb的鎖講解

• innodb的鎖，我們可以從幾個維度來分析，分為級別，類型
  • 級別
    • 行級鎖
    • 表級鎖
  • 類型
    • 共享鎖(S)，也稱為寫鎖, 級別:行級鎖
    • 意向共享鎖(IS)，也稱為意向寫鎖 級別:表級鎖
    • 排他鎖(X)，也稱為讀鎖 屬于行級鎖 級別: 行級鎖
    • 意向排他鎖(IX)，也稱為意向讀鎖 級別: 表級鎖
  • 行鎖的算法
    • Record Locks
    • Gap Locks
    • Next-Key Locks
    • Insert intention Locks
    • AUTO-INC Locks
    • Predicate Locks for Spatial Indexes
• 鎖的兼容性請看下圖
  • 
• 我們就先從類型講起
  • 共享鎖
    • 允許持有鎖的事務讀取行
    • 假如有一個a事務獲取了x行的共享鎖，這時候b事務也來請求x行的鎖，那么會進行一下處理
      • 如果b請求的是x行的共享鎖，那么會立刻授予，這時候a事務和b事務都擁有x行的共享鎖
      • 如果b請求的是x的排他鎖，那么不會立刻授予，因為共享鎖和排他鎖是不兼容的
  • 排他鎖
    • 允許持有鎖的事務更新或刪除行。
    • 假如有一個a事務獲取了x行的排他鎖，這時候b事務也來請求x行的鎖，這時候不管b事務請求的鎖是共享鎖還是排他鎖，都不能立即授予，只能等到a事務釋放了在x行的排他鎖才能授予b事務，因為排他鎖與任何的鎖都不兼容
  • 意向鎖的講述
    • innodb支持多粒度鎖定，這種鎖定允許事務在行級上的鎖和表級上的鎖同行存在，為了支持在不同粒度上進行操作，innodb存儲引擎支持一個額外的鎖方式，稱之為意向鎖，意向鎖是將鎖定的對象分為多個層次，意向鎖意味著事務希望在更細粒度上進行加鎖，也就是如果一個事務需要針對記錄R來加排他鎖，那么就需要對記錄R所在的數據庫，表，頁進行加鎖，最后對記錄R加排他鎖，如果有任何一個部分導致等待，那么該操作需要粗粒度鎖的完成。
    • 意向鎖是表級鎖，設計目的主要是為了在下一個事務中揭示下一行將被請求的鎖類型，由于innodb存儲引擎支持的是行級別的鎖，因此意向鎖其實不會阻塞除全表掃描以外的任何請求。
    • 上面的可能不好理解，下面我說一個實際的例子
      • 我們有一個student表
      • mysql> show create table student;
      • +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      • | Table | Create Table |
      • +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      • | student | CREATE TABLE student (
      • id int(11) NOT NULL AUTO_INCREMENT,
      • student_num int(11) NOT NULL DEFAULT '0' COMMENT '學號',
      • name varchar(32) NOT NULL DEFAULT '' COMMENT '學生姓名',
      • PRIMARY KEY (id),
      • UNIQUE KEY uqidx_student_num (student_num)
      • ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
      • +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      • 1 row in set (0.00 sec)
      • mysql> select * from student;
      • +----+-------------+----------+
      • | id | student_num | name |
      • +----+-------------+----------+
      • | 1 | 1 | zhangsan |
      • | 2 | 2 | lisi |
      • | 3 | 3 | wangwu |
      • | 4 | 4 | zhaoliu |
      • | 5 | 5 | liqi |
      • +----+-------------+----------+
      • 5 rows in set (0.00 sec)
      • session1
        • mysql> start transaction;
        • Query OK, 0 rows affected (0.00 sec)
        • mysql> select * from student where student_num=4 for update;
        • +----+-------------+---------+
        • | id | student_num | name |
        • +----+-------------+---------+
        • | 4 | 4 | zhaoliu |
        • +----+-------------+---------+
        • 1 row in set (0.00 sec)
      • session2
        • mysql> LOCK TABLE student write;
      • 這時候我們發現session2一直在等待，因為session2想獲取student整個表的寫鎖，如果session2申請成功了， 它是可以修改student表的任意一行的，那么大家會說session1已經獲取了student_num=4的排他鎖呢，如果session2申請成功了，那么student_num=4的記錄也會被session2修改了，所以說這時候session2肯定是會阻塞的，那么數據庫是根據什么方法來判斷使得session2被阻塞呢，無非就下面兩種方法
        1. 判斷這個表是否被其他事務用表鎖給鎖住
        2. 判斷這個表的每一行是否有行鎖
      • 根據前面的講述，假如我們要針對某一條記錄加排他鎖的話，那么會在記錄對應的表里面先加一個共享排他鎖，然后再到記錄上面加一個排他鎖，這時候我們可以發現session1可以針對student表加了一個共享排他鎖了，那么這時候session2發現student上面已經有其他事務加上共享排他鎖了，因此會阻塞。如果沒有意向鎖的話，那session2就要走方法2了，那么就需要判斷每一行，那么需要遍歷整個表，這種效率非常差，特別碰到表數據量大的時候。
  • 意向共享鎖
    • 事務想要獲取一個表中某幾行的共享鎖
  • 意向排他鎖
    • 事務想要獲取一個表中某幾行的排他鎖
  • 請注意：對于insert、update、delete，InnoDB會自動給涉及的數據加排他鎖（X）；對于一般的 Select 語句，InnoDB 不會加任何鎖，可能有人會有疑問，什么是一般的select，什么是特殊的select呢，一般的select就是select column from table where x=1 特殊的select就是select column from table where x=1 lock in share mode或者select column from table where x=1 for udpate ,前面加共享鎖，后者加排他鎖

• 行鎖

  • Record Locks
    • 記錄鎖是索引記錄上的鎖，例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;防止任何其他事務插入，更新或刪除t.c1的值為10的行
    • 記錄鎖始終鎖定索引記錄，如果一個表沒有定義任何的索引，像這種情況，innodb會創建一個隱藏的聚簇索引并且使用此索引進行記錄鎖定
    • 需要注意的是:
      • innodb的記錄鎖是針對索引加鎖，不是針對物理記錄加鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，將出現鎖沖突
  • Gap Locks
    • 間隙鎖是鎖定索引記錄之間的間隙，或鎖定第一個索引記錄之前或最后一個索引記錄之后的間隙上。。例如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;由于范圍內所有現有值之間的間隔都被鎖定，因此可以防止其他事務向列t.c1中插入值15，無論該列中是否已有任何此類值。
    • 對于使用唯一索引鎖定行的語句，不需要使用間隙鎖(這不包括搜索條件僅包括多列唯一索引的一些列的情況; 在這種情況下，確實會出現間隙鎖定），例如，如果id列是一個唯一索引，那么下面的語句只會在id=100的那行上面加一個索引記錄鎖,而不會關心別的會話（session）是否在前面的間隙中插入數據。
    • SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
    • 如果id沒有索引或者沒有唯一索引，則該語句將鎖定前述的間隙
    • 間隙鎖的主要作用，就是和記錄鎖組成Next-key鎖，解決幻讀問題
    • 間隙鎖在不同的隔離級別下，有著不同的作用范圍，能發揮間隙鎖作用的，是’REPEATTABLE READ’隔離級別，在這個級別下使用帶有間隙鎖的Next-Key鎖，解決了幻行的問題。這個涉及到了事務隔離級別和一致讀的相關信息，后面我也會更新對應的文章
  • Next-Key Locks
    • next-key鎖是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合,也就是相當于Record Locks+Gap Locks
    • InnoDB以這種形式實現行級鎖，當它查找或掃描表索引的時候，它會在遇到的索引記錄上設置共享或排它鎖。因此，行級鎖實際上是索引記錄鎖。next-key鎖同樣會影響索引記錄之前的間隙。就是說，next-key 鎖就是一個索引記錄鎖加上索引記錄前間隙的間隙鎖。如果一個會話擁有記錄 R 的索引上面的一個共享鎖或獨占鎖，另一個會話不能在索引順序中的R之前的間隙中插入新的索引記錄。
    • 下面我們來看一個表
    • mysql> create table goods(
    • -> id int not null auto_increment primary key,
    • -> title varchar(32) not null default '' comment '商品名稱',
    • -> classify tinyint not null default 0 comment '商品類型',
    • -> index idx_classify (classify)
    • -> )engine=innodb charset=utf8;
    • mysql> insert into goods (title,classify) values ('商品1',1),('商品2',3),('商品3',5),('商品4',8),('商品5',10),('商品6',1),('商品7',3),('商品8',5),('商品9',8),('商品10',10);
    • mysql> select * from goods;
    • +----+----------+----------+
    • | id | title | classify |
    • +----+----------+----------+
    • | 1 | 商品1 | 1 |
    • | 2 | 商品2 | 3 |
    • | 3 | 商品3 | 5 |
    • | 4 | 商品4 | 8 |
    • | 5 | 商品5 | 10 |
    • | 6 | 商品6 | 1 |
    • | 7 | 商品7 | 3 |
    • | 8 | 商品8 | 5 |
    • | 9 | 商品9 | 8 |
    • | 10 | 商品10 | 10 |
    • +----+----------+----------+
    • 10 rows in set (0.00 sec)
    • mysql> select distinct(classify) from goods;
    • +----------+
    • | classify |
    • +----------+
    • | 1 |
    • | 3 |
    • | 5 |
    • | 8 |
    • | 10 |
    • +----------+
    • 5 rows in set (0.02 sec)
    • #在REPEATTABLE READ隔離級別下，執行查詢時，因為Next-Key鎖存在，寫Next-Key的鎖定范圍如下
      • (-∞,1) 鎖定索引項1和1之前的間隙，因為1之前沒有其他索引項，所以負無窮
      • (1,3) 鎖定1和3之前的間隙，不包括1，包括3
      • (3,5) 同上
      • (5,8) 同上
      • (8,10) 同上
      • (10,∞) 鎖定索引項10和10之后的間隙，因為10之后沒有其他索引項，所以為正無窮
    • 下面我們就用一個demo來驗證一下
      • Session1
        • mysql> start transaction;
        • Query OK, 0 rows affected (0.00 sec)
        • mysql> select * from goods where classify=3 for update;
        • +----+---------+----------+
        • | id | title | classify |
        • +----+---------+----------+
        • | 2 | 商品2 | 3 |
        • | 7 | 商品7 | 3 |
        • +----+---------+----------+
        • 2 rows in set (0.00 sec)
        • #對于輔助索引，其加的是Next-Key鎖，鎖定的范圍是(1,3),特別需要注意的是，innodb存儲引擎還會對輔助索引下一個鍵值加上gab lock，即還有一個輔助索引范圍(3,5)的鎖
      • Session2
        • mysql> start transaction;
        • Query OK, 0 rows affected (0.00 sec)
          mysql> insert into goods (title,classify) select '商品11',4;
        • #這時候可以看到session2給阻塞了，因為session1的Next-Key的鎖定范圍是(1,3),(3,5)，正好包含了4
        • mysql> insert into goods (title,classify) select '商品11',6;
        • Query OK, 1 row affected (0.00 sec)
        • Records: 1 Duplicates: 0 Warnings: 0
        • 插入classify=6的就立刻成功了， 因為6不在(1,3),(3,5)的范圍內
  • Insert intention Locks
    • Insert intention 鎖是插入行之前由INSERT操作設置的一種間隙鎖。。此鎖表示要插入的意圖是多個事務插入到相同的索引間隙時, 如果它們沒有插入到間隙中的同一位置, 則不必等待對方。。假設存在值為4和7的索引記錄。兩個事務分別嘗試插入5和6，分別用插入意向鎖鎖住4和7之間的間隙，然后再取得插入行的排它鎖，但是相互不會阻塞，因為這些行是不沖突的。
  • AUTO-INC Locks
    • AUTO-INC鎖是由插入到帶有 AUTO_INCREMENT 列的表中的事務所采取的特殊表級鎖。。一個最簡單的例子，如果一個事務正在向表中插入值，則任何其他事務必須等待對該表執行自己的插入，以便第一個事務插入的行接收連續的主鍵值。
  • Predicate Locks for Spatial Indexes
    • innodb支持空間索引，如果使用Next-Key來支持空間索引，則不能滿足要求，這是因為普通的索引都是鍵值類型，意味著索引存在一個方向，這個方向是單向的，要不升序要不降序，這個方向的存在，使得數據庫存儲引擎可以利用索引進行常規的范圍查詢
    • 但是在空間數據類型上面，這個單向的有序變得失去了作用，因為空間數據是多維多向的，是以區域或空間為范圍的，沒有確定的方向順序，所以單向的Next-Key滿足不了要求
    • 空間索引是建立愛MBR上的，innodb為索引項的MBR增加了一個謂詞鎖，實現空間索引上的并發控制
• 參考資料
  • 鏈接：
    • https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
  • 書籍：
    • 高性能MySQ
    • MySQL技術內幕 innoDB存儲引擎
    • 數據庫事務處理的藝術 事務管理與并發控制