MySQL锁之InnoDB锁实战

1 问题背景

前面研究了很多锁的概念性知识，并没有很客观形象地了解发生InnoDB锁时到底会有什么现象。今天使用sql实战操作一下，看看InnoDB锁会造成什么现象。

参考自：

1. InnoDB锁和事务模型之锁
2. MySQL行锁、表锁、间隙锁

2 前言

针对前面若干篇InnoDB官方文档所学的知识以及相关视频，笔者总结下关于InnoDB锁的知识。本篇博客仅供自己总结回顾以及帮助有需要的人，有不正确的地方请指出。

3 回顾

从前面InnoDB锁和事务模型之锁学习到InnoDB有很多锁，但是从类型上划分，无非就是3种锁：

• 行锁（锁住的是一行记录，也可以 笼统地认为是索引锁、记录锁）
• 表锁（锁住的是一整张表）
• 间隙锁（锁住的是一行记录以及记录前面的间隙，可以 笼统地认为锁住的是一段区间）

4 资源准备

MySQL8.0，创建一张表，结构如下所示：

CREATE TABLE `t_product` (
  `a` int(11) NOT NULL,
  `b` varchar(16) DEFAULT NULL,
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入若干条数据，代码如下所示，其中transactionlearn是数据库名：

INSERT INTO `trasactionlearn`.`t_product`(`a`, `b`) VALUES (1, 'b');
INSERT INTO `trasactionlearn`.`t_product`(`a`, `b`) VALUES (3, 'b');
INSERT INTO `trasactionlearn`.`t_product`(`a`, `b`) VALUES (5, 'b');
INSERT INTO `trasactionlearn`.`t_product`(`a`, `b`) VALUES (7, 'b');

5 行锁

5.1 产生行锁时的可重复读

关闭自动提交，开启两个命令窗口，一个窗口代表一个事务。一个事务进行更新记录，一个事务进行查询：

总结：如上图所示，左边窗口为事务A，右边窗口为事务B。InnoDB默认的隔离级别是Repeatable Read（简称RR，可重复读）。当事务A修改数据但未提交时，事务B第二次查询数据时，仍不会查到事务A改过的数据，只能查到事务A修改数据前的数据，这就叫做可重复读，解决了脏读的问题。只有当事务A提交后，事务B才能查到事务A修改的数据。

5.2 更新同一行记录时的锁竞争

关闭自动提交，开启两个命令窗口，一个窗口代表一个事务。一个窗口进行更新一行记录，另一个窗口也更新同一行记录：

总结：如上图所示，左边窗口为事务A，右边窗口为事务B。事务A更新a=1的记录，事务B也更新同一条记录。两个事务对这条记录产生锁竞争。事务A先执行，因此事务A先拿到a=1这行记录的锁。因为是更新操作，属于排他锁，只有当事务A执行完并提交，并释放该行记录的锁，事务B才能执行；否则事务B一直阻塞，等待获取锁或者等待超时。

超时的现象如下图所示：

总结：如上图所示，事务B等待获取该行锁超时，报错。这个报错也常会在生产环境的批量操作场景遇到。相对表级锁而言，行锁比表锁好，减少了因获取锁超时的问题。因此我们应该避免索引失效，让innodb执行行锁，而不是产生表锁，如果常遇到这种获取锁超时的报错，可以优化sql语句让sql走索引，走行锁，避免产生表锁。

6 表锁

关闭自动提交，开启两个命令窗口，一个窗口代表一个事务。一个事务更新a=1和a=3这两条记录，让索引失效；另一个事务更新a=5的记录：

总结：如上图所示，事务A使用了or条件导致索引失效，条件匹配的是a字段。事务B更新另外一条与事务A无关的记录。事务A没有提交。但是两条sql都执行成功了。这与笔者参考的视频中阐述得不一致，推断是MySQL版本不一致，在更高的版本中InnoDB优化了这种情况。

给表再添加一个新的非索引字段c。关闭自动提交，开启两个命令窗口，一个窗口代表一个事务。一个事务更新c=1和c=3这两条记录，让索引失效；另一个事务更新c=5的记录：

总结：事务A通过非索引字段并且使用or条件导致索引失效，事务B更新另一条与事务A无关的记录，被阻塞了。可以看到，如果索引失效，当条件匹配的字段不是索引字段，那么会产生表锁，在持有表锁的事务A未提交的情况下，事务B被阻塞。

7 间隙锁

关闭自动提交，开启两个命令窗口，一个窗口代表一个事务。事务A通过范围条件进行更新记录，事务B往这段范围中插入数据：

总结：如上图所示，事务A进行范围查询，事务B往该范围插入数据，产生了间隙锁，事务B被阻塞。