【图例】直观的感受MySQL中的锁，行锁、表锁、自增锁 到底是个啥？_数据库行级锁和表级锁

---

前言

一直以来对MySQL中的锁都有点懵懵懂懂，技术大佬一问MySQL有什么锁啊？我：呃，有行级锁，表级锁。。。！那么这些锁到底是个啥？答案到底对不对，下面带你来看看MySQL的锁到底有哪些？

在这之间建议花几分钟扫一眼这篇文章【图例】直观的感受MySQL事务的隔离级别分别解决了什么问题 会让你更有目的性的想弄清楚为什么会有加锁这一步。

提供的测试sql初始化数据

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 22);
INSERT INTO `student` VALUES (2, '李四', 23);
INSERT INTO `student` VALUES (3, '王五', 24);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

一、行级锁

行级锁（Row Lock）是锁定表中的单个行或一组行的机制，是一种非常细粒度的锁。

1.1 共享锁（S锁）【Share】

需要使用SELECT … LOCK IN SHARE MODE语句。共享锁允许其他事务读取锁定的数据，但不允许修改，直到锁被释放。

1. 选择合适的存储引擎（InnoDB）
2. 使用共享锁查询：在您的查询语句中添加FOR SHARE或LOCK IN SHARE MODE子句。
   例如，如果您想要对ID为1的学生记录加共享锁，可以使用以下查询：

-- 启动事务
START TRANSACTION;

-- 查询语句中添加FOR SHARE或LOCK IN SHARE MODE子句。例如，对ID为1的学生记录加共享锁
SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;

-- COMMIT or ROLLBACK
COMMIT;
1
2
3
4
5
6
7
8

共享锁主要用于实现并发读取，避免在读取数据时发生更新冲突。在使用共享锁时，应该小心避免死锁的情况，特别是在有多个事务同时操作相同数据的情况下。共享锁的使用可能会影响性能。

1.2 排他锁（X锁）

可以通过SELECT … FOR UPDATE语句来锁定学生表中的一行或多行 sql 如下：

START TRANSACTION;
-- 锁定id为1的学生记录
SELECT * FROM student WHERE id = 1 FOR UPDATE;
UPDATE student SET age = age + 1 WHERE id = 1;

-- 提交事务
COMMIT;
1
2
3
4
5
6
7

1. SELECT … FOR UPDATE会锁定查询结果集中的所有行，直到事务提交或回滚。

1.3 意向共享锁（IS锁）& 意向排他锁（IX锁）和 共享、排他锁的关系

当一个事务对某个表加上锁时，它会首先在该表上设置一个意向锁。
意向锁表示该事务打算对该表进行操作，但尚未开始操作。
其他事务可以同时获得该表的意向锁。

意向锁是一种表级锁，它在事务打算对表中的记录加共享锁之前先行获取。它表示该事务计划读取表中的记录，但尚未决定具体要读取哪些记录。

⬇️ 没关系、意向锁偏向概念理解 ⬇️。

意向锁存在意义就是避免锁冲突从而提高并发性，提升效率，如果没有意向锁，那么每个事务在对一行数据加锁时都需要检查该行是否已经被其他事务锁定，这会增加锁冲突的可能性。而有了意向锁后，事务只需要检查表级别的意向锁就能知道自己是否可以对该行加锁，从而减少了冲突的可能性。

共享锁和行级锁之间的关系如下表：

如：横坐标为X，纵坐标为X是 值是冲突，表明对同一结果集不能同时加上排他锁（X）。以此类推。
1、意向锁之间是不互斥的
2、排他锁（X）与任何锁都互斥
3、表添加了意向排他锁，就不能添加共享锁（S）

1.4 间隙锁（Gap锁）

应用于可重复读（REPEATABLE READ）和序列化（SERIALIZABLE）隔离级别

间隙锁是一种锁定索引记录之间的空隙的锁。
当一个事务执行范围查询时，它可以获取间隙锁来防止其他事务插入新的记录，从而影响查询结果。
间隙锁可以用于实现可重复读隔离级别，确保在事务期间读取的数据是一致的。

示例SQL语句：

SELECT * FROM students WHERE age BETWEEN 18 AND 25 FOR UPDATE;
1

间隙锁（Gap Lock）主要是在可重复读（RR）隔离级别下使用，**它锁定的是索引记录之间的间隙，而不是具体的数据行。**这意味着间隙锁可能锁定的是一个范围内所有可能插入新记录的位置，而不仅仅是已存在的数据行。间隙锁的作用是为了防止其他事务在间隙中插入新的记录，从而保证事务在读取时的一致性。

间隙锁通常在以下情况下生效：
当使用范围条件查询时，如使用BETWEEN或者>, <等操作符进行查询。
当查询涉及到唯一索引时，如果没有使用唯一索引进行精确匹配查询，而是使用了范围查询，那么InnoDB会使用间隙锁来防止其他事务在这个范围内插入新记录。

1.5 临键锁（Next-Key Lock）

临键锁是查询时InnoDB根据查询的条件而锁定的一个范围，
这个范围中包含有间隙锁和记录数；临键锁 = 间隙锁 + 记录锁。
其设计的目的是为了解决幻读；主要是阻塞insert，但由于临键锁中包含有记录锁，因此临键锁所锁定的范围内如果包含有记录，那么也会给这些记录添加记录锁，从而造成阻塞除insert之外的操作；
Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。事务的隔离级别为可重复读（Repeatable Read），临键锁才会生效。

根据查询的条件不同、列的类型不同（是否是索引等）触发的临键锁范围也不同

测试sql：

INSERT INTO `student` VALUES (10, '张三10', 22);
INSERT INTO `student` VALUES (20, '李四20', 23);
INSERT INTO `student` VALUES (30, '王五30', 24);
1
2
3

1、当条件字段为普通列时：临键锁中的间隙锁和记录数均为表级别

2、普通索引列：

设置age为索引列。

CREATE INDEX index_name ON student (age);
1

非临界值：间隙锁为被查询的记录所在的区间，记录锁不生效

临界值：间隙锁为被查询记录所在的相邻两个区间，记录数退化为行锁

范围值：间隙锁和记录数均为查询条件所涉及到的区间

3、唯一索引或主键索引列：
非临界值：间隙锁为被查询的记录所在的区间，记录锁不生效
临界值：间隙锁失效，记录锁退化为行锁

范围值：间隙锁和记录数均为查询条件所涉及到的区间

1.6 锁升级案例

1、共享锁 和 【共享锁升级】

大家看看这个例子，共享锁大家的共识就是，大家在加上共享锁最好是不要在当前事务修改数据，主打一个叛逆，咱试下。。。

！！！！！我只加了共享锁（S）为啥出来个排他锁（X）！！！！！

共享锁竟然会升级为排他锁：
自动升级的原因：当一个事务首次读取数据时，它会获取共享锁。但是，如果事务后续需要进行更新操作，为了保护数据的完整性并防止其他事务在此期间修改数据，事务必须将共享锁升级为排他锁。这是因为排他锁可以防止其他事务同时写入，而共享锁则无法提供这种保护。

上面这个例子中，两个事务都涉及到了锁升级，需要注意的是，如果多个事务同时尝试升级共享锁到排他锁，就可能发生死锁。如果两个事务都持有共享锁并同时等待对方释放锁以便升级，就会形成循环等待，导致死锁的发生。

事务一测试sql

START TRANSACTION;
SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;
DO SLEEP(5);
UPDATE student SET age = age + 1 WHERE id = 1;
SELECT * FROM student WHERE id = 1;
COMMIT;
1
2
3
4
5
6

事务二测试sql

START TRANSACTION;
SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;
DO SLEEP(10);
UPDATE student SET age = age + 2 WHERE id = 1;
SELECT * FROM student WHERE id = 1;
COMMIT;
1
2
3
4
5
6

查看状态sql、每次刷新数据可能都不一样，需要考虑时间两个事务设置了SLEEP观察，控制在这之间查询。

-- 获取事务信息 
SELECT * FROM information_schema.INNODB_TRX;

-- 获取当前的锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
1
2
3
4
5

2、排他锁（锁同一数据，会阻塞运行）

二、表级锁

表级锁（Table Lock）是锁定整个表的机制，而不是仅仅锁定表中的部分行。使用LOCK TABLES语句可以实现对一个或多个表的锁定，这通常用于执行批量更新或者全表操作

sql 如下：

-- 锁定学生表，使用写锁
LOCK TABLES student WRITE;

-- 可以进行一些全表的操作，如插入、删除、更新等。
UPDATE student SET age = age + 1;

-- 解锁学生表
UNLOCK TABLES;
1
2
3
4
5
6
7
8

请注意以下几点：

1. LOCK TABLES会影响当前线程后续的所有表操作，直到遇到UNLOCK TABLES。 当使用LOCK TABLES时，必须手动调用UNLOCK TABLES来释放锁，否则可能导致死锁。
2. LOCK TABLES会阻塞其他线程对这些表的访问，直到锁被释放。 在事务中使用LOCK TABLES可能会影响事务的行为，因为LOCK TABLES会隐式提交前一个活动的事务。 在现代MySQL版本中，由于InnoDB存储引擎提供了行级锁，并且支持事务，因此LOCK TABLES的使用变得较少。
   通常，只在特定的批处理操作或系统升级时才需要使用表级锁。

三、页级锁（不支持InnoDB）

使用锁定命令：可以使用LOCK TABLES命令来对学生表加锁。例如，如果想要对学生表加共享读锁，可以使用以下命令：

LOCK TABLES student READ;
1

执行操作：在表被锁定期间，您可以执行需要的读操作。其他事务可以同时读取表，但不能进行写操作。

释放锁：完成操作后，使用UNLOCK TABLES命令释放锁：

UNLOCK TABLES;
1

监控锁状态：如果您想要查看锁的状态，可以通过查询

SHOW STATUS LIKE 'table%';
1

来查看MyISAM存储引擎中的锁争夺情况。

四、自增锁：

自增锁是一种特殊类型的锁，用于保护自增长列的值。
当一个事务需要更新自增长列的值时，它需要先获取自增锁，以确保生成的值是唯一的。
自增锁通常在插入新记录时自动获取，并在事务提交或回滚时释放。

当向student表中插入新记录时，不需要为id字段指定值。MySQL会自动为该字段分配一个唯一的整数值。
例如，可以使用以下SQL语句插入一条新记录：

INSERT INTO student (name) VALUES ('John Doe');
1

这将在student表中插入一条新记录，id字段会自动分配一个自增值。

---

总结

InnoDB中的行级锁可理解为表象：

具体是共享（S）锁、排他（X）锁 去操作数据，
过程中又会有间隙锁（Gap锁） 和 临键锁（Next-Key Lock）

无需特殊指定、在mysql默认的隔离级别 -> 可重复读（Repeatable Read）就会生效。
间隙锁（Gap锁）：范围搜索时会触发，锁定结果集的间隙中不能进行插入操作。
临键锁（Next-Key Lock）：在条件搜索时会生效，具体规则跟条件字段有关，会触发行级锁和间隙锁避免幻读。

表级锁：粒度太大会导致阻塞，慎用。

自增锁：插入时会自动生成id，开发时都习以为然了。

---

在实际业务中，排他锁（Exclusive Lock）和共享锁（Shared Lock）通常在具有并发访问的数据库环境中使用。这些锁的使用可以确保数据的一致性和避免并发访问导致的问题。

举例，产生印象，但不一定只有一种解决办法噢！！！

1. 订单系统：在订单系统中，当用户在购物车中添加商品、结算并生成订单时，需要使用排他锁来避免并发下多个用户对同一个库存进行购买操作导致的库存不足问题。
2. 并发修改：在需要多个用户同时对同一条数据进行修改的情况下，可以使用排他锁实现数据的原子性访问，避免脏读和不可重复读。
3. 实时报表系统：在需要实时查询并生成报表的业务场景中，可以使用共享锁来避免数据在生成报表期间被修改，确保报表的准确性。
4. 数据备份：在进行数据库备份时，需要使用排他锁来确保备份期间数据不被修改，从而保证备份的一致性。
   在这些业务场景中，排他锁和共享锁可以确保数据的一致性和并发访问的正确性，从而保证系统的稳定性和可靠性。然而，需要根据具体业务的需求和数据库的性能调优来合理选择锁的类型和使用方式。