Notes

Exploring note

0%

用动态的观点看加锁

发表于 更新于 分类于
本文字数： 4.6k 阅读时长 ≈ 8 分钟
“锁”是在语句的执行过程中一步步的加上的

加锁规则：

  • 原则1： 加锁的基本单位是next-key lock。前开后闭区间。
  • 原则2： 查找过程中访问到的对象才会加锁。
  • 优化1： 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  • 优化2： 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  • 一个bug： 唯一索引上的范围查询会访问到不满足条件的第一个值为止。 
1
2
3
4
5
6
7
8
9
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 inro t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

1
2
begin;  
select * from t where id>9 and id<12 order by id desc for update;

这个语句的加锁范围是主键索引上的(0,5]、(5,10]、(10,15)。id=15这一行，并没有被加上锁。

加锁单位是next-key lock，都是前开后闭区间，但是这里用到了优化2，索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁(10,15)。

查询语句中的“等值查询”分析：加锁动作是发生在语句执行过程中的，分析加锁行为需要从索引上的数据结果开始。

  1. 首先这个查询语句是order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个id<12的值”。
  2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙。
  3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock(0,5]。

在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方式。

等值查询的过程

1
2
begin;  
select id from t where c in(5,20,10) lock in share mode;

in语句explain结果：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 simple t null range c c 5 null 3 100.00 Using where, Using index

这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的。

在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。

同样，执行c=10这个逻辑的时候，加锁的范围是(5,10]和(10,15)；执行c=20这个逻辑的时候，加锁的范围是(15,20]和(20,25)。

综上，语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁。

这些锁是“在执行过程中一个一个假的”，而不是一次性加上去的。 

1
select id from t where c in(5,20,10) order by c desc for update;

间隙锁是不互锁的，以上两条语句都会在索引c上的c=5、10、20这三行记录上加记录锁。

由于语句里的order by c desc，这三个记录锁的加锁顺序，是先锁c=20，然后c=10，最后是c=5。

以上两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，可能出现死锁。

关于死锁的信息，MySQL只保留了最后一个死锁的现场，不完备。

死锁(select)

出现死锁后，执行show engine innodb status命令得到部分输出。其中一节latest detected deadlock，记录的就是最后一次死锁信息。

  • lock_mode X waiting表示next-key lock；
  • lock_mode X locks rec but not gap只有行锁；
  • locks gap before rec只有间隙锁。
  1. 这个结果分成三个部分：
    • (1)transaction，是第一个事务的信息；
    • (2)transaction，是第二个事务的信息；
    • we roll back transaction(1)，是最终的处理结果，表示回滚了第一个事务。
  2. 第一个事务的信息中：
    • waiting for this lock to be granted，表示的是这事务在等待的锁信息；
    • record lock说明这是一个记录锁；
    • index c of table ‘test’.’t’，说明在等的是表t的索引c上面的锁；
    • lock mode S waiting表示这个语句要自己加一个读锁，当前的状态时等待中；
    • n_field 2表示这个记录是两列，也就是字段c和主键字段id；
    • 0:len 4; hex 0000000a; asc;;是第一个字段，也就是c。值是十六进制a，也就是10；
    • 1:len 4; hex 0000000a; asc;;是第二个字段，也就是主键id，值也是10；
    • 这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格。
    • 第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁。
    • 既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。
  3. 第二个事务显示的信息多一些：
    • ‘holds the lock(s)’用来显示这个事务持有哪些锁；
    • index c of table ‘test’.’t’表示锁是在表t的索引c上；
    • hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁；
    • waiting for this lock to be granted，表示在等(c=5,id=5)这个记录锁。

综上：

  1. ‘lock in share mode’这条语句，持有c=5的记录锁，在等c=10的锁；
  2. ‘for update’这个语句，持有c=20和c=10的记录锁，在等c=5的记录锁。
  1. 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
  2. 在发生死锁的时刻，for update语句占有的资源更多，回滚的成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句来回滚。

锁等待(delete&insert)

sessionA sessionB
begin;
select * from t where id>10 and id<=15 for update;
delete from t where id=10;
(Query OK)
insert into t values(10,10,10);(blocked)

由于sessionA并没有锁住c=10这个记录，所以sessionB删除c=10这一行是可以的，但是之后，sessionB再想insert id=10这一行回去就不行了。

根据id>10得到的第一个记录是id=15，所以不会加(5,10]这个next-key lock，又由于id是主键索引，只加了(10,15]这个next-key lock。

执行show engine innodb status命令，锁信息在transactions这一节：

  1. index primary of table ‘test’.’t’，表示这个语句被锁住是因为表t主键上的某个锁。
  2. lock_mode X locks gap before rec before rec insert intenting waiting里有几个信息：
    • insert intention表示当前线程准备插入一个记录，这是一个插入意向锁。可以理解为插入动作本身。
    • gap before rec表示这是一个间隙锁，而不是记录锁。
  3. 接下来的0~4这5行的内容是这个间隙的记录信息。
  4. n_fields 5也表示了，这一个记录有5列:
    • 0:len 4; hex 0000000f; asc ;;第一列是主键id字段，十六进制f就是id=15。这个间隙锁值id=15之前的，因为id=10已经不存在了，它表示的就是(5,15)。
    • 1:len 6; hex 000000000513; asc;;第二列是长度为6字节的事务id，表示最后修改这一行的是trx id为1299的事务。
    • 2:len 7; hex b0000001250134; asc %4;;第三列长度为7的字节的回滚段信息。可以看到，这里的asc后面有显示内容(%和4)，这是因为刚好这个字节是可打印字符。
    • 后面两列是c和d的值，都是15。

综上：
由于delete操作把id=10这一行删掉了，原来的两个间隙(5,10)、(10,15)变成了一个(5,15)。删除前间隙锁只有一个(10,15)，删除后间隙锁左侧扩张成了5，得到间隙锁(5,15)

  1. sessionA执行完select语句之后，什么都没做，但它加锁的范围突然变大了；
  2. 对比，当执行select * from t where c>=10 and c<=25 order by c desc lock in share mode;倒序向左扫描到c=5(非唯一索引，一直扫描到第一个不符合条件的值)的时候才会停止，要把(0,5]锁起来。所谓“间隙”，是由“这个间隙右边的那个记录”定义的。

update

sessionA sessionB
begin;
select c from t where c>5 lock in share mode;
update t set c=1 where c=5;
(Query OK)
update t set c=5 where c=1;
(blocked)

sessionA的加锁范围是索引C上的(5,10]、(10,15]、(15,20]、(20,25]、(25,supremum]。

根据c>5查到的第一个记录是c=10，因此不会加(0,5]这个next-key lock。

之后sessionB的第一个update语句，要把c=5改成c=1，分为两步：

  1. 插入(c=1,id=5)这个记录；
  2. 删除(c=5,id=5)这个记录。

索引c上(5,10)间隙是由这个间隙右边的记录c=10定义的(锁等待中的总结)。
执行第一个操作后，锁为(c=1,id=5)~(25,supremum]。

sessionB的第二个update语句，要把c=1改成c=5，分为两步：

  1. 插入(c=5,id=5)这个记录；
  2. 删除(c=1,id=5)这个记录。

第一步试图在已经加了间隙锁的(c=1,id=5)~(c=10,id=10)中插入数据，所以就被堵住了。

空表的间隙

一个空表只有一个间隙。 

1
2
3
begin;  
select * from t where id>1 for update;  
-- 这个查询语句加锁的范围是next-key lock(-∞,supremum]。
sessionA sessionB
create table t(id int primary key) engine=innodb;
begin;
select * from t where id>1;
insert into t values(2);
(blocked)
show engine innodb status;