精华内容
下载资源
问答
  • MySql-间隙锁和临键锁

    2021-02-02 06:11:29
    说明mysql的innodb引擎中有七种:S(Share Lock)共享,所有类似于select * from t where id = 1 lock in share mode或者update t set c = c+1 where id = 1的形式,行级;X(Exclusive Lock)排他,所有...

    说明

    mysql的innodb引擎中有七种锁:S锁(Share Lock)共享锁,所有类似于select * from t where id = 1 lock in share mode或者update t set c = c+1 where id = 1的形式,行级锁;

    X锁(Exclusive Lock)排他锁,所有select * from t where id = 1 for update的形式。行级锁;

    IS锁,意向共享锁,在获取S锁之前加的锁,表级锁;

    IX锁,意向排他锁,在获取X锁之前加的锁,表级锁;

    Record Lock记录锁,行级锁的真正实现;

    Gap Lock间隙锁,锁住一个范围;

    Next-key Lock临键锁,Gap Lock+Record Lock的组合;

    笔记记载的内容是在版本5.7.24和8.0.13验证通过,以后其他版本的MySql的加锁策略可能会变化。“临键锁”称呼来源于“架构之路”公众号。

    加锁策略

    加锁锁住的是什么?

    索引注意:MySql只有在RR的隔离级别下才有gap lock和next-key lock。原则1:加锁的基本单位是Next-key Lock(左开右闭区间);

    原则2:查找到的对象才会加锁;

    优化1:索引上的等值查询,给唯一索引加锁的时候,会退化为行锁;

    优化2:索引上的等值查询,向右遍历最后一个值不满足等值条件时,next-key lock会退化为间隙锁。

    一个不合理的地方:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

    案例分析

    建表语句:1

    2

    3

    4

    5

    6

    7

    8

    9

    10CREATE TABLE `t` (

    `id` int(11) NOT NULL,

    `c` int(11) DEFAULT NULL,

    `d` int(11) DEFAULT NULL,

    PRIMARY KEY (`id`),

    KEY `c` (`c`)

    ) ENGINE=InnoDB;

    insert into t values(0,0,0),(5,5,5),

    (10,10,10),(15,15,15),(20,20,20),(25,25,25);idcd000

    555

    101010

    151515

    202020

    252525

    案例一:唯一索引等值查询1

    2

    3

    4

    5

    6

    7

    8

    9--session A

    begin;

    update t set d = d + 1 where id = 7;

    --session B

    insert into t values (8,8,8);

    --session C

    insert t set d = d + 1 where id = 10;

    上面开启了三个会话分别执行了三个动作。

    分析:原则1:加锁区间为:(5,10];

    原则2:id=7的不存在,所以这一行没有锁;

    优化1:id=7的行没有锁,不满足优化1;

    优化2:id=7,与(5,10]的最后一个条件不相等,退化为gap lock。

    综上:所以session A加了gap lock(5,10),session B会阻塞,C会成功。

    案例二:非唯一索引等值查询1

    2

    3

    4

    5

    6

    7

    8

    9--session A

    begin;

    select id from t where c = 5 lock in share mode;

    --session B

    update t set d = d + 1 where id = 5;

    --session C

    insert into t values (7,7,7);

    分析:原则1:索引C加了next-key lock(0,5];

    原则2:c=5存在,所以索引c=5加锁;

    优化1:由于索引覆盖(不访问id索引就可以拿到id的值),虽然C=5索引加锁,但是id=5的索引实际并没有加锁,不满足优化1;

    优化2:非唯一索引需要向右继续查找,直到10,所以加上next-key lock(5,10],由于10不满足c=5的条件,所以next-key lock退化为gap lock(5,10)。

    所以session B的操作会成功,C会失败。

    如果想避开索引优化,可以使用select id from t where c = 5 for update,在加锁时会直接锁住主键索引,或者查询一些其他字段。

    案例三:唯一索引范围查询1

    2

    3

    4

    5

    6

    7

    8

    9

    10--session A

    begin;

    select * from t where id>=10 and id<11 for update;

    --session B

    insert into t values (8,8,8);

    insert into t values (13,13,13);

    --session C

    update t set d = d + 1 where id = 15;

    分析:原则1:next-key lock(5,10]和(10,15];

    原则2:查找过程中找到了id为10这一行,加了行锁;

    优化1:由于给唯一索引加锁,所以next-key lock(5,10]退化为id=10的行锁;

    优化2:不满足等值查询的条件;

    所以上面操作的结果是session B的第一个插入操作会成功,第二个插入操作会阻塞;

    session C的操作会阻塞。

    案例四:非唯一索引范围查询1

    2

    3

    4

    5

    6

    7

    8

    9--session a

    begin;

    select * from t where c >= 10 and c < 11 for update;

    --session b

    insert into t values (8,8,8);

    --session c

    update t set d = d + 1 where c = 15;

    分析:原则1:next-key lock(5,10]和(10,15];

    原则2:由于查找到10,所以对10加锁;

    优化1:由于不是唯一索引,因此(5,10]不会退化;

    优化2:由于不是等值查询,所以(10,15]不会退化;

    所以上面的操作是session b阻塞,session c也阻塞。

    案例五:唯一索引范围锁bug

    对应上面加锁策略中的不合理之处。1

    2

    3

    4

    5

    6

    7

    8

    9--session a

    begin;

    select * from t where id > 10 and id <= 15 for update;

    --session b

    update t set d = d + 1 where id = 20;

    --session c

    insert into t values (16,16,16);

    分析:原则1:范围查询next-key lock(10,15];

    原则2:搜索到id=15这一行,所以这一行数据要加锁;

    优化1:id=15满足等主键索引值查询,所以next-key lock(10,15]退化为行锁;

    优化2:满足等值查询,所以不会退化为gap lock;

    由于id是唯一索引,所以理论来说没必要再往后搜索,但是实际上还有一个next-key lock(15,20];

    所以session b会锁住,session c也会锁住。

    案例六:非唯一索引存在”相等值“

    首先插入一条数据:1mysql> insert into t values(30,10,30);

    数据库测试有两条c=10的数据了。即:idcd000

    555

    101010

    151515

    202020

    252525

    301030

    此时索引C的状态是:

    {5-5}-{10-10}-{10-30}-{15,15}-……

    执行SQL如下:1

    2

    3

    4

    5

    6

    7

    8

    9

    10--session a

    begin;

    -- delete的加锁规则和for update是一样的

    delete from t where c = 10;

    --session b

    insert into t values (12,12,12)

    --session c

    udpate t set d = d + 1 where c = 15;

    分析:next-key lock(5,10];

    c=10这一行加锁;

    非唯一索引,不满足退化条件;

    满足条件,向右查询第一个不等的值即c=15,加上next-key lock(10,15],由于15!=10,所以退化为gap lock(10,15);

    所以上述操作是session b阻塞,c成功。

    案例七:limit加锁1

    2

    3

    4

    5

    6--session a

    begin;

    delete from t where c = 10 limit 2;

    --session b

    insert into t values(12,12,12);

    分析:

    同案例六,但是在优化2的时候,由于limit 2的存在,所以在遍历到(30,10,30)这一行的时候就停止遍历了,所以session b的结果是成功。

    案例八:死锁1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13--session a

    begin;

    select id from t where c = 10 lock in share mode;

    --session b

    update t set d = d + 1 where c = 10;

    --session a

    insert into t values(8,8,8);

    --session b

    ERROR 1213(40001);

    Deadlock found and tring to get lock ; try restarting transaction;

    分析:

    session a:next-key lock(5,10];

    c = 10加锁;

    不满足优化1;

    满足优化2,加上next-key lock(10,15]并退化为gap lock(10,15)。

    session b:获取gap lock(5,10)成功,再获取c = 10的record lock时进入等待;

    session a:

    插入c = 8的行被死锁,a b进入互相等待。

    这个案例说明:next-key lock是由gap lock和record lock组成的,而且是分两部分获取的。

    案例九:排序对锁的影响1

    2

    3

    4

    5

    6--session a

    begin;

    select * from t where c >= 15 and c <= 20 order by c desc lock in share mode;

    --session b

    insert into values (6,6,6);

    分析:

    由于是order by c desc,所以遍历时第一个是c=20以及最右边的行:next-key(15,20](20,25],其中(20,25]会退化为(20,25)gap lock;

    c=15,c=20,c=10加锁,同时id=10,15,20也会枷锁;

    由于不是唯一索引,所以不满足优化1;

    在索引c上向左遍历,直到c=10才会停止(因为15的左边是10,为了保证10的安全还会对(5,10]加锁),所以next-key lock还包括(5,10]。

    案例十:多索引等值查询

    首先创建表:1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14CREATE TABLE z (

    id INT PRIMARY KEY AUTO_INCREMENT,

    b INT,

    KEY b(b)

    )

    ENGINE = InnoDB

    DEFAULT CHARSET = utf8;

    INSERT INTO z (id, b)

    VALUES (1, 2),

    (3, 4),

    (5, 6),

    (7, 8),

    (9, 10);

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15----session a

    BEGIN;

    SELECT *

    FROM z

    WHERE b = 6 FOR UPDATE;

    --session b

    INSERT INTO z VALUES (2, 4);

    INSERT INTO z VALUES (2, 8);

    INSERT INTO z VALUES (4, 4);

    INSERT INTO z VALUES (4, 8);

    INSERT INTO z VALUES (8, 4);

    INSERT INTO z VALUES (8, 8);

    INSERT INTO z VALUES (0, 4);

    INSERT INTO z VALUES (-1, 4);

    分析:next-key lock(4,6];

    b=6这一行加锁;

    不满足;

    满足,加gap lock(6,8);

    需要牢记的是:这个锁是加在b索引上的,而索引都是有序的,所以我们很轻易得到下图:

    b82e0ea8e284cf78c402e93ef2ec1845.png

    所以session b的结果是:1

    2

    3

    4

    5

    6

    7

    8INSERT INTO z VALUES (2, 4);--success

    INSERT INTO z VALUES (2, 8);--blocked

    INSERT INTO z VALUES (4, 4);--blocked

    INSERT INTO z VALUES (4, 8);--blocked

    INSERT INTO z VALUES (8, 4);--blocked

    INSERT INTO z VALUES (8, 8);--success

    INSERT INTO z VALUES (0, 4);--自增主键如果为0会自动替换为表里最大id的下一位,所以这里实际是(10,4),blocked

    INSERT INTO z VALUES (-1, 4);--success

    参考

    架构师之路:

    案例十详解参见其他博客:

    展开全文
  • next-key lock = 间隙锁 + 行锁 原则1:加锁的基本单位是next-key lock。next-key lock是前开后闭区间。 原则2:查找过程中访问到的对象才会加锁。 原则3:索引上的等值查询,给唯一索引加锁的时候,next-key ...

    环境

    MySQL version 5.6.47

    隔离级别:可重复读(RR)

     

    加锁规则

    可重复读隔离级别下加锁规则

    next-key lock = 间隙锁 + 行锁

     

    原则1:加锁的基本单位是next-key lock。next-key lock是前开后闭区间。

    原则2:查找过程中访问到的对象才会加锁。

    原则3:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。

    原则4:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。

    原则5:唯一索引上的范围查询会访问到不满足条件的第一个值为止。【注释:其实我不认为这是bug,只是唯一索引和非唯一索引保持一致】

     

    知识点

    锁是加在索引上的而不是加在数据上

    锁是加在索引上的而不是加在数据上

     

    lock in share modefor update的区别

    区别一:

    lock in share mode加的是读锁

    for update 加的是写锁

     

    区别二:

    在非主键索引上通过两种方式加锁是有区别的。

    lock in share mode 只锁覆盖索引,也就说:lock in share mode只锁非主键索引对应的B+树中的索引内容。

    for update:如果对非主键索引使用 for update加锁就不一样了。 执行 for update 时,mysql会认为你接下来要更新数据,因此会通过非主键索引中的主键值继续在主键索引对应的b+数上查找到对应的主键索引项进行加锁,也就是说:for update的加锁内容是非主键索引树上符合条件的索引项,以及这些索引项对应的主键索引树上相应的索引项。在两个索引上都加了锁。

     

    案例数据

    CREATE TABLE `t` (
    
      `id` int(11) NOT NULL,
    
      `c` int(11) DEFAULT NULL,
    
      `d` int(11) DEFAULT NULL,
    
      PRIMARY KEY (`id`),
    
      KEY `c` (`c`)
    
    ) ENGINE=InnoDB;
    
    
    
    insert into t values (5,5,5),
    
    (10,10,10),(15,15,15),(20,20,20),(25,25,25);

     

    id(主键)

    c(普通索引)

    d(无索引)

    5

    5

    5

    10

    10

    10

    15

    15

    15

    20

    20

    20

    25

    25

    25

    以上数据为了解决幻读问题,更新的时候不只是对上述的五条数据增加行锁,还对于中间的取值范围增加了6个临键锁,(-∞,5](5,10](10,15](15,20](20,25](25,+supernum] (其中supernum是数据库维护的最大的值。为了保证临键锁都是左开右闭原则。)

     

    案例一:间隙锁简单案例

    步骤

    事务A

    事务B

    1

    begin;

    select * from t where id = 11 for update;

    -

    2

    -

    insert into t value(10,10,10); //Duplicate entry '10' for key 'PRIMARY’

    insert into t value(12,12,12) //blocked

    insert into t value(15,15,15) // Duplicate entry '15' for key 'PRIMARY'

    3

    commit;

    -

    当有如下事务A和事务B时,事务A会对数据库表增加(10,15]这个区间锁,经过原则4,退化为间隙锁(10,15)。

    这时insert id = 12 的数据的时候就会因为区间锁(10,15)而被锁住无法执行。

    这时insert id = 10 和  insert id = 15的数据是未被锁定,报错:Duplicate entry '15' for key 'PRIMARY'

     

    案例二: 间隙锁死锁问题

    步骤

    事务A

    事务B

    1

    begin;

    select * from t where id = 9 for update;

    -

    2

    -

    begin;

    select * from t where id = 6 for update;

    3

    -

    insert into t value(7,7,7) //blocked

     

    4

    insert into t value(7,7,7) //Deadlock found when trying to get lock; try restarting transaction

     

    -

    不同于写锁相互之间是互斥的原则,间隙锁之间不是互斥的。

    如果一个事务A获取到了(5,10)之间的间隙锁,另一个事务B也可以获取到(5,10)之间的间隙锁。这时就可能会发生死锁问题。

     

    如下案例:
    事务A获取到(5,10)之间的间隙锁不允许其他的DDL操作,在事务提交,间隙锁释放之前,事务B也获取到了间隙锁(5,10),这时两个事务就处于死锁状态

     

    案例三: 等值查询唯一索引

    步骤

    事务A

    事务B

    1

    begin;

    update t set d= d+ 1 where id = 7;

    -

    2

    -

    insert into u (8,8,8); //blocked

    update  t set d = d+ 1 where id = 10; //ok

    insert into t value(10,10,10); //Duplicate entry '10' for key 'PRIMARY'

    4

    -

    -

    1.加锁的范围是(5,10]的临建锁
    2.由于数据是等值查询,并且表中最后数据id = 10 不满足id= 7的查询要求,故id=10 的行级锁退化为间隙锁,(5,10)

     

    执行说明:
    1.事务B中id=8会被锁住,而insert id=10 和 update id=10的时候不都会被锁住。

     

    案例四: 等值查询普通索引

    步骤

    事务A

    事务B

    1

    begin;

    select id from t where c = 5 lock in share mode;

    -

    2

    -

    update t set d = d + 1 where id = 5;

    insert into t value(7,7,7); //blocked

    insert into t value (4,4,4); //blocked

    insert into t value(10,10,10); //Duplicate entry '10' for key 'PRIMARY'

    4

    -

    -

    1.初步加锁,加锁的范围是(0,5],(5,10]的临建锁。
    2.由于c是普通索引,根据原则4,搜索到5后继续向后遍历直到搜索到10才放弃,故加锁范围为(5,10]
    3.根据原则4,由于查询是等值查询,并且最后一个值不满足查询要求,故间隙锁退化为(5,10)

    4.最终的加锁范围是:(0,10)

     

    执行说明:
    1.因为加锁是对普通索引c加锁,而且因为索引覆盖,没有对主键进行加锁,所以事务B执行正常。
    2.因为加锁范围(0,10),故insert id=4 和 update id=7执行阻塞。
    3.需要注意的是,lock in share mode 因为覆盖索引故没有锁主键索引,如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住。

     

    案例五: 范围查询唯一索引

    步骤

    事务A

    事务B

    1

    begin;

    select * from t where id >= 10 and id <11 for update

    -

    2

    -

    update t set d = d+ 1 where id = 15; //blocked

    insert into t value(8,8,8); //ok

    insert into t value(10,10,10); //blocked

    insert into t value(13,13,13); //blocked

    insert into t value (15,15,15); //blocked

    insert into t value (16,16,16); //ok

    4

    -

    -

    1. next-key lock 增加范围锁(5,10]
    2. 根据原则5,唯一索引的范围查询会到第一个不符合的值位置,故增加(10,15]
    3. 因为等值查询有id =10,根据原则3间隙锁升级为行锁,故剩余锁[10,15]
    4. 因为查询并不是等值查询,故[10,15]不会退化成[10,15)
    5. 最终的加锁范围是:[10,15]

     

    执行说明:
    1.insert id=8 和 update id=16,执行OK。

    2.update id=15、insert id=10 和 insert id=13 和insert id=15, 执行阻塞。

     

    案例六: 范围查询普通索引

    步骤

    事务A

    事务B

    1

    begin;

    select * from t where c >= 10 and c <11 for update

    -

    2

    -

    update t set d = d+ 1 where c = 15;  //blocked

    insert into t value(8,8,8); //blocked

    insert into t value(10,10,10); //blocked

    insert into t value(13,13,13); //blocked

    insert into t value (15,15,15); //Duplicate entry '15' for key 'PRIMARY'

    insert into t value (16,16,16); //ok

    4

    -

    -

    1. next-key lock 增加范围锁(5,10],(10,15]
    2. 因为c是非唯一索引,故(5,10]不会退化为10
    3. 因为查询并不是等值查询,故[10,15]不会退化成[10,15)

     

    执行说明:
    1.insert id=15 ,Duplicate entry '15' for key 'PRIMARY’。[存疑之处]

    2.update id=16,执行OK。

    3.update id=15、insert id=8 和 insert id=10 和 insert id=13,执行阻塞。

     

    案例七: 普通索引-等值问题

    上面的数据增加一行(30,10,30),这样在数据库中存在的c=10的就有两条记录

    步骤

    事务A

    事务B

    1

    begin;

    delete from t where c = 10

    -

    2

    -

    update t set d = d+ 1 where c = 15; //ok

    insert into t value(12,12,12) //blocked

    insert into t value(5,5,5) // Duplicate entry '5' for key 'PRIMARY'

    insert into t value(6,6,6) //blocked

     

    4

    -

    -

    1. next-key lock 增加范围锁(5,10],(10,15]
    2. 因为是等值查询故退化为(5,10],(10,15)

    执行说明:

    1. update c=15 成功,insert id = 12阻塞。

    加锁的范围如下图

     

    案例八: 普通索引-等值Limit问题

    步骤

    事务A

    事务B

    1

    begin;

    delete from t where c = 10 limit 2

    -

    2

    -

    update t set d = d+ 1 where c = 15; //ok

    insert into t value(12,12,12) //ok

    insert into t value(5,5,5) // Duplicate entry '5' for key 'PRIMARY'

    insert into t value(6,6,6) //blocked

    4

    -

    -

    1.根据上面案例8改造,将delete增加limit操作2的操作
    2.因为知道了数据加锁值加2条,故在加锁(5,10]之后发现已经有两条数据,故后面不在向后匹配加锁。所以事务B执行成功,加锁范围如下

     

    大家结合上面的分析,尝试理解一下下面的分析吧。

    分析一条Sql的加锁情况,參见何登成博文

    • Sql: select * from t1 where id=10;
    • Sql: delete from t1 where id=10;
      在回答这个问题之前我们须要明白几个前提条件:
    • 前提一:id列是不是主键
    • 当前数据库引擎的隔离级别是什么
    • Id列不是主键。那么Id列上面有无索引
    • Id列上面假设有二阶索引,那么Id是否是Unique Key
    • 两个Sql的运行计划是什么?索引扫描?全表扫描?
      另外一个Sql即便通过分析结论会使用索引,但实际运行计划有非常多复杂的其它条件,即便”看上去“会走索引可是终于通过运行计划看却走了全表扫描。

    组合一:id列是主键。RC隔离级别,运行delete from t1 where id = 10;

    create table t1(
    
        id int(32) not null,
    
        name varchar(50) not null,
    
        primary key(id)
    
    );

    结论:假设id列是主键,这样的情况仅仅须要在id=10的列上加上X锁。

    组合二:idUnique_key , RC隔离级别,运行delete from t1 where id=10;

    create table t1(
    
        id int(32) not null,
    
        name varchar(50) not null,
    
        primary key (`name`),
    
        unique key `key_name`(`name`)
    
    );

    运行图(何登成博客获取):

    这里写图片描写叙述


    这样的组合以下id是二阶段索引,这样的情况下和组合一加锁不同,DB引擎先走where条件的Id索引,在相应Id索引上id=10的记录上加X锁,然后依据name值回到聚簇索引上面,并对name=d的值加X锁。为什么聚簇索引上面也须要加X锁,假设不加X锁在delete运行的同事假设一个update t1 set id=100 where name=’d’;就会有冲突。
    结论:假设id是唯一索引。name为主键,那么会在id索引上面id=10的记录上加X锁。而且name聚簇索引上name=’d’的记录上加X锁。

    组合三:id为非唯一索引,RC隔离级别delete from t1 where id=10;

    create table t1(
        id int(32) not null ,
        name varchar(50) not null ,
        primary key (`name`),
        key `key_name`(`name`)
    );

    运行图(何登成博客获取):

    这里写图片描写叙述


    从图可知,在where条件匹配到的id=10的全部记录均会加上X锁,而且相应到索引上的记录也都会加锁。


    结论:若id列上有非唯一索引,那么相应的全部满足SQL查询条件的记录。都会被加锁。

    同一时候,这些记录在主键索引上的记录,也会被加锁。

     

    组合四:id列上无索引。RC隔离级别delete from t1 where id=10;

    create table t1(
    
        id int(32) not null ,
    
        name varchar(50) not null,
    
        primary key (`name`),
    
    );

    运行图(何登成博客获取):

    这里写图片描写叙述


    结论:mysql在走where条件的时候因为无法高速通过索引确认影响行,因此会对全部聚簇索引的记录行加上X锁然后返回全部记录。在详细实现时Mysql做了优化,再次通过where条件推断,对于不满足的记录通过unlock_row将X锁进行释放(违背了2PL规范);

    组合五:id为主键列。RR隔离级别

    这样的情况下加锁机制同组合一一致。

    组合六:id列为唯一索引,RR隔离级别

    这样的情况下加锁同组合二一致

    组合七:id列为非唯一索引,RR隔离级别 GAP锁)

    因为Mysql事务离别为RC的情况下是同意幻读的,可是隔离级别在RR的情况下是不运行幻读。Mysql是怎样做到RR隔离级别不产生幻读?这个组合中会加以说明。
    运行图(何登成博客获取):

    这里写图片描写叙述


    这里的加锁机制和RC以下的加锁机制相似,唯一差别的是就是RC的加锁情况下添加了一个GAP锁,而且GAP锁不是加到详细的记录上的。而是载入到记录与记录之间的一把锁。
    先说说幻读:幻读的意思是说当连续两次运行一个select * from t1 where id=10 for update Sql的时候,前后两次读取的记录数不一致(第二次不会返回比第一次很多其它的记录数)。
    RR隔离级别下,因为B+树索引是有序的。那么须要保证的是在id=[6,10)之间不能插入id=10的记录。详细就是在[6,c]与[10,b]之间插入相似[10,aa]或者在[10,b]与[10,d]之间插入[10,c]时都须要有一把锁来使得这些插入不能运行(即:GAP锁)。

    GAP锁之所以在组合五和组合六中不会出现的原因是因为上面两种组合保证了记录的唯一性,也就没有必要使用GAP锁。

    结论:Repeatable Read隔离级别下,id列上有一个非唯一索引,相应SQL:delete from t1 where id = 10; 首先。通过id索引定位到第一条满足查询条件的记录。加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回。然后读取下一条,反复进行。直至进行到第一条不满足条件的记录[11,f],此时,不须要加记录X锁,可是仍旧须要加GAP锁,最后返回结束。

    组合八:id上无索引。RR事务隔离级别

    加锁情况(何登成博客获取):

    这里写图片描写叙述


    结论:加锁机制和RC隔离级别下相似。差别是同事为每一个记录之间添加了一个GAP锁。不论什么更新/改动/插入等涉及到加锁的Sql语句都无法运行。

    欣喜的是同组合四相似,Mysql会提前过滤where条件为不满足条件的提前释放锁。

    组合九:Serializable

    Serializable情况下。delete from t1 where id=10 通RR情况下一样会通过Gap锁解决掉幻读情况。

    Serializable影响的是在select * from t1 where id=10 ,这条Sql在RR 和 RC以下都是快照度不加锁。可是在Serializable情况下会加锁。

    一条复杂Sql的分析

    Sql用比例如以下(何登成博客获取):

    这里写图片描写叙述

    在分析出SQL where条件的构成之后,再来看看这条SQL的加锁情况 (RR隔离级别),例如以下图所看到的:

    这里写图片描写叙述


    从图中能够看出,在Repeatable Read隔离级别下,由Index Key所确定的范围,被加上了GAP锁;Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤。视MySQL的版本号而定。在MySQL 5.6版本号之前,不支持Index Condition Pushdown(ICP),因此Index Filter在MySQL Server层过滤。在5.6后支持了Index Condition Pushdown。则在index上过滤。

    若不支持ICP,不满足Index Filter的记录。也须要加上记录X锁。若支持ICP,则不满足Index Filter的记录,无需加记录X锁 (图中。用红色箭头标出的X锁,是否要加,视是否支持ICP而定)。而Table Filter相应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤。因此聚簇索引上也须要X锁。

    最后。选取出了一条满足条件的记录[8,hdc,d,5,good],可是加锁的数量。要远远大于满足条件的记录数量。

    结论:在Repeatable Read隔离级别下,针对一个复杂的SQL,首先须要提取其where条件。Index Key确定的范围,须要加上GAP锁;Index Filter过滤条件,视MySQL版本号是否支持ICP。若支持ICP,则不满足Index Filter的记录。不加X锁,否则须要X锁;Table Filter过滤条件,不管是否满足,都须要加X锁。

    死锁的原理和分析

    死锁的情况1(何登成博客获取):

    这里写图片描写叙述


    死锁情况2(何登成博客获取):

    这里写图片描写叙述


    上面的两个死锁用例。第一个非常好理解。也是最常见的死锁,每一个事务运行两条SQL,分别持有了一把锁。然后加还有一把锁。产生死锁。

     

    第二个用例。尽管每一个Session都仅仅有一条语句,仍旧会产生死锁。要分析这个死锁,首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1。从name索引出发,读到的[hdc, 1],[hdc, 6]均满足条件,不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁。加锁顺序为先[1,hdc,100],后[6,hdc,10]。

    而Session 2,从pubtime索引出发。[10,6],[100,1]均满足过滤条件。相同也会加聚簇索引上的记录X锁。加锁顺序为[6,hdc,10]。后[1,hdc,100]。

    发现没有。跟Session 1的加锁顺序正好相反,假设两个Session恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。

     

     

     

    参考资料:

    http://hedengcheng.com/?p=771

    https://www.jianshu.com/p/32904ee07e56

    https://www.cnblogs.com/jzssuanfa/p/7394931.html

    对其中描述不清晰或者有问题的地方,根据自己的理解修改了一下。
    备注:
    转载请注明出处:https://blog.csdn.net/WSYW126/article/details/105324239
    作者:WSYW126

     

     
    展开全文
  • 行锁在 InnoDB 中是基于索引实现的,所以一旦某个加锁操作没有使用索引,那么该锁...需要注意的是:id 列必须为唯一索引列或主键列,否则上述语句加的锁就会变成临键锁。 同时查询语句必须为精准匹配(=),不能为...

     

    行锁在 InnoDB 中是基于索引实现的,所以一旦某个加锁操作没有使用索引,那么该锁就会退化为表锁

    记录锁(Record Locks)

    顾名思义,记录锁就是为某行记录加锁,它封锁该行的索引记录

    -- id 列为主键列或唯一索引列
    SELECT * FROM table WHERE id = 1 FOR UPDATE;
    复制代码

    id 为 1 的记录行会被锁住。

    需要注意的是:id 列必须为唯一索引列主键列,否则上述语句加的锁就会变成临键锁

    同时查询语句必须为精准匹配=),不能为 ><like等,否则也会退化成临键锁

    其他实现

    在通过 主键索引唯一索引 对数据行进行 UPDATE 操作时,也会对该行数据加记录锁

    -- id 列为主键列或唯一索引列
    UPDATE SET age = 50 WHERE id = 1;
    复制代码

    间隙锁(Gap Locks)

    间隙锁基于非唯一索引,它锁定一段范围内的索引记录间隙锁基于下面将会提到的Next-Key Locking 算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据

    SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
    复制代码

    即所有在(1,10)区间内的记录行都会被锁住,所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条记录行并不会被锁住。

    除了手动加锁外,在执行完某些 SQL 后,InnoDB 也会自动加间隙锁,这个我们在下面会提到。

    临键锁(Next-Key Locks)

    Next-Key 可以理解为一种特殊的间隙锁,也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁,当某个事务持有该数据行的临键锁时,会锁住一段左开右闭区间的数据。

    需要强调的一点是,InnoDB行级锁是基于索引实现的,临键锁只与非唯一索引列有关,在唯一索引列(包括主键列)上不存在临键锁

    假设有如下表:
    MySqlInnoDBRepeatable-Read:table(id PK, age KEY, name)

    idagename
    110Lee
    324Soraka
    532Zed
    745Talon

    该表中 age 列潜在的临键锁有:
    (-∞, 10],
    (10, 24],
    (24, 32],
    (32, 45],
    (45, +∞],

    事务 A 中执行如下命令:

    -- 根据非唯一索引列 UPDATE 某条记录
    UPDATE table SET name = Vladimir WHERE age = 24;
    -- 或根据非唯一索引列 锁住某条记录
    SELECT * FROM table WHERE age = 24 FOR UPDATE;
    复制代码

    不管执行了上述 SQL 中的哪一句,之后如果在事务 B 中执行以下命令,则该命令会被阻塞:

    INSERT INTO table VALUES(100, 26, 'Ezreal');
    复制代码

    很明显,事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时,也获取了 (24, 32] 这个区间内的临键锁。

    不仅如此,在执行以下 SQL 时,也会陷入阻塞等待:

    INSERT INTO table VALUES(100, 30, 'Ezreal');
    复制代码

    那最终我们就可以得知,在根据非唯一索引 对记录行进行 UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时,InnoDB 会获取该记录行的临键锁 ,并同时获取该记录行下一个区间的间隙锁

    事务 A在执行了上述的 SQL 后,最终被锁住的记录区间为 (10, 32)

    总结

    1. InnoDB 中的行锁的实现依赖于索引,一旦某个加锁操作没有使用到索引,那么该锁就会退化为表锁
    2. 记录锁存在于包括主键索引在内的唯一索引中,锁定单条索引记录。
    3. 间隙锁存在于非唯一索引中,锁定开区间范围内的一段间隔,它是基于临键锁实现的。
    4. 临键锁存在于非唯一索引中,该类型的每条记录的索引上都存在这种锁,它是一种特殊的间隙锁,锁定一段左开右闭的索引区间。


    作者:Gtaker
    链接:https://juejin.cn/post/6844903666420285454
    来源:掘金
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    展开全文
  • 本文主要针对InnoDB的行锁研究,避免陷入间隙锁而导致死锁等不利现象。

    行锁简介
    通过上一章可以了解到,InnoDB下行锁可细分为记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next_Key Lock),是基于索引实现的,其本质上是三种加锁算法。ps:若不声明,默认采用RR隔离级别。

    注:有些把记录锁理解为行锁,RL,GL,NKL属于同一级别,都是排他锁的一种。
    

    记录锁:锁精确加在某一行上。
    间隙锁:锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前(负无穷,first-key)或最后一个索引之后(last-key,正无穷)的无限空间。
    临键锁:记录锁与间隙锁组合起来用就叫做Next-Key Lock,就是将键及其两边的的间隙加锁(向左扫描扫到第一个比给定参数小的值, 向右扫描扫描到第一个比给定参数大的值, 然后以此为界,构建一个区间)。
    利用Next-Key Lock可以阻止其它事务在锁定区间内插入数据,因此在一定程度上解决了幻读问题。
    eg:构建表test(id pk,num key)。

    idnum
    33
    55
    77
    107
    149
    1610
    2013

    记录锁
    记录锁很好理解,一般要通过主键或唯一索引加锁,就可以较好的实现。

      select * from test where id=6 for update;//该句可以准确且只锁id=6这一行
    

    间隙锁
    间隙锁锁定的时一个开区间,而不是某个键,它是基于非唯一索引。需要注意的是用非唯一索引时,要explain下,确保sql走了索引(mysql查询优化器认为全表扫描比用索引更快时会锁表)。那么什么情况下出现间隙所呢?
    首先:InnoDB存储引擎,采用RR隔离级别,参数innodb_locks_unsafe_for_binlog=0(静态参数,默认为0,表示启动gap lock,如果设置为1,表示禁用gap lock,该参数最新版本已被弃用)。
    1:唯一索引/主键+范围查询
    锁住范围内的已存在的符合要求的行,还会加上范围内的gap,例子如下:

    select * from test where id between 6 and 16 for update;//该句锁id=7,10,15这三行,
    还会锁id=8,9,11,12,13,14这几行,  此时另一个事务是插不进去id=8,9,11,12,13,14的数据的。
    

    2:普通索引+绝对范围(不存在等于的情况)查询
    锁住范围内的已存在的符合要求的,还会加上范围内的gap,例子如下:

    事务A:
    select * from test where num>10 for update;//该句锁定范围(10,正无穷),不包括10。
    
    事务B:
    insert into test (id,num)  values(15,10)//成功
     update test set num=18 where id=16//成功 *
      insert into test (id,num)  values(17,10)//失败*
       insert into test (id,num)  values(17,9)//成功*
    

    3:普通索引+等值查询
    除了锁定值外,还会加上左右两侧的gap,例子如下:

    事务A:
    select * from test where num = 7 for update;//该句除了锁定(5,9)  
    
    事务B:
    insert into test (id,num)  values(4,5)//成功
     update test set num=18 where id=5//成功 *
      insert into test (id,num)  values(6,5)//失败*
       insert into test (id,num)  values(6,20)//成功*
        insert into test (id,num)  values(6,8)//失败          
    

    对于普通索引需要注意的是,锁范围的边缘值可以更新,但不允许在锁定范围内插入与边缘值相等的行。如例子中加*的sql。

    另外,对于2,如果是 select * from test where num>=9;就会锁定范围(7,正无穷),而不是[9,正无穷),但是select * from test where id>=16;就会锁定范围[16,正无穷),而不是(14,正无穷)。

    要想明白这个,首先要了解InoDB的索引,在InnoDB表就是一个索引组织表(IOT),他的主键就是它的聚族索引(InnoDB默认对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,该列的值会随着数据的插入自增),而普通索引作为非聚族索引,就会有一个普通索引值对多个主键值(如【7,7】,【10,7】),因此mysql对于二级索引和一级索引的间隙锁处理机制不同的,二级索引(辅助)允许插入相同的值,为了防止在锁定范围前插入边缘值,所以直接锁定范围外的第一个gap。

    临键锁 参考链接
    在默认情况下,mysql的事务隔离级别是RR,并且innodb_locks_unsafe_for_binlog=0,这时默认采用next-key locks.所谓Next-Key Lock,就是Record lock和gap lock的结合。

    分析
    下面我们针对大部分的SQL类型分析是如何加锁的,假设事务隔离级别为可重复读。
    select … from
    不加任何类型的锁

    select…from lock in share mode
    在扫描到的任何索引记录上加共享的(shared)next-key lock,对于主键/唯一索引加共享锁

    select…from for update
    在扫描到的任何索引记录上加排它的next-key lock,对于扫描到的主键/唯一索引加记录锁 ,对于不存在的加间隙锁

    update…where delete from…where
    在扫描到的任何索引记录上加next-key lock,对于扫描到的主键/唯一索引加记录锁 ,对于不存在的加间隙锁

    insert into…
    简单的insert会在insert的行对应的索引记录上加一个排它锁,这是一个record lock,并没有gap,所以并不会阻塞其他session在gap间隙里插入记录。不过若insert操作在间隙锁范围内,就会加另一种锁,官方文档称它为insertion intention gap lock,参考插入意向间隙锁解析,也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时,只要插入的记录不是gap间隙中的相同位置,则无需等待其他session就可完成,这样就使得insert操作无须加真正的gap lock
    这样的插入机制,会很大程度上提升并发性,如果一个表有一个唯一索引id,表中有记录1和8,那么当(1,8)不存在间隙锁,每个事务都可以在[2,7]之间插入任何记录,只会对当前插入的记录加record lock,并不会阻塞其他session插入与自己不同的记录,因为他们并没有任何冲突;当存在间隙锁,其他session就不能插入任何记录,直到间隙锁被释放,这样可以防止幻读。

    可以看到T2时会话2对(1,8)加间隙锁,所以T3会话1会获得插入意向锁,被阻塞,而T4时会话2可以正常插入,并对id=6加记录锁。

    行锁的兼容矩阵
    在这里插入图片描述
    可以看到,Gap与Gap是互相兼容的 ,请求IIGL与当前Gap是冲突的,参考文章

    展开全文
  • MySQL 中最基本的机制: 共享(S  – Shared Locks) 排它(X  – Exclusive Locks) 意向共享(IS  – Intention Shared ...
  • 数据库mysql 在可重复读、序列化隔离级别下使用的是间隙锁或者临键锁间隙锁+行锁) 加锁规则:(隔离级别可重复、序列化) 主键索引或者唯一索引查询具体的值(如a=“value”)且value值存在时,可只加行锁,...
  • 间隙锁和临键锁只有在RR级别中才能生效。set global transaction isolation level repeatable read;select @@global.tx_isolation;间隙锁的目的是为了防止多个事务把记录插入到同一范围中去,这样能防止幻读间隙锁...
  • 首先普及一下这三种锁的定义: 记录锁:指where的条件中使用索引作为条件,查出来的一条数据中的索引项会加锁,但是这里需要注意,虽然锁住的是...临键锁: 等于记录锁+间隙锁。左开右闭区间,它锁定的范围遵循:...
  • 有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章 一文详解MySQL的锁机制 这篇主要通过小案例来对记录锁、间隙(gap)锁、临键(next-key)锁做一个更好的理解。 ...
  • 在了解mysql中的各种之前你还需要了解这些一、加锁的目的是什么?在我们了解数据库之前,首先我们必须要明白加锁的目的是为了解决什么问题,如果你还不清楚的话,那么从现在起你应该知道,数据库的是为了解决...
  • MySQL间隙锁临键锁和死锁分析前言事务隔离级别回顾间隙锁(Gap Lock) 前言 在上一篇博客(事务隔离级别案例分析)中,重点说明了几种事务隔离级别下,针对出现脏读、幻读和不可重复读问题的产生过程和结果。 同时也在...
  • 一、MySQL数据库介绍 是数据库系统区别于文件系统的一个关键特性。机制用于管理对共享资源的并发访问,在MySQL数据库...SX都是行锁,兼容是指对同一记录(row)的兼容性情况。 X .
  • 行锁行锁在 InnoDB 中是基于索引实现的,但是如果某个加锁操作没有使用索引,那么该就会退化为表锁。行锁的分类记录(Record Locks)记录就是为某行记录加锁,它封锁该行的索引记录:SELECT * FROM table WHERE ...
  • 在编写一个领取码的时候,业务复杂度不高,所以直接做了个查询的排它 for update,同时研究了行锁相关的内容,因为经人提点说,,MySQL行锁只在主键唯一索引起效,根据个人经验,相信MySQL不会仅仅如此,故进行...
  • Next-Key Lock:临键锁,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,可以解决幻读的问题。 那么什么是幻读呢? 事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的...
  • 间隙锁是封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。 产生间隙锁的条件(RR事务隔离级别下;): 使用普通索引锁定; 使用多列唯一索引; 使用唯一索引锁定多行记录。...
  • MySQL的InnoDB的细粒度行锁,是它最吸引人的特性之一。但是,如《InnoDB,5项最佳实践》所述,如果查询没有命中索引,也将退化为表锁。InnoDB的细粒度,是实现在索引记录上的。一,InnoDB的索引InnoDB的索引有两类...
  • 间隙锁 间隙锁,如其名字,锁住的是索引值,如:delete from tableX where id between 2 and 10; 在Repeatable read, 的隔离级别,id为唯一主键的条件下,将锁住 2到10之间的间隙,如果其他事务需要插入主键是2...
  • 之前在验证MySQL临键锁的时候使用docker安装的最新版本的MySQL镜像,发现其临键锁在最新的MySQL的表现低版本(5.7)不一致,后面又自己验证了一下,并整理成博客 本文使用的高低版本MySQL分别为: 高版本MySQL: ...
  • 聊到mysql,好像就有了一个聊不完的话题。网上一搜感觉各种答案都有,各种方式的描述也有,种类繁多,描述繁琐,反正看来看去,基本最后看多少忘记多少。 mysql 分储存引擎来聊。myisam 表锁,innodb行锁、...
  • MySql 事务 ACID 原子性 一致性 隔离性 持久性 并发事务带来的问题 脏读 事务A读取到了事务B已经修改但尚未提交的数据 不可重复读 事务A内部的相同查询语句在不同时刻读出的结果不一致,不符合隔离性 幻...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,537
精华内容 614
关键字:

mysql间隙锁和临键锁

mysql 订阅