MySQL锁

本文最后更新于：2023年9月25日 下午

MySQL 锁

两阶段锁协议

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。目的是为了实现可串行化调度，而不需要提前知道完整的执行调度。

因此如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

例如对于购买影票的事务：

  1. 从顾客A账户余额中扣除电影票价；
  2. 给影院B的账户余额增加这张电影票价；
  3. 记录一条交易日志。

应当按照3、1、2的顺序，影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

幻读和间隙锁

幻读

可重复读隔离级别下，出现幻读的场景：

1
2
3
4
5
6
7
8
9
10
#表定义
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(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

幻读情景

其中，三个查询都是加了for update，都是当前读。Q3读到id=1这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

说明：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在可重复读隔离级别的“当前读”下才会出现。
  2. 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读的问题

  • 加锁语义被破坏：session A在T1时刻就声明了，“我要把所有d=5的行锁住，不准别的事务进行读写操作”，而这个语义实际上被破坏了。

  • binlog数据不一致：不加行锁的情景

    binlog数据不一致的情景

    1. 经过T1时刻，id=5这一行变成 (5,5,100)，当然这个结果最终是在T6时刻正式提交的;
    2. 经过T2时刻，id=0这一行变成(0,5,5);
    3. 经过T4时刻，表里面多了一行(1,5,5);

    binlog内容：

    1
    2
    3
    4
    5
    6
    7
    8
    #T2时刻，session B事务提交，写入了两条语句；
    update t set d=5 where id=0; /*(0,0,5)*/
    update t set c=5 where id=0; /*(0,5,5)*/
    #T4时刻，session C事务提交，写入了两条语句；
    insert into t values(1,1,5); /*(1,1,5)*/
    update t set c=5 where id=1; /*(1,5,5)*/
    #T6时刻，session A事务提交，写入了一条语句.
    update t set d=100 where d=5;/*所有d=5的行，d改成100*/

    这个语句序列，不论是拿到备库去执行，还是以后用binlog来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100)和(5,5,100)。也就是说，id=0和id=1这两行，发生了数据不一致。

    即便给表中所有行加上行锁，id=1这一行还是会数据不一致。

间隙锁

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

6个记录，产生了7个间隙

这样，执行select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁和区间右端的行锁合称next-key lock，每个next-key lock是前开后闭区间。表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]。

间隙锁造成的问题

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的，甚至造成死锁：

间隙锁造成死锁

图中session A和session B互相等待对方在id索引(5,10)上的间隙锁，形成死锁。

加锁规则总结

  1. 原则1：加锁的基本单位是next-key lock。next-key lock是分成间隙锁和行锁两段来执行的。
  2. 原则2：查找过程中访问到的对象才会加锁。
  3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。例如select * from t where id>10 and id<=15 for update会加上(15,20]这个next-key lock.

死锁分析方法

产生死锁的场景：

1
2
3
4
5
#session A
begin;
select id from t where c in(5,20,10) lock in share mode;
#session B，加锁顺序相反
select id from t where c in(5,20,10) order by c desc for update;

使用show engine innodb status命令，有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。

死锁日志

  1. 这个结果分成三部分：
    • (1) TRANSACTION，是第一个事务的信息；
    • (2) TRANSACTION，是第二个事务的信息；
    • WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
  2. 第一个事务的信息中：
    • WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
    • index c of table `test`.`t`，说明在等的是表t的索引c上面的锁；
    • lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
    • Record lock说明这是一个记录锁；
    • n_fields 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. 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
  2. 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句，来回滚。

锁等待分析方法

产生锁等待的场景：

锁等待场景

使用show engine innodb status命令，锁信息是在这个命令输出结果的TRANSACTIONS这一节

锁等待日志

  1. index PRIMARY of table `test`.`t` ，表示这个语句被锁住是因为表t主键上的某个锁。
  2. lock_mode X locks gap before rec insert intention waiting 这里有几个信息：
    • insert intention表示当前线程准备插入一个记录，这是一个插入意向锁。可以认为它就是这个插入动作本身。
    • gap before rec 表示这是一个间隙锁，而不是记录锁。接下来的0~4这5行的内容就是gap后的记录的信息。
  3. 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字节的回滚段信息。可以看到，这里的acs后面有显示内容(%和4)，这是因为刚好这个字节是可打印字符。
    • 后面两列是c和d的值，都是15。

因此，由于delete操作把id=10这一行删掉了，原来的两个间隙(5,10)、(10,15）变成了一个(5,15)，阻塞了对id=10的插入操作。

mysql
MySQL锁
https://njuu.top/1970/01/01/java/mysql锁/
作者
Wayne
发布于
1970年1月1日
许可协议