搜尋

首頁 >資料庫 >mysql教程 >MySQL資料庫InnoDB引擎行級鎖定範圍詳解

MySQL資料庫InnoDB引擎行級鎖定範圍詳解

小云云
小云云原創
2018-02-23 13:49:111963瀏覽

Mysql資料庫InnoDB引擎支援行級鎖，也就是說我們可以對表中某些行資料執行鎖定操作，鎖定操作的影響是：如果一個事物對表中某行執行了鎖定操作，而另一個事務也需要對相同的行執行鎖定操作，這樣第二個事務的鎖定操作有可能被阻塞，一旦被阻塞第二個事務只能等到第一個事務執行完畢（提交或回滾）或逾時。

本文主要介紹InnoDB中的行鎖相關概念，重點介紹行鎖的鎖定範圍：

  • 什麼樣的SQL語句會加鎖？

  • 加上什麼樣的鎖定？

  • 加鎖語句會鎖定哪些行？

背景知識

上面我們簡單的介紹了InnoDB的行級鎖定，為了理解後面的驗證部分，需要補充一下背景知識。如果對相應知識非常了解，可以直接跳到驗證部分內容。

1. InnoDB鎖定的類型

InnoDB引擎使用了七種類型的鎖，他們分別是：

  • 共享排他鎖（Shared and Exclusive Locks）

  • 意圖鎖定（Intention Locks）

  • 記錄鎖定（Record Locks）

  • 間隙鎖定（Gap Locks）

  • Next-Key Locks

  • 插入意圖鎖定（Insert Intention Locks）

  • #自增鎖定（AUTO-INC Locks）

共享鎖（S鎖）和排他鎖（X鎖）的概念在許多程式語言中都出現過。先來描述這兩個鎖在MySQL中的影響結果：用一張經典的矩陣表格繼續說明共享鎖定和排他鎖的互斥關係：
#本文主要涉及Shared and Exclusive Locks，Record Locks，Gap Locks，Next-Key Locks這幾種鎖，其他類型鎖如果大家感興趣可以自己深入了解，在此不在詳述。 1.1 Shared and Exclusive Locks
如果一個交易對某一行資料加了S鎖，另一個事務還可以對對應的行加S鎖，但是不能對對應的行加X鎖。 如果一個事務對某一行資料加了X鎖，另一個事務既不能對對應的行加S鎖也不能加X鎖。
##-- S X
##########S#########0#######1 ###############X#########1#######1############

圖中S表示共享鎖定X表示獨佔鎖，0表示鎖定相容1表示鎖定衝突，相容不被阻塞，衝突被阻塞。由表可知一旦一個事務加了排他鎖，其他個事務加任何鎖都需要等待。多個共享鎖不會互相阻塞。

1.2 Record Locks、Gap Locks、Next-Key Locks

這三種類型的鎖都描述了鎖定的範圍，故放在一起說明。

以下定義摘自MySQL官方文件

  • 記錄鎖定（Record Locks）:記錄鎖定索引中一筆記錄。

  • 間隙鎖（Gap Locks）:間隙鎖定要麼鎖住索引記錄中間的值，要麼鎖住第一個索引記錄前面的值或最後一個索引記錄後面的值。

  • Next-Key Locks:Next-Key鎖定是索引記錄上的記錄鎖定和在索引記錄之前的間隙鎖定的組合。

#定義中都提到了索引記錄（index record）。為什麼？行鎖和索引有什麼關係呢？其實，InnoDB是透過搜尋或掃描表中索引來完成加鎖操作，InnoDB會為他遇到的每一個索引資料加上共用鎖定或排他鎖定。所以我們可以稱行級鎖（row-level locks）為索引記錄鎖（index-record locks），因為行級鎖定是加入到行對應的索引上的。

三種類型鎖的鎖定範圍不同，且逐漸擴大。我們來舉一個例子來簡要說明各種鎖的鎖定範圍，假設表t中索引列有3、5、8、9四個數字值，根據官方文檔的確定三種鎖的鎖定範圍如下：

  • 記錄鎖定的鎖定範圍是單獨的索引記錄，就是3、5、8、9這四行資料。

  • 間隙鎖定的鎖定為行中間隙，以集合表示為(-∞,3)、(3,5)、(5,8)、(8,9)、 (9,+∞)。

  • Next-Key鎖定是有索引記錄鎖定加上索引記錄鎖定之前的間隙鎖定組合而成，用集合的方式表示為(-∞,3]、(3, 5]、(5,8]、(8,9]、(9,+∞)。

最後對於間隙鎖定還需要補充三點：

  1. #間隙鎖定阻止其他交易對間隙資料的並發插入，這樣可有有效的解決幻讀問題(Phantom Problem)。 ##，MySQL InnoDB引擎只有在Repeatable Read（預設）隔離等級才使用間隙鎖定。會阻止其他事務擁有同樣的間隙鎖定。

    對於唯一索引的加鎖行為，間隙鎖定就會失效，此時只有記錄鎖定起作用
    2. 。語句

  2. 前面我們已經介紹了InnoDB的是在SQL語句的執行過程中透過掃描索引記錄的方式來實現加鎖行為的。下來我們逐一描述：

    3. select ... from語句：InnoDB引擎採用多版本並發控制（MVCC）的方式實作了非阻塞讀，所以對於普通的select讀語句， InnoDB並不會加鎖【註1】。 mode，透過字面意思我們可以猜到這是一條加鎖的讀語句，並且鎖類型為共享鎖（讀鎖）。唯一行，next-key降級為索引記錄鎖。 )。 where ...語句：。 【註2】

  3. delete ... where ...語句：。 InnoDB會對搜尋的所有索引記錄加上next-key鎖定，但如果掃描唯一索引的唯一行，next-key降級為索引記錄鎖定。

insert語句：InnoDB只會在將要插入的那一行上設定一個排他的索引記錄鎖定。

    最後補充兩點：

    • 如果一個查詢使用了輔助索引並且在索引記錄加上了排他鎖，InnoDB會在相對應的聚合索引記錄上加鎖。

    • 如果你的SQL語句無法使用​​索引，這樣MySQL必須掃描整個表以處理該語句，導致的結果就是表的每一行都會被鎖定，並且阻止其他用戶對該表的所​​有插入。

    • SQL語句驗證

  • 閒言少敘，接下來我們進入本文重點SQL語句驗證部分。

    1.測試環境

    資料庫：MySQL 5.6.35  
    交易隔離等級：Repeatable read  
    資料庫存取終端機：mysql client

    2.驗證場景

    2.1 場景一

    建表：

    CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    插入資料：

    INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

    首先我們執行SQL語句的範本：

    ##--#INSERT INTO `user` (`id`, `name`) VALUES (5#rollback;--6--rollback;
    步驟 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name='e' for update; --
    3 -- begin;
    410, #{name});

    取代步驟5中name的值，觀察結果：執行結果#a不阻塞b不阻塞d#e#f ##阻塞h#不阻塞
    #name的值



    i

    不阻塞 一共鎖住索引name中三行記錄，(c,e]區間應該是next-key鎖定而(e,h)區間是索引記錄e後面的間隙。 步驟client 1client 2
    #觀察結果，我們發現SQL語句 # SELECT * FROM user where name='e' for update
    ##1 begin; --
    2 SELECT * FROM user where name='e' for update; #- -
    3 -- SELECT * FROM user where name=#{name} for update;
    #5 rollback; --
    #6

    --rollback; 取代步驟5中name的值，觀察結果：d#不阻塞#e
    name的值#執行結果

    阻塞

    #f

      不阻塞

    • 因為間隙鎖定只會阻止insert語句，所以同樣的索引數據，

      insert
      • 語句阻塞而
    • select for update

      語句不阻塞的就是間隙鎖，如果兩個語句都阻塞就是索引記錄鎖。

      • 觀察執行結果可知，d和f為間隙鎖，e為索引記錄鎖定。

    • 結論：透過兩個SQL，我們確定了對於輔助索引name在查詢條件為

      where name='e'  
      • 時的加鎖範圍為（c,e],(e ,g),其中：

    對SQL語句掃描的索引記錄e加上索引記錄鎖定[e]。的間隙，c到e之間的資料(c,e)加了間隙鎖定

    前兩個構成了next-key鎖定(c,e]。值得注意的是還鎖定了e後面的間隙(e,g)。中沒有間隙的邊界資料c和g。 client 2##-- 2SELECT * FROM user where name='e' for update;##rollback; --6--rollback;

    取代步驟5中id，name的值，觀察結果：

    ##client 1
    1 begin;
    ##--
    #3 -- begin;
    4 -- INSERT INTO `user ` (`id`, `name`) VALUES (#{id}, #{name});
    #5
    阻塞-1-1阻斷11不阻塞22阻斷#3不阻塞阻斷阻塞5阻塞不阻塞#c#gc#10
    #id的值 name=c #執行結果 id的值 name=g 執行結果
    -- #-- -- -3 g 群組塞
    -- -- -- -2 g
    #c 不阻塞 g
    c 不阻塞 g
    c 不阻塞 g
    ##c 不阻塞 3 #g
    #4 c 4 g
    #5 c
    g
    6 c 阻斷 #6 g
    7 c 不阻塞 #7 g
    8 #8 阻塞
    8 不阻塞 9
    #不阻塞 9 g 不阻塞
    ##c

    阻塞

    10

    • g

      不阻塞

      • 11

    • c阻斷

      -
      • ##-

    -

    12c---透過觀察以上執行結果，我們發現，name等於c和e時insertid=5name=cname=cid=3的id聚合索引資料記錄之後的間隙(3,5)，(5,7)，(7,9)，( 9,∞)都被加上了鎖。 name=ename=e對應的id=7select * from user where id = x for update;語句判斷出以上間隙上加的鎖都為間隙鎖。
    語句的結果隨著id值得不同一會兒鎖定，一會兒不鎖定。那一定是id列加了鎖才會造成這樣的結果。 如果先不看這一行資料的結果，我們發現一個規律：
    當時，對應的
    當時，
    的id聚合索引資料記錄之前的間隙(5,7)，(3,5)，(1,3)，(-∞,1)都被加上了鎖。 我們可用

    接下來我們解釋一下id=5的鎖定情況#步驟client 11begin; SELECT * FROM user where name='e' for update;##--6取代步驟5中id的值，觀察結果：
    執行SQL語句的範本：
    client 2
    -- 2
    3 --SELECT * FROM user where id=#{id} for update;
    5rollback; --
    --rollback;
    #######id的值######執行結果# ####################3#############不阻塞############### ###4############不阻塞###################5############ 阻塞##################6#############不阻塞################# #7 ############不阻塞################

    通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。

    最终结论：  
    对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:

    1. 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。

    2. 对辅助索引对应的聚合索引加上索引记录锁。

    3. 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。

    上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。

    2.2 场景二

    建表：

    CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    注意与场景一表user不同的是name列为唯一索引。

    插入数据：

    INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

    首先我们执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name='e' for update;
    3 -- begin;
    4 -- INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
    5 rollback; --
    6 -- rollback;

    替换步骤5中name的值，观察结果：

    name的值 执行结果
    a 不阻塞
    b 不阻塞
    c 不阻塞
    d 不阻塞
    e 阻塞
    f 不阻塞
    g 不阻塞
    h 不阻塞
    i 不阻塞

    由测试结果可知，只有name='e'这行数据被锁定。

    通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，

    2.3 场景三

    场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。

    借用场景一的表和数据。

    建表：

    CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    插入数据：

    INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

    执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- begin;
    4 -- INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name});
    5 rollback; --
    6 -- rollback;

    替换步骤5中name的值，观察结果：

    name的值 执行结果
    a 阻塞
    b 阻塞
    c 阻塞
    d 阻塞
    e 阻塞
    f 阻塞
    g 阻塞
    h 阻塞
    i 阻塞

    这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？

    我们执行以下的SQL语句执行计划：

     explain select * from user where name>'e' for update;

    执行结果：

    +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

    如果你的结果与上面不同先执行一下OPTIMIZE TABLE user;再执行以上语句。

    通过观察SQL语句的执行计划我们发现，语句使用了name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。

    接下来我们再制造一组数据。  
    建表：

    CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    插入数据：

    INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

    这张表和前表的区别是多了一列非索引列age。

    我们再执行一下同样的SQL语句执行计划：

     explain select * from user where name>'e' for update;

    执行结果：

    +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

    是不是和第一次执行结果不同了，rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。

    因为select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：

    1. 索引记录g在name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

    2. g和i对应id列中的7和9加索引记录锁。

    3. 当name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。

    接下来我们逐一测试：

    首先测试验证了next-key锁范围，执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- begin;
    4 -- INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
    5 rollback; --
    6 -- rollback;

    替换步骤5中name的值，观察结果：

    name的值 执行结果
    a 不阻塞
    b 不阻塞
    c 不阻塞
    d 不阻塞
    f 阻塞
    g 阻塞
    h 阻塞
    i 阻塞
    j 阻塞
    k 阻塞

    下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- SELECT * FROM user where name=#{name} for update;
    5 rollback; --
    6 -- rollback;

    替换步骤5中name的值，观察结果：

    name的值 执行结果
    e 不阻塞
    f 不阻塞
    g 阻塞
    h 不阻塞
    i 阻塞
    j 不阻塞

    接下来验证对id列加索引记录锁，执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- SELECT * FROM user where id=#{id} for update;
    5 rollback; --
    6 -- rollback;

    替换步骤5中id的值，观察结果：

    id的值 执行结果
    5 不阻塞
    6 不阻塞
    7 阻塞
    8 不阻塞
    9 阻塞
    10 不阻塞

    最后我们验证name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- begin;
    4 -- INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
    5 rollback; --
    6 -- rollback;

    替换步骤5中id的值，观察结果：

    id的值 执行结果
    -1 不阻塞
    1 不阻塞
    2 不阻塞
    3 不阻塞
    4 不阻塞
    5 不阻塞
    6 阻塞
    7 阻塞
    8 阻塞
    9 阻塞
    10 阻塞
    11 阻塞
    12 阻塞

    注意7和9是索引记录锁记录锁。

    观察上面的所有SQL语句执行结果，可以验证select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。

    2.4 场景四

    我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。

    建表：

    CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    插入数据：

    INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

    和场景三表唯一不同是name列为唯一索引。

    SQL语句select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

    我们通过SQL验证我们的结论，执行SQL语句的模板：

    步骤 client 1 client 2
    1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- begin;
    4 -- INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
    5 rollback; --
    6 -- rollback;

    取代步驟5中name的值，觀察結果：

    ##阻斷
    name的值 執行結果
    a 不阻塞
    b 不阻塞
    c 不阻塞
    d 不阻塞
    f # #阻塞
    g
    #h 阻斷
    #i
    #j
    k

    阻塞步驟client 1client 21begin;--#2SELECT * FROM user where name>'e' for update;--3#--SELECT * FROM user where name=#{name} for update;5rollback;#--
    下面驗證next-key鎖定哪部分是間隙鎖，哪部分是索引記錄鎖，執行SQL語句的範本：
    ##6

    --rollback;取代步驟5中name的值，觀察結果：#ef##gh
    name的值執行結果
    ##不阻塞
    不阻塞
    阻塞
    #i
    #j

    ##步驟
    ##不阻塞
    透過上面兩個SQL語句的驗證結果，我們證明了我們的g和i的鎖定範圍趨勢為兩者next-key疊加組合。 接下來我們驗證輔助索引加鎖後對聚合索引的鎖定轉移，執行SQL語句的範本：
    client 1 client 2
    #1 begin; --
    2 SELECT * FROM user where name>'e' for update; --
    3 -- SELECT * FROM user where id=#{id} for update;
    5

    rollback;----#rollback; #取代步驟5中id的值，觀察結果：id的值56 78#9
    6
    執行結果
    不阻塞
    #不阻塞
    阻斷
    不阻塞

    阻塞

    10

    #不阻塞

    由結果可知對輔助索引中的g和i列對應的聚合索引列中的7和9加上了索引記錄鎖定。 到目前為止所有實驗結果和場景三完全一樣，這也很好理解，畢竟場景四和場景三隻是輔助索引name的索引類型不同，一個是唯一索引，一個是普通索引。 最後驗證意向，next-key鎖定邊界資料e，看看結論時候和場景三相同。 執行SQL語句的範本：步驟##1begin;--2SELECT * FROM user where name>'e' for update;--#345
    nameid
    client 1 client 2
    ##-- begin;
    -- INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
    rollback;#####--############ 6######--######rollback;############

    取代步驟5中id的值，觀察結果：

    1不阻塞#不阻塞#不阻塞不阻塞#阻塞#不阻塞##9阻斷#10不阻塞
    id的值 執行結果
    -1 不阻塞
    1
    #不阻塞
    2
    #3
    4
    5
    #6
    7
    8

    11

    #不阻塞

    12

    • 不阻塞

    • #注意7和9是索引記錄鎖記錄鎖。

      • 透過結果可知，當
    name

    列為索引記錄上邊界e時，並沒有對id有加鎖行為，這點與場景三不同。

    對於唯一索引的範圍查詢和普通索引的範圍查詢類似，唯一不同的是當輔助索引等於上下範圍的邊界值是不會對主鍵加上間隙鎖。

    唯一索引範圍查詢加鎖範圍：


    #對於掃描的輔助索引記錄的鎖定範圍就是多個索引記錄next -key範圍的疊加組合。


    對於聚合索引（主鍵）的鎖定範圍，會為多個輔助索引對應的聚合索引列加上索引記錄鎖定。

    結論

    InnoDB引擎會對他掃描過的索引記錄加上對應的鎖定，透過「場景一」我們已經明確地掃描一條普通索引記錄的鎖定範圍，透過「場景三」我們可以推論任意多個掃描普通索引索引記錄的鎖定範圍。透過「場景二」我們確定了掃描一筆唯一索引記錄（或主鍵）的鎖定範圍。透過「場景四」我們可以推斷任意多個掃描索唯一引記錄（或主鍵）的鎖定範圍。在實際的應用可以靈活使用，判斷兩個SQL語句是否會互相鎖定。這裡還需要注意的是對於索引的查詢條件，不能想當然的理解，他往往不是我們理解的樣子，需要結合執行計劃判斷索引最終掃描的記錄數，否則會對加鎖範圍理解產生偏差。


    備註######註1：當交易隔離等級為SERIALIZABLE時，普通的select語句也會對語句執行過程中掃描過的索引加上next-key鎖定。如果語句掃描的是唯一索引，那就將next-key鎖定降級為索引記錄鎖定了。  ###註2：當更新語句修改聚合索引（主鍵）記錄時，會對受影響的輔助索引執行隱性的加鎖操作。當插入新的輔助索引記錄之前執行重複檢查掃描時和當插入新的輔助索引記錄時，更新操作也會對受影響的輔助索引記錄新增共用鎖定。 ######相關推薦：############mysql執行sql檔報錯Error: Unknown storage engine'InnoDB如何解決############MySQL啟動時InnoDB引擎被停用了怎麼辦############MySQL儲存引擎MyISAM和InnoDB之間的比較###############

以上是MySQL資料庫InnoDB引擎行級鎖定範圍詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！

sql mysql for select Error auto delete 并发 table 数据库
陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
上一篇：MysqL安全策略分享下一篇：MysqL安全策略分享

相關文章

看更多