MySQL 锁

锁

1 锁的概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

查看锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

要其他手动开启的事务加锁之后，才能查看的到。

2 全局锁

在 MySQL 中锁按颗粒度分为三类

全局锁: 锁定数据库中的所有表。
表级锁：每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据

介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

语法

1.终端1

#登录到MySQL
flush tables with read lock ;

2.终端2

#不要登录到MySQL，而是在MySQL的bin目录下的mysqldump
mysqldump -uroot –proot123 test > test.sql

3.回到终端1

unlock tables; # 释放全局锁

3 表级锁

表级锁，就是锁整张表。

3.1 介绍

表锁
元数据锁（meta data lock，MDL）
意向锁

3.2 表锁

表共享锁

# 加锁
lock tables table_name read;
# 解锁
unlock tables;

所有事务只能读不可写

表独占写锁

# 加锁
lock tables table_name write;
# 解锁
unlock tables;

只有事务自己可读可写，其他不可读亦不可写

3.3.3 元数据锁

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。**为了避免DML与DDL冲突，保证读写的正确性。**  ==说白了，就是修改表结构的锁==

MDL 读锁

在 MySQL5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁 (共享)；
就是，事务有操作时，其他事务不能对表结构就行修改。

MDL 写锁

当对表结构进行变更操作的时候，加 MDL 写锁 (排他)。
当对表结构就行修改时，其他事务不能读写。

3.4 意向锁

为了解决行锁与表锁的冲突而出现，意向锁本质是表锁，用来标识该表的行锁情况，避免其他事务要遍历表来检查行锁。

说说过程

1.当事务 A 进行增删改查 (查需要手动加共享锁) 的时候，会先对需要操作的行进行加锁。并且，InnoDB 会对整张表加一个意向锁。

2.其他事务想加表锁，不再需要逐行扫描行锁了，直接根据意向锁来解决锁是否可以兼容加锁即可，极大提高了效率。

3.4.1 意向锁分类

意向共享锁 (IS):
- 由语句 select … lock in share mode 添加。
- 与表锁共享锁 (read) 兼容，与表锁排他锁 (write) 互斥。
- 手动添加
意向排他锁 (IX):
由 insert、update、delete、select…for update 添加。与表锁共享锁 (read) 及排他锁 (write) 都互斥，意向锁之间不会互斥。
自动添加

4.4 行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中。

4.4.1 行锁分类

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。在 RC、RR 隔离级别下都支持。

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生幻读。在 RR 隔离级别下都支持。

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap。在 RR 隔离级别下支持。

4.2 InnoDB 的行锁

共享锁（S） - 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁，但是可以自己执行增删改来把行升级成排它锁。

排他锁（X） - 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。即，自己可以读写，其他事务只可以读 - 注意: 要与表锁区分，表锁的写锁，其他事务既不可读也不可写。

常见的 SQL 行锁情况：

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT（正常）	不加任何锁
SELECT … LOCK IN SHARE MODE	共享锁	之后执行增删改，则会自动升级成排它锁。
SELECT … FOR UPDATE	排他锁	需要手动在 SELECT 之后加 FOR UPDATE

默认情况下，InnoDB 的隔离级别是 REPEATABLE READ 即可重复读，另外使用了 next-key 锁 (间隙锁) 进行搜索和索引扫描，以防止幻读。

警告

因为 InnoDB 的行锁是针对索引的，所以如果 where 字段中没有索引或者索引失效，那么将升级成表锁！！
不管 where 用的是二级还是主键索引，最后统统优化成主键索引！

4.3 记录锁 & 间隙锁 & 临键锁

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，为了好好理解锁的应用场景我们分情况讨论。

记录锁: - 主键或唯一索引等值命中触发

间隙锁: - 使用索引，不论等值/范围，未命中

锁类型	命中时机	范围	备注
记录锁	主键或者唯一索引并等值，并命中	被查出来的记录本身
间隙锁	使用索引，不论等值 / 范围，未命中	左开右开	间隙锁，就只有间隙
临键锁	使用索引，范围查询，并命中	左闭右开	记录锁 + 临键锁的结合

间隙锁与临键锁有时候很混乱，直接给例子说明（后缀全是 lock in share mode）
命中记录时

select ... where age = 5 #锁区间：  [1,5)  [5]  [5,10)，即[1,10)

其实这我不理解，为什么大家都说是左开右闭，但是我反复试验，age=1 的记录 (id 自增) 是无论如何都无法插入，我怀疑是临键锁。

或者说，间隙锁不会单独出现，它虽然是左开右开，但是会与记录锁共同作用成临键锁。

select ... where age > 5 #锁区间：  [5,+∞)
select ... where age = 15 #锁区间：  [10,15)  [15]   [15,+∞)

记录不存在时：

select ... where age > 15 #锁区间：  [15,+∞)

select ... where age > 1000 #锁区间：  [1000,+∞)

select ... where age = 1000 #锁区间：  [1000,+∞)

三种锁总结：

当开启事务并加锁（共享 / 排它），锁的都是左闭右开！！
左闭：到上一条记录，含记录，没有则是- ∞
右开：到下一条记录，不含记录，没有则是 + ∞
注意：如果是 age>n，
实际上，存在多条记录，那么将会把所有的 age>n 的记录当成参照体，重新以左闭右开进行加锁，即类似级联加锁。
但是，可以简单理解成：[n,+∞)

死锁

MySQL中的死锁（Deadlock）是指两个或多个事务在执行过程中由于相互持有对方所需的资源而造成的一种僵局状态。在这种状态下，事务无法继续执行，系统需要进行干预来解决这个问题。

以下是MySQL死锁的基本概念、检测、分析和解决方案的教程。

1. 死锁的基本概念

死锁通常发生在以下情况下：

事务A持有锁资源1，并请求锁资源2。
事务B持有锁资源2，并请求锁资源1。
由于相互持有的锁资源，两个事务都无法继续执行，从而产生死锁。

2. 死锁的检测

MySQL自动检测死锁，并在发生死锁时回滚其中一个事务。你可以使用以下方法查看死锁信息：

查看InnoDB死锁日志：

在MySQL日志中，死锁信息通常记录在InnoDB引擎的错误日志中。你可以通过以下命令查看日志文件：

SHOW ENGINE INNODB STATUS;

该命令会返回InnoDB存储引擎的状态信息，其中包括死锁的详细信息。

3. 分析死锁

在查看 SHOW ENGINE INNODB STATUS 的输出时，找到“LATEST DETECTED DEADLOCK”部分，这里会显示最近检测到的死锁信息。关键字段包括：

TRANSACTIONS: 包含参与死锁的事务的详细信息。
LOCKS: 发生死锁的锁资源。
WAITING FOR: 当前事务正在等待的资源。
HELD BY: 当前事务持有的资源。

通过这些信息，可以确定哪个事务被回滚了，以及死锁的原因。

4. 解决死锁

解决死锁的方法主要包括以下几种：

4.1. 设计事务

减少事务的持续时间: 尽量将事务的执行时间缩短，减少持有锁的时间。
按照相同的顺序访问资源: 确保所有事务按照相同的顺序访问锁资源，避免循环依赖。
使用合理的锁粒度: 尽量避免对整个表进行锁定，尽量使用行级锁。

4.2. 处理死锁

重试机制: 在应用程序中捕获死锁异常（通常是错误代码1213），并实现重试机制。重试机制可以在事务被回滚后自动重试，以期获得成功。

示例（伪代码）：

while True:
    try:
        # 执行数据库操作
        break
    except DeadlockException:
        # 捕获死锁异常，重试
        continue

使用合理的隔离级别: 在事务中选择适当的隔离级别。通常，使用较低的隔离级别可以减少死锁的概率，但可能会增加脏读等其他问题。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5. 实践示例

假设有两个事务如下：

事务A：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- 等待
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

事务B：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 2;
-- 等待
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;

如果事务A在执行第一个UPDATE时持有了锁，并且事务B在执行第一个UPDATE时也持有了锁，而两个事务都在等待对方释放锁，那么就会发生死锁。

通过合理设计事务及应用上述解决方案，你可以有效地减少和解决MySQL中的死锁问题。

Z笔记

左老师的课堂笔记