Mysql的加锁

  • 加锁的几个优化原则
  • 锁优化案例
  • 建表语句
  • 等值查询间隙锁
  • 非唯一索引等值锁
  • 主键索引范围锁


加锁的几个优化原则

两个原则，两个优化，一个bug

两个原则：

  • 加锁的基本单位是next-key lock,next-key lock是前开后闭区间
  • 查找过程中访问到的对象才会加锁

两个优化：

  • 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
  • 索引上的等值查询，向右遍历且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

一个Bug:

  • 唯一索引上的范围查询会访问到不满足条件的第一个值为止

锁优化案例

建表语句

mysql> 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;

mysql> INSERT INTO t VALUES(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

等值查询间隙锁

mysql函数加锁 mysql加锁规则_加锁

  1. 对上图进行分析： 如果按照正常的加行锁判断： session A 对id为7的行进行查找，这个时候会加上next-key lock (5,10]
  2. 由于对于mysql加锁规则的两个对等值查询的优化规则：索引上的等值查询，向右遍历到第一个值不满足条件时，next-key lock退化为间隙锁
  3. 由此next-key lock就退化成(5,10)的间隙锁，由此就可以解释session B里面的语句被锁住，session A里面的查询语句是OK的情况

非唯一索引等值锁

mysql函数加锁 mysql加锁规则_mysql_02

  1. 上面的情况按照加锁规则来的话应该是c = 5的这一行被加上(0,5]的next-key lock，然后是c = 10的这一行被扫描，10这一行不满足条件，所以(5,10]退化成(5,10)的间隙锁，可是这么搞的话最后加的锁应该是(0,10)呀，为什么session B里面id=5的这一行会查询成功呢？
  2. 这个就得提到我们上面说的两个原则之一了：查找过程中访问到的对象才会加锁，由于session A里面使用的是覆盖索引，返回值是主键id,不需要用到主键索引,所以并不会锁住主键索引上的行,因此session B上面能加锁成功，但是session c里面关联到了c=7这一行，所以在被锁范围内，会被锁住

注意点：在这个例子里面，lock in share mode只锁覆盖索引，但是如果是for uodate就不一样了，执行for update是，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁

主键索引范围锁

下面这两个语句的加锁有何不同？

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

看图说话：

mysql函数加锁 mysql加锁规则_mysql函数加锁_03

  1. 分析session A,首先要明白，加在id上的锁是加在主键索引上，主键索引是唯一索引
  2. session A首先找到id=10的这一行，由于是唯一索引上的等值查询，要满足优化规则之一：唯一索引上的等值查询，next-key lock退化成行锁，因此并不会加锁(5,10],而是加行锁10
  3. 然后向右遍历，找到id=15的行，这一行不满足条件，加上锁(10,15],由于这是范围查询，所以不会退化成间隙锁
  4. 由此我们可以总结出session A上面加了id=10这一行的锁和(10,15]的next-key lock

注意点： 首次session A定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断