数据库13问，谁能顶得住

Ms.Wang大约 21 分钟

数据库13问，谁能顶得住 |

前言

过完这个节，就要进入金银季，准备了 13 道数据库锁面试题，一定用得上。

锁的问题归更到底就是资源竞争导致的，而并发场景一定会发生资源竞争，所以锁的问题，也是并发场景下必问的问题。理解好锁的原理，在编程中提升设计能力也非常重要。

作者：
感谢每一个支持： github

1. 为什么要在数据库中加锁

就像我们生活中，每天出门都要给房子上锁，这时有人来拜访看到已上锁，就知道家里没人。

我们的系统绝大多不是单机系统，都会从在资源的竞争（并发控制）和数据一致性的场景，比如：俩个人同时买一个商品、但是商品只剩下一件，这时就需要锁来保证谁拥有这件商品。锁是保证数据库事务四个特性，原子性、一致性、隔离性和持久性（ACID）的关键性技术之一。

无锁的情况下就可能出现，如脏读、不可重复读和幻读等。

2. InnoDB 中有哪些锁

2.1 共享锁/排它锁

共享锁（S锁）和排它锁（X锁）是最基础的锁类型，用于操作对数据的读取和写入。

通过名字，我们也可以看出这两个锁的作用。当事务要读取一条记录时，先获取该记录的S锁；当事务要改动一条记录时，先获取该记录的X锁。

通过这个图可以知道，什么情况下可以获得 S 锁和 X 锁。可以看到，只有都是共享锁时，才可以同时在一行记录加锁。

例子：

加共享锁，共享锁允许其他事务读取这些行，但不允许其他事务修改或删除这些行，直到当前事务结束。

SELECT ... LOCK IN SHARE MODE; 加共享锁
---
SELECT * FROM user WHERE id=1 LOCK IN SHARE MODE;

加排他锁，相当于是独占记录。当一个事务对某行数据加上排他锁后，其他事务既不能读取也不能修改这些数据，直到持有排他锁的事务结束。

SELECT ... FOR UPDATE; 加排他锁
---
START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR UPDATE;
-- 执行一些更新操作
UPDATE table_name SET column = value WHERE condition;
COMMIT;

2.2 行锁/临键锁

行锁

行锁通常在执行涉及单个行的操作时自动应用，例如 SELECT 语句中的 FOR UPDATE 子句，这会为查询结果中的每行添加排他锁。也就是上面说到的例子。

START TRANSACTION;
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
-- 在此事务中，id为1的行将被锁定，其他事务不能修改或读取这行
COMMIT;

临键锁

临键锁结合了记录锁和间隙锁，用于行级锁定和范围查询，防止幻读。我们一般无法判断是行锁还是临键锁，都是行锁的形式，由存储引擎在执行查询时自动管理决定的。

START TRANSACTION;
SELECT * FROM table_name WHERE id BETWEEN 1 AND 10 FOR UPDATE;
-- 这将锁定id在1到10之间的所有行，以及可能的下一个键值
COMMIT;

2.3 意向锁

意向锁是针对多粒度锁定协议的系统，比如行粒度锁、表粒度锁。当一个事务打算在某个细粒度上请求锁（共享锁|排他锁），它会首先在更粗力度上设置意向锁。

意向共识锁 - 简称 IS

意向排他锁 - 简称 IX

例子：如果一个事务要对一张表加排他锁，它会先在表级别加上意向排他锁，然后对表中的特定行加排他锁。

2.4 间隙锁/插入意向锁

间隙锁和插入意向锁针对实现了多版本并发控制（MVCC）的系统中，如 MySQL 的 InnoDB 存储引擎。

间隙锁（Gap Locks）：

间隙锁是一种行锁，它锁定一个范围内的间隙，但不锁定该范围内的任何具体行。间隙锁主要用于防止其他事务在这个间隙中插入新的行，从而维护数据的顺序性和一致性。间隙锁通常在执行范围查询并加上共享或排他锁时自动应用。例如，如果一个事务执行了 SELECT ... WHERE index_column BETWEEN x AND y LOCK IN SHARE MODE ，InnoDB 会在索引列 x 和 y 之间的间隙上设置间隙锁，防止其他事务在这个范围内插入新行。

示例：

START TRANSACTION;
SELECT * FROM table_name WHERE id BETWEEN 10 AND 20 LOCK IN SHARE MODE;
-- 在id为10到20的范围内设置间隙锁
COMMIT;

插入意向锁（Insert Intention Locks）：

插入意向锁是一种特殊的间隙锁，它表明一个事务有意向在某个间隙中插入新行。当一个事务想要在一个已经被其他事务加上间隙锁的范围内插入新行时，它会首先在该范围内设置一个插入意向锁。

插入意向锁允许多个事务保留在特定间隙中插入新行的意图，而不直接与间隙锁冲突。这样，当间隙锁被释放时，持有插入意向锁的事务可以继续执行插入操作。

示例：

START TRANSACTION;
-- 假设另一个事务已经在id为10到20的范围内设置了间隙锁
SELECT * FROM table_name WHERE id = 15 FOR UPDATE;
-- 这将设置一个插入意向锁，表明事务有意向在id为10到20的范围内插入新行
COMMIT;

2.5 自增锁

自增锁（Auto-Increment Locks，简称：AI Locks）是MySQL数据库中InnoDB存储引擎特有的一种锁机制，它与自增字段（AUTO_INCREMENT）相关联。AUTO_INCREMENT 你一定不陌生，我们在建表时多数情况都会让主键 id 自增来生成唯一序列。

当一个表中包含自增字段时，InnoDB 会使用自增锁来确保在并发环境下，自增字段生成的值是唯一的，并且连续的。

示例：

START TRANSACTION;
INSERT INTO table_name (auto_increment_column, other_columns) VALUES (NULL, 'value1');
-- InnoDB分配自增值并锁定它
COMMIT;
-- 自增锁在事务提交时释放

在这个示例中，auto_increment_column 是一个自增字段。当事务提交时，InnoDB 会分配一个新的自增值给插入的行，并在事务提交时释放自增锁。

2.6 外键锁

外键（Foreign Key）是一种数据库完整性约束，它用于维护两个表之间的链接，并确保引用的数据的完整性。

外键锁顾名思义就是针对外键的。外键锁并不是一个标准的锁类型，而是指与外键约束相关的锁定行为，这些行为确保在执行涉及外键的插入或更新操作时，数据库的完整性不被破坏。

外键锁这个术语并不是用来描述一种特定的锁类型，而是用来描述与外键约束相关的锁定行为。数据库系统会自动处理这些锁定，以确保数据的完整性和一致性。

2.7 表锁/页锁

表锁和页锁是两种不同粒度的锁，

表锁（Table Locks）

表锁是锁定整个表的锁，这意味着在锁定期间，没有其他事务可以对这张表进行读写操作。表锁通常用于批量操作，如全表扫描或全表更新，以及在不需要频繁锁定和解锁单个行的场景中。

特点：

粒度较大：表锁影响整个表的所有数据，因此粒度较大。
冲突较少：由于锁定了整个表，减少了锁冲突的可能性，但在高并发环境下可能导致其他事务长时间等待。
使用场景：适用于全表操作，如全表备份或全表删除。

示例：

LOCK TABLES table_name WRITE;
-- 在此期间，其他事务不能访问table_name
UNLOCK TABLES;

页锁（Page Locks）

页锁是锁定数据库中的一个“页”的锁。在许多数据库系统中，数据是按页存储的，每页包含一定数量的行。页锁允许多个事务同时访问不同的页，从而提供比表锁更细粒度的并发控制。

特点：

粒度较小：页锁锁定的是数据页，而不是整个表，因此粒度较小。
并发性更好：允许多个事务并发访问不同的数据页，提高了并发性能。
使用场景：适用于需要较高并发性能的场景，尤其是在大型表上进行部分数据的读写操作。

注意：页锁通常由数据库管理系统自动管理，不需要用户显式操作。例如，在InnoDB存储引擎中，虽然页锁不是用户可以直接控制的锁类型，但InnoDB会根据需要自动在页级别上应用锁。

总结：粒度：行锁 < 页锁 < 表锁（从细到粗）。

3. 什么是死锁

死锁是在事务数据库中会发生的一种特殊现象，多个事务在执行过程中，相互等待对方持有的资源，导致这些事务都无法继续执行。简单来说，就是两个或多个事务陷入了一个循环等待的状态，每个事务都在等待其他事务释放资源。

比如这个蛇和青蛙互不放手。

死锁通常由以下四种情况同时出现时引起：

互斥条件（Mutual Exclusion）：
- 指某些资源（如数据库中的记录）一次只能被一个事务占用。
持有和等待条件（Hold and Wait）：
- 指一个事务至少持有一个资源，并在等待获取其他事务持有的资源。
不可剥夺条件（No Preemption）：
- 指已经分配给一个事务的资源，在事务使用完毕之前，不能被强行剥夺。
循环等待条件（Circular Wait）：
- 指事务之间形成了一个闭环，每个事务都在等待下一个事务所持有的资源。

4. 行锁、表锁、页面锁有什么区别

从每种锁的锁粒度、并发性、冲突和使用场景几个角度来区分。

行锁（Row Locks）

锁粒度：行锁是锁定表中的单行或多行。
并发性：行锁允许多个事务同时访问表中的不同行，从而提供较高的并发性。
冲突：行锁可能导致更多的锁冲突，因为它们需要在更细的粒度上管理锁。
使用场景：适用于需要高并发读写操作的场景，尤其是在表中只有少数行会被访问时。
示例：在InnoDB存储引擎中，使用SELECT ... FOR UPDATE可以显式地对行加排他锁。

表锁（Table Locks）

锁粒度：表锁是锁定整个表。
并发性：表锁期间，其他事务不能对表进行任何读写操作，直到锁被释放，因此并发性较低。
冲突：由于锁定了整个表，减少了锁冲突的可能性，但可能导致其他事务长时间等待。
使用场景：适用于需要对整个表执行批量操作的场景，如全表备份或全表删除。
示例：在MySQL中，可以使用LOCK TABLES和UNLOCK TABLES语句显式地对表加锁和解锁。

页锁（Page Locks）

锁粒度：页锁是锁定数据库中的一个数据页，每个页包含多行数据。
并发性：页锁允许多个事务同时访问不同的数据页，提高了并发性。
冲突：相比于行锁，页锁可能导致较少的锁冲突，但仍然存在一定的冲突可能。
使用场景：适用于需要较高并发性能的场景，尤其是在大型表上进行部分数据的读写操作。
示例：页锁通常由数据库管理系统自动管理，不需要用户显式操作。例如，在PostgreSQL中，页锁可以用于控制对表中特定数据页的访问。

总结

粒度：行锁 < 页锁 < 表锁（从细到粗）。
并发性：行锁提供最高的并发性，页锁次之，表锁最低。
冲突：行锁可能导致最多的锁冲突，页锁和表锁的冲突较少。
使用场景：行锁适用于高并发读写，表锁适用于全表操作，页锁适用于需要较高并发的场景。

区分选择不同的锁，正确使用锁类型对于优化数据库性能、确保数据一致性至关重要。

5. 乐观锁和悲观锁有什么区别，它们什么场景会用

乐观锁

乐观锁基于这样的假设：多个事务在同一时间对同一数据对象进行操作的可能性很小，因此它允许多个事务同时进行，仅在事务提交时检查是否有其他事务对数据进行了修改。

特点：

无锁：乐观锁通常不使用数据库的锁定机制，而是通过其他方式（如时间戳、版本号）来确保数据一致性。
性能：在冲突较少的情况下，乐观锁可以提供更好的性能，因为它允许高并发。
冲突检测：在事务提交时检测冲突，如果检测到冲突，事务将被回滚。

实现方式：

版本号：在数据表中添加一个版本号字段或时间戳字段。事务开始时记录版本号，提交时检查版本号是否发生变化，如果变化了，则表示有冲突。
时间戳：使用时间戳来控制事务的顺序，如果检测到时间戳冲突，则回滚事务。

适用场景：

写冲突较少：适用于写操作不频繁或者写冲突可能性较低的场景。
读多写少：适用于读操作远多于写操作的环境。
高并发：需要支持高并发访问的系统。

悲观锁

悲观锁基于这样的假设：多个事务对同一数据对象的并发操作会产生冲突，因此它在事务开始时就对数据对象加锁，直到事务结束才释放锁。

特点：

锁定：悲观锁通过数据库的锁定机制来确保数据一致性，如行锁或表锁。
性能：在高冲突环境下，悲观锁可能导致更多的等待和锁竞争，从而影响性能。
预防冲突：通过锁定机制预防冲突，而不是检测冲突。

实现方式：

显式锁定：使用数据库的锁定命令（如SELECT ... FOR UPDATE）来显式地锁定需要的资源。
隐式锁定：通过数据库事务的隔离级别隐式地实现锁定。

适用场景：

写冲突较多：适用于写操作频繁或者写冲突可能性较高的场景。
写多读少：适用于写操作多于读操作的环境。
数据一致性要求高：需要严格保证数据一致性的场合。

6. select...for update 加了行锁还是表锁

行锁。

这个在上面的例子中有提到过，这个 SQL 会锁定查询到的行，行锁的粒度更小、可以提高并发性。

示例：

START TRANSACTION;
SELECT * FROM table_name WHERE column_name = 'some_value' FOR UPDATE;
-- 在此事务中，column_name 等于 'some_value' 的行将被加上排他锁
COMMIT;

在这个示例中，只有 column_name 等于 'some_value' 的行会被锁定，其他行仍然可以被其他事务访问。

7. 如何通过 SQL 手动给 MySQL 的某个表加锁

手动加锁也就是显示的加表锁。

MySQL提供了LOCK TABLES语句来显式地锁定一个或多个表。这可以用来阻止其他会话访问这些表。

加写锁（排他锁）示例：

LOCK TABLES table_name WRITE;

加读锁（共享锁）示例：

LOCK TABLES table_name READ;

锁定多个表的示例：

复制
LOCK TABLES table1 READ, table2 WRITE;

8. 高并发场景下，如何修改同一条数据

这个要根据业务场景来选择锁。比如读多写少场景下，可以使用乐观锁，写冲突较多时可以使用悲观锁。

例子：

使用乐观锁：乐观锁适用于写冲突较少的场景。通过在数据表中添加一个版本号或时间戳字段来实现。每次更新数据时，检查版本号或时间戳是否与读取时的值相同，如果相同，则进行更新并增加版本号或更新时间戳；如果不同，则放弃更新，提示用户冲突发生。
```
START TRANSACTION;
SELECT version_column, data_column FROM table_name WHERE id = 1;
-- 检查并更新
UPDATE table_name SET data_column = 'new_value', version_column = version_column + 1
WHERE id = 1 AND version_column = read_version;
COMMIT;
```

使用悲观锁：悲观锁适用于写冲突较多的场景。通过在事务开始时锁定需要修改的数据行，直到事务结束才释放锁。

复制
START TRANSACTION;
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
-- 进行更新操作
UPDATE table_name SET data_column = 'new_value' WHERE id = 1;
COMMIT;

9. MySQL 中的锁升级是什么

锁升级指细粒度锁升级为粗粒度锁，如行锁升级为表锁。锁升级通常发生在一下情况：

锁竞争：当一个事务持有多个行锁，并且这些行锁的数量超过了系统预设的阈值时，数据库系统可能会将这些行锁升级为表锁，以减少系统开销和提高性能。
锁定的行数：如果锁定的行数占表中行数的很大一部分，数据库系统可能会认为整个表都被锁定了，因此会升级为表锁。
锁定的页数：类似地，如果锁定的页（数据库中数据的存储单位）数量占表中总页数的很大一部分，也可能触发锁升级。
系统配置： MySQL的配置参数（如innodb_table_locks）会影响是否启用表锁以及何时进行锁升级。

锁升级的优点包括：

减少系统开销：管理大量行锁的开销可能很大，升级为表锁可以简化锁管理。
提高性能：在某些情况下，锁升级可以减少死锁的可能性，并提高系统的整体性能。

锁升级的缺点包括：

降低并发性：表锁是一种粗粒度的锁，会阻止其他事务访问整个表，从而降低并发性。
增加等待时间：其他事务可能需要等待表锁释放，增加了它们的等待时间。

锁升级是数据库系统自动进行的，不需要用户干预。

10. MySql RR 的隔离级别下加锁规则

重点

在MySQL的可重复读（Repeatable Read，简称RR）隔离级别下，InnoDB存储引擎采用了一系列加锁规则来保证事务的隔离性。

行锁：
- 对于索引项的查询，如果能够定位到具体的索引记录，无论是主键索引还是二级索引，InnoDB会在这些具体的索引项上加上行锁（Record Lock）。
间隙锁：
- 如果查询条件的结果为空，即没有命中任何索引，那么在RR隔离级别下，InnoDB会在相关索引的间隙上加上间隙锁（Gap Lock）。
Next-Key Lock：
- 对于范围查询，InnoDB会使用Next-Key Lock，它是行锁和间隙锁的组合，用于锁定一个区间内的所有行以及区间的边界值。
快照读与当前读：
- 在RR隔离级别下，普通的SELECT语句是快照读（Snapshot Read），不加锁；但是当使用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE时，会变成当前读（Current Read），并加上相应的行锁或共享锁。
幻读：
- RR隔离级别旨在防止幻读现象，即在一个事务中，两次读同一个范围的时候，第二次读到之前不存在的行。为了防止幻读，InnoDB会在查询涉及的范围上加上足够的间隙锁。
锁升级：
- 在RR隔离级别下，如果锁定的行数过多，InnoDB可能会将行锁升级为表锁，尽管这种行为在默认配置下不常见。
自增锁：
- 对于自增字段，InnoDB可能会使用自增锁（Auto-Increment Locks）来保证自增值的唯一性和连续性。
意向锁：
- 在多粒度锁定协议中，InnoDB使用意向锁（Intention Locks），如意向共享锁（Intention Shared Locks）和意向排他锁（Intention Exclusive Locks），以表明事务将在更细粒度上请求锁。
锁的兼容性：
- 共享锁（S锁）之间是兼容的，但排他锁（X锁）与任何其他类型的锁都不兼容。

这些加锁规则共同作用，以确保在RR隔离级别下，事务可以看到一致的快照数据，并防止其他事务的干扰，直到事务完成。

11. 如何使用分布式锁

实现分布式锁有以下几种方式：

11.1. 基于唯一索引实现

这种方法通过尝试向一个表中插入具有唯一索引的记录来实现锁的获取。如果插入成功，则认为获取了锁；如果插入失败（由于唯一键冲突），则认为获取锁失败。

创建锁表：

CREATE TABLE `t_lock` (
  `lock_key` varchar(64) NOT NULL COMMENT '锁的标识',
  PRIMARY KEY (`lock_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分布式锁';

获取锁：

insert ignore into t_lock(lock_key) values('my_lock_key');

使用insert ignore来避免违反唯一约束时产生的错误，如果返回值大于0，则认为成功获取了锁。

释放锁：

delete from t_lock where lock_key = 'my_lock_key';

当操作完成时，删除记录以释放锁。

11.2. 基于排他锁（`SELECT ... FOR UPDATE`）实现

这种方法通过在SELECT语句后增加FOR UPDATE来获取排他锁，确保某条记录在事务中被锁定，其他事务无法再获取到相同的排他锁。

获取锁：

SELECT * FROM some_table WHERE some_column = 'some_value' FOR UPDATE;

这样就会在满足条件的行上加上排他锁。

释放锁：排他锁会在事务结束时释放，无论是通过COMMIT提交事务还是通过ROLLBACK回滚事务。

11.3. 基于乐观锁

乐观锁通常通过在表中添加一个版本号或时间戳字段来实现。每次更新记录时，检查版本号或时间戳是否与读取时的值相同，如果相同则进行更新，否则放弃更新。

获取锁：使用普通的SELECT语句读取数据及版本号。

更新操作：

UPDATE some_table SET column1 = value1, version = version + 1 WHERE id = some_id AND version = some_version;

只有当版本号匹配时才更新，并增加版本号。

释放锁：乐观锁通常不需要显式释放，因为它不依赖于数据库的锁定机制。

12. 如何检测死锁

大多数数据库都自带死锁检测机制，内置的死锁检测器会在事务等待资源时自动检测死锁。例如：

MySQL：可以使用INFORMATION_SCHEMA数据库中的INNODB_LOCK_WAITS表来查看死锁信息。
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
```
这个表展示了哪些事务被锁定，以及哪些事务持有其他事务正在等待的锁。

13. 你遇到过死锁吗，你怎么处理的

解决死锁：数据库管理系统通常有机制来检测和解决死锁：

死锁检测：系统定期检查事务的执行状态，以识别是否存在死锁。
超时：事务在等待资源超过一定时间后超时，系统自动回滚事务。
回滚：检测到死锁后，系统可能会选择一个或多个事务进行回滚，释放它们持有的资源，从而打破死锁状态。
锁升级：在某些情况下，系统可能会尝试将锁从更细的粒度升级到更粗的粒度，以减少死锁的可能性。

这四种是解决死锁的办法，接下来实操如何发现处理死锁。

监控死锁

show engine innodb status

这里返回了包含详细信息的输出，包括死锁检测到的信息。

解决死锁

回滚事务

ROLLBACK;

杀死引起死锁的进程

找到引起死锁的进程 ID，使用 kill 杀死该进程。

SHOW PROCESSLIST;

KILL <process_id>