Day8

2025-01-15🌱上海: ☀️ 🌡️+4°C 🌬️↓19km/h

# MySQL 中有哪些锁类型？

# 简要回答

在 MySQL 中，主要有以下几中锁类型：

行级锁：对特定行加锁，适合高并发，允许并发访问不同行。
表级锁：对整个表加锁，用于需保证完整性的小型表，加锁时其他事务无法读写。
意向锁：表锁，分意向共享锁（IS）和意向排它锁（IX），用于行级锁与表级锁结合。
共享锁：允许多个事务并发读，不允许修改，释放后其他事务才能获排它锁。
排它锁：只允许一个事务读写，其他事务需等待排它锁释放。
元数据锁（MDL）：保护数据库对象元数据，防止 DDL 操作时被修改。
间隙锁：针对索引间隙加锁，防其他事务插入新记录，避免幻读，不锁定具体行。
临键锁：行级锁和间隙锁结合，防范围内幻读，用于可重复读隔离级别。
插入意向锁：等待间隙的锁，允许共享锁，插入时阻止其他排它锁。
自增锁：插入自增列时加锁，保证自增值唯一。

# 扩展及总结

# 共享锁和排他锁

MySQL 中的锁分为共享锁（S 锁）和排他锁（X 锁）：

共享锁（S 锁）：事务读取记录时获取，允许多个事务同时持有，相互不冲突。使用 SELECT... LOCK IN SHARE MODE; 语句对记录加 S 锁， LOCK TABLES yes READ 对表加 S 锁。
排他锁（X 锁）：事务修改记录时获取，只允许一个事务持有，X 锁之间以及 X 锁与 S 锁之间都会冲突。使用 SELECT... FOR UPDATE; 语句对记录加 X 锁， LOCK TABLES yes WRITE 对表加 X 锁
引擎支持情况：MyISAM 引擎仅支持表锁，InnoDB 引擎既支持表锁也支持行锁。一般日常的 UPDATE 、 SELECT 操作常用行锁，以避免表锁粒度粗导致的性能问题。
表锁使用场景：主要用于 DDL 语句，如 ALTER TABLE 时锁定整个表，防止查询和修改。此外，MySQL 还提供了元数据锁（MDL）用于保护元数据。

是否冲突	S	X
S	不冲突	冲突
X	冲突	冲突

# MDL 锁

元数据锁分为读锁（MDL_SHARED）和写锁（MDL_EXCLUSIVE）：

读锁：事务执行读取表元数据操作（如 SELECT）时获取，多个事务能同时持有读锁，不会相互阻塞。
写锁：事务执行修改表元数据操作（如 ALTER TABLE）时获取，会阻塞其他任何读锁和写锁，实现独占访问。

元数据锁主要作用：

防止操作冲突：在事务进行 DDL 操作（如 ALTER TABLE）时，写锁阻止其他事务对表操作；在事务进行 DML 操作（如 SELECT 等）时，读锁阻止其他事务对表进行结构性更改。
保护元数据一致性：确保 DDL 操作（如 CREATE TABLE 、DROP TABLE、ALTER TABLE）执行时，元数据不被其他事务同时修改。

若业务使用表锁，InnoDB 加表锁时，判断表中是否已有行锁不能通过遍历记录实现，因为效率太低。所以这里就有了叫意向锁的东西

# 意向锁

锁类型	含义	加锁时机	作用
IS（共享意向锁）	Intention Shared Lock，表级锁	当需要对表中的某条记录加 S 锁（共享锁）时，先在表上加 IS 锁	表明此时表内有 S 锁，用于在添加表级锁时快速判断是否可以上锁，避免遍历表中所有记录
IX（独占意向锁）	Intention Exclusive Lock，表级锁	当需要对表中的某条记录加 X 锁（排他锁）时，先在表上加 IX 锁	表明此时表内有 X 锁，用于在添加表级锁时快速判断是否可以上锁，避免遍历表中所有记录

冲突	S	X	IS	IX
S	不冲突	冲突	不冲突	冲突
X	冲突	冲突	冲突	冲突
IS	不冲突	冲突	不冲突	不冲突
IX	冲突	冲突	不冲突	不冲突

接下来了解一下 MySQL 中的行锁有哪几种

# 记录锁

记录锁是作用于索引上的锁，用于锁住当前记录。InnoDB 即便无主键也会创建隐藏聚簇索引，故记录锁始终锁定索引记录。

例如事务 A 执行 SELECT * FROM yes WHERE name = 'xx' FOR UPDATE; 会锁定 name = xx 这条记录，其他事务无法对其进行插入、删除、修改操作。

当事务 A 未提交时，另一事务 B 执行 insert into table (name) values ('xx') 会被阻塞。而事务 C 执行 insert into table (name) values ('aa') 是否阻塞取决于 name 字段：

若 name 没有索引，由于记录锁需作用于索引，此时只能依赖聚簇索引，但聚簇索引无法通过 name 快速定位数据，需全表扫描，最终导致整个表被锁定，所以事务 C 会被阻塞。
若 name 有索引，事务 C 不会被阻塞。

综上，没有索引的列不要随意进行锁定操作。

# 间隙锁和临键锁

为预防幻读（给未存在记录加锁），引入了间隙锁和 Next - Key Locks：

间隙锁
原理：给记录之间的间隙加锁，数据页中有虚拟记录 Infimum 和 Supremum，与实际记录形成多个间隙，间隙锁作用于这些间隙。
作用：例如锁定 3 和 5 之间的间隙，插入 id = 4 的记录会被阻塞，避免幻读，实现锁定未插入记录的需求。
冲突情况：间隙锁之间不冲突，目的是防止其他事务在间隙插入数据。
生效与禁用：在事务隔离级别为可重复读时生效，若将事务隔离级别更改为 READ COMMITTED，间隙锁对于搜索和索引扫描禁用，仅用于外键约束检查和重复键检查。
Next - Key Locks：是记录锁与间隙锁的结合，呈前开后闭区间。如上述例子中，间隙锁锁定 (3, 5) 区间，Next - Key Locks 能锁定 (3, 5] 区间，可防止查询 id = 5 时的幻读

# 插入意向锁

插入意向锁（Insert Intention Locks）是与间隙相关的锁，与间隙锁作用不同：

作用机制：不是锁定间隙，而是等待某个间隙。当事务因间隙锁阻塞（如插入 id = 4 的事务 C 被间隙锁阻塞）时，会生成插入意向锁，表明在等待间隙锁释放。
冲突情况：插入意向锁之间不会阻塞，不存在冲突。因为它们目的一致，都是等待间隙被释放。
存在意义：锁本质是内存中的结构，事务上锁是创建锁对象争抢资源，未抢到资源的事务也会生成等待状态的锁对象。当拥有资源的事务释放锁后，会寻找等待的锁结构并唤醒对应事务。等待间隙锁的插入事务需建立插入意向锁结构，这样间隙锁释放时能找到等待插入的事务并唤醒，且由于插入意向锁之间不阻塞，可多个事务一起执行插入。

# Auto-inc Lock

基本概念与作用：Auto - Inc Lock 是特殊的表级锁，用于自增列插入数据。插入数据时在表上加锁，为自增列分配递增的值，插入语句结束后释放锁。
性能改进：MySQL 5.1.22 版本后引入互斥量进行自增减累加，互斥量性能优于 Auto - Inc Lock。Auto - Inc Lock 在语句插入完毕才释放，而互斥量在获得递增值后即可释放。
主从复制问题：并发插入时，基于 statement - based binlog 复制，自增的值顺序难以把控，可能造成主从数据不一致。
配置选项：MySQL 通过 innodb_autoinc_lock_mode 配置控制自增锁使用方式，有三个值：

0：仅使用 Auto - Inc Lock。
1：默认值，对于插入前已知插入行数的插入操作使用互斥量，插入前不知具体插入数的使用 Auto - Inc Lock，可保证基于 statement - based binlog 复制的安全性。
2：仅使用互斥量。

# Auto inc Lock 实例测试

# 创建测试表

首先创建一个带有自增列的测试表：

	CREATE TABLE test_autoinc (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(50)
	);

# 1. innodb_autoinc_lock_mode = 0（只用 Auto - Inc Lock）

配置 innodb_autoinc_lock_mode = 0 后，所有插入操作都使用 Auto - Inc Lock 。

假设我们有两个并发事务：

	-- 事务 A
	START TRANSACTION;
	INSERT INTO test_autoinc (name) VALUES ('A1');
	-- 事务 A 还未提交，此时表被 Auto - Inc Lock 锁住

	-- 事务 B
	START TRANSACTION;
	INSERT INTO test_autoinc (name) VALUES ('B1');
	-- 事务 B 会被阻塞，因为事务 A 持有 Auto - Inc Lock，只有事务 A 提交后，事务 B 才能获取锁并执行插入

当事务 A 提交后：

COMMIT;

事务 B 才能继续执行插入操作并提交：

	-- 事务 B 继续执行
	INSERT INTO test_autoinc (name) VALUES ('B1');
	COMMIT;

# 2. innodb_autoinc_lock_mode = 1（默认值）

# 插入前已知插入行数的插入（用互斥量）

	-- 已知要插入 3 条记录
	START TRANSACTION;
	INSERT INTO test_autoinc (name) VALUES ('C1'), ('C2'), ('C3');
	-- 这里使用互斥量，在获取每个自增值后就释放锁，并发性能更好
	COMMIT;

# 插入前不知道具体插入数的插入（用 Auto - Inc Lock）

	-- 存储过程中动态插入，事先不知道具体插入数
	DELIMITER //
	CREATE PROCEDURE insert_dynamic()
	BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE num INT DEFAULT FLOOR(RAND() * 10) + 1; -- 随机生成 1 到 10 之间的插入数
	START TRANSACTION;
	WHILE i <= num DO
	INSERT INTO test_autoinc (name) VALUES (CONCAT('D', i));
	SET i = i + 1;
	END WHILE;
	COMMIT;
	END //
	DELIMITER ;

	-- 调用存储过程
	CALL insert_dynamic();
	-- 这里由于事先不知道插入数，使用 Auto - Inc Lock，在整个存储过程结束后才释放锁

# 3. innodb_autoinc_lock_mode = 2（只用互斥量）

	-- 多个并发事务同时插入
	-- 事务 D
	START TRANSACTION;
	INSERT INTO test_autoinc (name) VALUES ('E1');
	-- 事务 D 获得自增值后就释放锁，其他事务可以立即竞争自增值

	-- 事务 E
	START TRANSACTION;
	INSERT INTO test_autoinc (name) VALUES ('E2');
	-- 事务 E 可以快速获取自增值并执行插入，因为互斥量的快速释放机制提高了并发性能
	COMMIT;

这种模式下，在高并发插入场景中性能最好，但在基于 statement - based binlog 复制时，可能会因为自增顺序问题导致主从数据不一致。例如，主库上两个并发事务插入顺序是事务 D 先获取自增值 1，事务 E 后获取自增值 2，但在从库上由于执行顺序差异，可能导致自增顺序不同，从而出现数据不一致。因此，在使用 innodb_autoinc_lock_mode = 2 时，需要特别注意主从复制的一致性问题。

# MySQL 事务的而二阶段提交是什么？

# 简要回答

MySQL 事务的两阶段提交是确保 binlog 和 redolog 一致性的关键机制，旨在防止主备库数据不一致：

两阶段提交过程

Prepare 阶段：SQL 成功执行并生成 redolog，处于 prepare 状态。
BinLog 持久化：先通过 write () 将 binlog 内存日志数据写入文件缓冲区，再用 fsync () 将其从文件缓冲区永久写入磁盘。
Commit 阶段：在执行引擎内部执行事务操作，更新 redolog。

为何需要两阶段提交：若不采用两阶段提交，可能出现两种数据不一致情况。一是先写 redo log 成功但 binlog 未写，系统崩溃重启后，主备同步会缺变更记录；二是先写 binlog 成功但 redo log 未写，重启后崩溃恢复无操作，但主备同步会将新值同步到备库，导致主备数据不一致。
两阶段提交保证一致性的方式

情况一：一阶段提交后崩溃（redo log 处于 prepare 状态），崩溃恢复时直接回滚事务，主备均未执行该事务。
情况二：一阶段成功且写完 binlog 后崩溃，检查 binlog 中事务是否存在且完整，若存在且完整则提交事务，否则回滚。
情况三：redo log 处于 commit 状态时崩溃，重启后处理同情况二。

判断 binlog 和 redolog 一致的方法：当 MySQL 写完 redolog 并标记为 prepare 状态时，会在 redolog 中记录全局唯一标识事务的 XID。设置 sync_binlog = 1 后，写 redolog 完成第一阶段，MySQL 会将对应 binlog 刷新到磁盘，binlog 结束位置也有 XID。当两者 XID 一致时，MySQL 认为 binlog 和 redolog 逻辑上一致。

innodb_flush_log_at_trx_commit=2 ：每个事务提交时仅将日志写入操作系统缓存，定期刷新到磁盘，组提交效果更明显。
innodb_flush_log_at_trx_commit=0 ：不在事务提交时刷盘，数据可能丢失，但可以最大化组提交的效果

# 有了组提交后的二阶段提交

在引入组提交后，MySQL 事务的两阶段提交过程发生变化，主要体现在日志刷盘环节：

write 和 fsync 操作：write 操作将数据写入文件缓冲区，数据暂存于内存；fsync 用于将文件修改强制持久化到磁盘，常与 write 配合确保数据落盘。
两阶段提交变化：由于组提交，日志刷盘过程中的 fsync 步骤被延迟。需等待一个组内多个事务都处于 Prepare 阶段后，才进行一次组提交，将日志统一持久化到磁盘。

# MySQL 中如果发生死锁应该如何解决？

# 总结分析

# 什么是死锁？

# 死锁概述

死锁指两个或多个进程（或线程）执行中，因资源竞争或通信而阻塞，无外力则无法推进，处于此状态的系统中，相互等待的进程即死锁进程。

# 死锁产生的必要条件

互斥条件：资源每次仅能被一个进程使用。
占有且等待：进程请求资源受阻时，不释放已获取的资源。
不可强行占有：进程已获资源在未使用完前，不能被强行剥夺。
循环等待条件：进程间形成循环等待资源的关系。

# 死锁的解除与预防

避免四个必要条件同时发生可解除和预防死锁，常见方法包括：

破坏不可抢占：设置优先级，让高优先级进程能抢占资源。
破坏循环等待：确保多个进程（线程）执行顺序相同，防止循环等待资源情况出现。

# 数据库死锁相关总结

# 一、死锁产生原因

资源竞争：多个事务对相同资源（如数据库表、行等）请求顺序不同引发互相等待。
未释放资源：事务完成后未释放资源，常因程序错误或异常所致。
事务执行速度差异：获取资源后执行慢的事务使其他事务等待超时。
操作数据量过大：持有锁时又请求更多锁致互相等待。

# 二、死锁解决办法

自动干预：多数现代数据库管理系统检测到死锁会自动回滚部分事务打破死锁。
手动强制回滚：部分 DBMS 支持手动操作，如 Navicat 可按特定步骤关闭死锁进程。
MySQL 自身处理：开启死锁检测（innodb_deadlock_detect = on）可定时检测并自动终止事务解决；设置事务等待锁超时时间（innodb_lock_wait_timeout），超时则回滚事务解决。

# 三、避免死锁方法

减少锁数量：用 RC 替代 RR 规避因 gap 锁和 next - key 锁引发的死锁。
减少锁时长：加快事务执行速度、缩短执行时间。
固定顺序访问数据：事务访问同表时按相同顺序获取锁。
减少操作数据量：降低事务操作数据规模及持有时间。

# 死锁示例图

# 如何解决死锁？

自动干预：多数现代数据库管理系统检测到死锁会自动回滚部分事务打破死锁。
手动强制回滚：部分 DBMS 支持手动操作，如 Navicat 可按特定步骤关闭死锁进程。
MySQL 自身处理：开启死锁检测（innodb_deadlock_detect = on）可定时检测并自动终止事务解决；设置事务等待锁超时时间（innodb_lock_wait_timeout），超时则回滚事务解决。

# 如何避免死锁？

常见避免或降低死锁的手段如下：

事务处理方面：避免大事务，将其拆分为多个小事务以快速释放锁，减少锁持有时间和冲突概率。
锁申请顺序：调整申请锁的顺序，如先获取影响范围大的锁或固定访问数据的顺序，确保获取足够锁的同时避免死锁。
隔离级别调整：用读已提交隔离级别替换可重复读，减少因间隙锁和临键锁导致的死锁情况。
索引优化：合理建立索引，使操作能命中索引，减少加锁范围，降低死锁概率。
检测与等待时长设置：开启死锁检测，并适当调整锁等待时长，以便及时发现和处理死锁情况。

# 手动关闭死锁步骤

手动 kill 语句步骤如下：

查找当前的事务和锁信息

执行 SHOW ENGINE INNODB STATUS; 命令，可输出 InnoDB 状态，从中找到被阻塞的事务及其线程 ID，同时也能查看死锁信息和当前的活动事务。
还可查询 INFORMATION_SCHEMA 中的 INNODB_LOCKS 和 INNODB_LOCK_WAITS 表来查看当前锁和锁等待情况以获取事务 ID。
通过查询 INFORMATION_SCHEMA 的 innodb_trx 表找到事务 ID 和线程 ID 的对应关系。

手动终止该事务：确定需要 KILL 的线程 ID 后，执行 KILL <thread_id>; 命令，将 <thread_id> 替换为实际找到的线程 ID。

同时对相关示例表的查询结果及字段解释如下：

INNODB_LOCKS ：
用于查询当前锁的信息。
示例结果中 LOCK_ID 唯一标识每个锁； LOCK_MODE 有排它锁（X）、共享锁（S）等类型； LOCK_TYPE 如 RECORD 表示行锁； LOCK_TABLE 是被锁定的表； LOCK_INDEX 为相关索引； LOCK_OWNER 是拥有该锁的事务 ID； LOCK_DATA 是被锁定的数据行的主键值。
INNODB_LOCK_WAITS ：
用于查询锁等待情况。
示例结果中 REQUESTING_TRX_ID 是请求锁的事务 ID； HOLDING_TRX_ID 是当前持有锁的事务 ID； LOCK_ID 与 INNODB_LOCKS 表中的锁相对应； LOCK_TYPE 是请求的锁类型。
INNODB_TRX ：
用于查询线程 ID 等信息。
示例结果中 trx_state 表示事务的当前状态，如 LOCK_WAIT 表示事务正在等待锁； trx_started 是事务开始的时间； trx_mysql_thread_id 与该事务关联的 MySQL 线程 ID，可用于查找事务更多信息； trx_query 是当前事务正在执行的查询； trx_id 是事务的唯一标识符。

Mysql

# MySQL 中有哪些锁类型？

# 简要回答

# 扩展及总结

# 共享锁和排他锁

# MDL 锁

# 意向锁

# 记录锁

# 间隙锁和临键锁

# 插入意向锁

# Auto-inc Lock

# Auto inc Lock 实例测试

# 创建测试表

# 1. innodb_autoinc_lock_mode = 0（只用 Auto - Inc Lock）

# 2. innodb_autoinc_lock_mode = 1（默认值）

# 插入前已知插入行数的插入（用互斥量）

# 插入前不知道具体插入数的插入（用 Auto - Inc Lock）

# 3. innodb_autoinc_lock_mode = 2（只用互斥量）

# MySQL 事务的而二阶段提交是什么？

# 简要回答

# 有了组提交后的二阶段提交

# MySQL 中如果发生死锁应该如何解决？

# 总结分析

# 什么是死锁？

# 死锁概述

# 死锁产生的必要条件

# 死锁的解除与预防

# 数据库死锁相关总结

# 一、死锁产生原因

# 二、死锁解决办法

# 三、避免死锁方法

# 死锁示例图

# 如何解决死锁？

# 如何避免死锁？

# 手动关闭死锁步骤

Day7

Day9