Mysql数据库锁

锁

什么是锁

一种用于控制数据库并发访问的机制。它可以防止多个事务同时访问同一数据，从而避免数据冲突和保证数据一致性。锁通过限制或控制并发操作，确保数据在多用户环境下的一致性和完整性，尤其是在事务性数据库中。

锁的作用

数据一致性：通过避免并发冲突，确保事务按照预期执行，保证数据库的ACID特性。
防止脏读、不可重复读、幻读等问题：数据库锁机制能够防止不同事务间不一致的数据读写问题。
并发性控制：合理使用锁能够提高并发执行的效率，同时避免资源竞争。

锁的分类

按锁的粒度分类

行锁（Row Lock）：
只锁定当前操作的单行数据。这是最细粒度的锁，能够实现较高的并发性。
优点：并发性高，能够减少锁竞争。
缺点：管理开销大，性能消耗较高。

注：Mysql Innodb引擎默认行锁

表锁（Table Lock）：
锁定整个数据表，事务对表中的所有数据进行操作时，其他事务无法访问该表。
优点：简单、管理方便。
缺点：并发性差，容易造成阻塞和锁竞争。

页锁（Page Lock）：
锁定数据库中的一页（通常是多行记录）。这是行锁和表锁之间的一种中间粒度的锁。
优点：比表锁粒度小，提高了一定的并发性。
缺点：粒度大于行锁，可能会影响性能。

按锁的类型分类

共享锁（S Lock）：
允许事务读取数据，但不允许对数据进行修改。其他事务也可以对数据加共享锁（读锁），但不能加排他锁（写锁）。
使用场景：事务需要读取数据而不修改数据时。

排他锁（X Lock）：
允许事务读取和修改数据，且在锁释放之前，不允许其他事务对该数据加任何类型的锁。
使用场景：事务需要修改数据时，确保数据在修改期间不被其他事务读取或修改。

意向锁（Intention Lock）：
一种用于表示事务将要对某一行或某一表加锁的锁。它本身不会阻塞其他事务的操作，但它可以用于协调不同事务之间的锁。
意向共享锁（IS Lock）：事务打算对某些数据加共享锁。
意向排他锁（IX Lock）：事务打算对某些数据加排他锁。

加锁机制分类

悲观锁（Pessimistic Locking）
定义：假设发生冲突，直接加锁。MySQL通过排他锁（X锁）来实现悲观锁。
实现方式：
使用SELECT … FOR UPDATE语句加排他锁（X锁），防止其他事务同时修改数据。
适用于冲突较多的高并发环境。

乐观锁（Optimistic Locking）
定义：假设不会发生冲突，不加锁，而是在提交时检查数据是否被修改。MySQL并没有内置的乐观锁，但可以通过版本号或时间戳来实现。
实现方式：
在表中增加版本号字段（如version），每次更新时检查该版本号是否匹配，若不匹配则说明数据已被修改，事务回滚或重试。
通过控制版本号，避免了加锁操作。

行锁的实现

Mysql的默认实现是行锁

记录锁（Record Lock）

定义：记录锁是行锁的一种，用来锁定表中的单一数据行，防止其他事务修改该行数据。
实现方式：
通过 SELECT … FOR UPDATE（排他锁） 或 SELECT … LOCK IN SHARE MODE （共享锁 ）来锁定特定行。
记录锁是最常用的行锁类型，适用于需要对特定数据行进行排他操作的场景。

示例：

SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;

在这个例子中，order_id = 123 的记录会被加上排他锁（X锁），其他事务不能对该行进行修改。

间隙锁（Gap Lock）

定义：间隙锁是一种特殊类型的行锁，用来锁定索引中的“空隙”，防止其他事务在该空隙范围内插入新行。间隙锁用于防止幻读的发生。

实现方式：
在执行范围查询时，MySQL 会在匹配的记录之间加锁，防止其他事务插入数据，从而避免幻读现象。

示例：

SELECT * FROM orders WHERE order_date > '2025-01-01' FOR UPDATE;

该查询可能会在 order_date > ‘2025-01-01’ 的范围内加上间隙锁，以防止其他事务在这个范围内插入新的订单。

临键锁（Next-Key Lock）

定义：临键锁是记录锁和间隙锁的结合体。它不仅锁定某个记录行（通过记录锁），还会锁定该行所在的间隙，以防止其他事务插入新数据到该位置。

实现方式：

临键锁是一种复合锁，它通常出现在范围查询的情况下，不仅锁定目标记录行，还锁定它前后的“间隙”区域。
示例：

SELECT * FROM orders WHERE order_id BETWEEN 100 AND 200 FOR UPDATE;

在这个查询中，InnoDB 会为 order_id 在 [100, 200] 范围内的记录加锁，同时还会锁住这些记录之间的空隙，防止其他事务插入数据。

插入意向锁（Insert Intention Lock）

定义：插入意向锁是一种特殊的锁，它主要用于表明事务在某个范围内有可能会插入新行。插入意向锁本身并不会加锁某一行或区间，它只是一个信号，表示事务可能会在该范围插入数据。

实现方式：
当事务准备在某个范围内插入数据时，它会首先加一个插入意向锁。其他事务无法在该范围内插入数据，从而避免冲突。

示例：

SELECT * FROM orders WHERE order_id > 500;

在某些情况下，InnoDB 会在查询的结果范围内插入意向锁，标记该范围内可能插入数据。

死锁

什么是死锁

死锁是指两个或多个事务在执行过程中，因为互相持有对方需要的锁而造成的永久性阻塞，从而无法继续执行。死锁会导致数据库系统的性能严重下降，因此必须有效地检测和排除。幸运的是，MySQL InnoDB引擎可以自动检测死锁，并自动回滚其中一个事务，通常是选择回滚最少操作的事务，以便其他事务能够继续执行。

死锁发生时 MySQL 的处理方式

当MySQL检测到死锁时，它会：

自动回滚一个事务：InnoDB会选择回滚事务中开销最小的那个（例如，事务已执行较少的操作，或者锁定的数据较少）。
返回死锁错误：MySQL会返回错误代码 1213 - Deadlock found when trying to get lock; try restarting transaction，并附带死锁的相关信息。

怎么排查死锁

1.查看死锁信息
当MySQL发生死锁时，你可以通过以下方式查看死锁的具体信息：

查看错误日志：MySQL的错误日志中记录了死锁信息，包括涉及的事务、锁等待情况等。
错误日志的路径通常是 /var/log/mysql/error.log 或 /var/log/mysqld.log，具体路径可通过my.cnf文件配置。

通过 SHOW ENGINE INNODB STATUS 命令：该命令返回详细的InnoDB引擎状态信息，其中包括最新的死锁信息。

在输出中，你可以找到类似如下的“LATEST DETECTED DEADLOCK”部分，里面包含了有关死锁事务、锁请求、回滚等详细信息。

2.分析死锁信息
通过上述命令或日志输出，你可以找到以下信息：

涉及的事务：哪些事务涉及了死锁。
事务持有的锁和等待的锁：每个事务持有的锁，以及它们等待的锁。
死锁的资源：死锁是由于哪些资源（如特定的数据行、表等）导致的。
分析这些信息可以帮助你找出死锁的根本原因。

总结

数据库锁大的分类就是表锁，行锁，页锁，其中行数是Mysql数据库的默认实现，其中行锁的实现有记录锁，间隙锁，临键锁，插入意向锁，且按照分类有共享锁和排他锁，一种是乐观的一种的悲观的，其中乐观锁和悲观锁只是一种加锁机制，看实际场景使用。

其他

表锁怎么加

显式表锁：LOCK TABLES

LOCK TABLES 用于显式地给表加上读锁或写锁，阻止其他事务对该表的读写操作。

读锁（READ）：允许其他事务读取该表，但不允许其他事务修改它。
写锁（WRITE）：只允许当前事务对该表进行操作，其他事务无法对该表执行读或写操作。

-- 加表的写锁，禁止其他事务修改该表
LOCK TABLES user_info WRITE;

-- 执行查询或其他写操作
SELECT * FROM user_info WHERE id = 2;

-- 解锁表
UNLOCK TABLES;

注意： LOCK TABLES 只能在事务外部使用（即不在 START TRANSACTION 内）。

页级锁（Page Lock）

页级锁是 InnoDB 存储引擎的一种隐式锁，它通常不由用户直接控制，而是由 InnoDB 根据查询操作自动管理。在 InnoDB 中，数据是按页（通常是 16KB）存储的。通过某些操作，可能会触发页锁。

例如，当一个事务更新多行数据，且这些行数据存储在同一个页中时，InnoDB 会对该页加锁。页锁的粒度比行锁要大，但比表锁小，因此它可以在一定程度上提高并发性。

MySQL 本身不提供直接的语法来显式地加页锁，但你可以通过 更新多个行 来间接触发页锁。

触发页锁的场景

当你对多个行的数据进行修改，且这些行存储在同一个页中时，InnoDB 会自动对该页加锁。例如：

START TRANSACTION;

-- 更新多个行，可能会导致同一页被加锁
UPDATE user_info SET order_count = order_count + 1 WHERE id BETWEEN 1 AND 100;

COMMIT;

总结

行锁：通过 SELECT … FOR UPDATE 或 SELECT … LOCK IN SHARE MODE 实现，对单行或多行进行加锁。
表锁：通过 LOCK TABLES 显式加锁，控制整个表的读写访问。
页锁：InnoDB 在进行某些查询时自动管理的锁，通常不需要用户显式控制，但可以通过操作多个行的数据来间接触发。

原文地址：https://blog.csdn.net/qq_44819486/article/details/145269185

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！