Knowledge

QiShunwang

知识分享

测试MySQL锁的问题

2021/6/24 15:07:09    来源:

测试MySQL锁的问题

目录
  • 测试MySQL锁的问题
    • 1 Record Lock
    • 2 Next-Key Lock
    • 2 死锁测试

InnoDB支持三种行锁:

  • Record Lock:单个行记录上面的锁

  • Gap Lock:间隙锁,锁定一个范围,但不会包含记录本身

  • Next-Key Lock:Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身。

    比如一个索引有10,11,13,20,那么该索引可能被Next-Key Locking锁住的区间为:

    (-∞,10], (10,11], (11, 13], (13, 20], (20, +∞]

1 Record Lock

InnoDB对于primary key(如果是多个列,且查询了所有的列,即点对点查询),会把间隙锁降级为Record Lock,比如下面的例子:

表的schema为:

create table t(
    a int PRIMARY KEY
);

insert into t select 1;
insert into t select 2;
insert into t select 5;

session A和session B的操作顺序如下:


/*session a*/
begin;
select * from t where a = 5 for update;

/*session b*/
begin;
/*不会阻塞,因为对于唯一索引,innodb采用的是record lock,即锁住单行*/
insert into t select 4;
commit;


/*session a*/
commit;

2 Next-Key Lock

如果是二级索引,因为不具备唯一性,为了防止幻读的产生,InnoDB会锁住相关的范围,比如下面的例子:

表的schema为:

/*测试Next-Key Lock的问题*/
create table z(
    a INT,  
    b INT,
    PRIMARY KEY(a), 
    KEY(b)
);

insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

session A和session B的操作顺序如下:


/*session a*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
/*按照书里面讲的,这个语句会锁住范围(1,3)(3,6),所以我们尝试下*/
select * from z where b = 3 for update;

/*session b*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
/*阻塞,因为session a中锁定了范围,所以这里也会被锁住*/
select * from z where a = 5 lock in share mode;
/*下面的两个语句也会被锁住*/
insert into z select 4,2;
insert into z select 6,5;

/*但是下面的语句就不会被锁住,因为不在锁定的范围里面*/
insert into z select 8,6;
insert into z select 2,0;
insert into z select 6,7;
commit;


/*session a*/
commit;

2 死锁测试

表的schema如下:

create table t(
    a int PRIMARY KEY
);

insert into t values(1),(2),(4),(5);

测试MySQL版本:8.0.20

  • 先来看最常见的AB-BA锁问题:

    时间线 session A session B
    1 begin;
    select * from t where a = 2 for update;```
    2 begin;
    select * from t where a = 4 for update;
    3 /*session a:尝试更新4*/
    update t set a = a + 100 where a = 4;
    4 /*session b:尝试更新2,直接发生死锁*/
    update t set a = a + 100 where a= 2;

session B的时间点4的时候,mysql直接报错,并对sessionB进行回滚操作:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  • 接下来是另外一种较为隐蔽的死锁;

    先看第一种情况

    时间线 session A session B
    1 begin;
    select * from t where a = 4 for update;
    2 /*因为sessionA中锁定了a=4这一行,所以这里会一直阻塞住,但是,不同的是,它成功的获取到了1,2的行锁,正在等待4的锁*/
    begin;
    select * from t where a <= 4 lock in share mode;
    3 /*发生死锁,虽然session b在等待,但是session b已经获取到了部分的gap lock,即[1,3),所以这里直接报死锁错误*/
    insert into t values(3);

    这里的情况稍微有些不同,sessionB虽然被阻塞了,但是它成功的获取到了a=1,a=22的行锁(Record Lock),此时正在等待a=4的行锁,如果在session A的时间点3的时候,我们允许插入成功,那么sessionA提交后,sessionB lock in share mode成功后,是不是应该在回过头来获取3的Record Lock?这样子是不合理的,所以Mysql这里就认定为死锁,直接回退了undo比较小的事务,那么问题来了,Mysql是怎么做到的呢?个人猜测可能是通过gap lock来实现的,只是在performance_schema.data_locks表中查不到而已,进一步的情况需要查看源代码。

    再来看第二种情况:

    时间线 session A session B
    1 begin;
    select * from t where a = 4 for update;
    select * from t where a = 2 for update;
    2 /*因为sessionA中锁定了a=2和a=4这两行,所以这里会一直阻塞住,但是,不同的是,它成功的获取到了1的行锁,正在等待2的锁*/
    begin;
    select * from t where a <= 4 lock in share mode;
    3 /*和第一种情况不同的是,这里直接成功了,并没有认定为死锁*/
    insert into t values(3);

    这里之所以没有问题,是因为session B在时间点2的时候,已经获取到了a=1的Record Lock,此时正在等待a=2的行锁,因为3>2,所以允许session A对a=3加上X锁,假设session A在时间点3后commit了,这个时候session B在时间点2的语句也成功了,查看performance_schema.data_locks可以看到,session B获取到了a=1, a=2, a=3, a=4的行锁。

    这是Mysql实现锁的一个细节之处,需要好好理解下。

文章引用:http://www.qishunwang.net/knowledge_show_193335.aspx