MySQL-深度探讨锁类别和锁优化策略

MySQL-深度探讨锁类别和锁优化策略

锁是计算机协调多个进程或者线程访问某一资源的机制。

3.1锁分类

3.1.1按照兼容性划分

3.1.1.1共享锁

共享锁：共享锁和共享锁是兼容的，共享锁和排他锁是互斥的。

3.1.1.2排他锁

排他锁：排他锁和其他的任何锁都是不兼容的。

3.1.2按照粒度划分

3.1.2.1全局锁

锁住数据库中的所有表，即锁住库

3.1.2.2表级锁

表级锁，每次操作都要锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在Innodb，MyISAM等存储引擎中，分为表锁、元数据锁 、意向锁。

3.1.2.2行级锁

-- 查看意向锁和行锁的sql

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

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

在InnoDB存储引擎中，数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为三类：行锁（记录锁）、间隙锁、临键锁

3.2全局锁详解

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的所有DML，DDL都会被阻塞。

最典型的使用场景：一般用于从库的数据库数据备份 ，获得一致性数据备份。

在对数据库进行数据备份之前，先对整个数据库加上全局锁，一旦加上全局锁后，其他的DDL、DML全都会处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。

那么数据在进行逻辑备份的过程中，数据库中的数据是不会发生变化的，也就是保证了数据的一致性和完整性。

数据备份sql:

-- 1. 在数据备份之前，加上全局锁，锁住数据库中的所有表

flush tables with read lock;

##### 测试 DDL、DQL
-- 2. 进行数据备份

mysqldump -uroot -p test > D:\dump1.sql
-- 3. 释放锁
unlock tables;

提示：mysqldump是mysql提供的一个工具，在bin目录下。

全局锁特点：

1. 如果在主库上备份，那么备份期间都不能执行更新，业务被暂停。
2. 如果在从库上备份，那么在备份期间不能执行主库同步过来的二进制日志(binlog)，导致主从数据延迟。

如何解决上面的问题？ => 使用快照读备份，而不用加全局锁

-- 在innodb存储引擎中，可以在备份时加上参数 --single-transaction 参数
-- 来完成不加锁的一致性数据备份

mysqldump --single-transaction -uroot -proot test > D:\dump2.sql

3.3表级锁全面剖析

3.3.1表锁

对于表锁，分成两种：表共享读锁(read lock，读锁)，表独占写锁(write lock，写锁)
加锁语法：

-- 1. 加锁
lock tables 表名...  read/write
-- 2. 释放锁
unlock tables / 客户端端开

1. 读锁
   

2. 写锁
   
   总结：

读锁不会阻塞其他客户端的读，但是会阻塞写。
写锁会阻塞其他客户端的读和写。

3.3.2元数据锁（Metadata Lock, MDL）

元数据锁(meta data lock，MDL)，元数据锁的加锁过程是自动的，无需显示加锁，在访问一张表的时候会自动加上。MDL锁的主要作用是为了维护表元数据(理解为表结构)的数据一致性，在有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的正确性。

在MySQL 5.5中引入了MDL，当对一张表进行增删改的时候，加上MDL读锁(共享锁)，当对表结构进行修改的时候，会加上MDL写锁(排他锁)。

测试元数据共享锁:

begin;
select * from t_user;

然后开启另外一个窗口，查看加元数据锁情况：

select object_type,object_schema,object_name,lock_type,lock_duration 
from performance_schema.metadata_locks

3.3.3意向锁（Intention Locks）

为了避免DML在执行时，加的行锁和表锁冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

假如没有意向锁，客户端一对表加了行锁之后，客户端二如何给表加表锁呢？通过示意图简单分析一下：

1. 首先客户端一，开启一个事务，执行DML语句，在执行DML语句时，会涉及到行加行锁。
   

2. 当客户端二，想对这张表加锁时，会检查当前表是否有对应的行锁，如果没有，则直接添加表锁，此时就从第一行数据，检查到最后一行数据，效率较低。
   

有了意向锁之后 :
客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

意向锁类型：

1. 意向共享锁(IS)：由语句select... lock in share mode添加
2. 意向排他锁(IX)：由insert、update、delete、select...for update

那意向共享锁和意向排他锁的兼容情况是如何的？

1. 意向共享锁(IS)：与表锁共享锁(read)兼容，与表锁排他锁(write)互斥
2. 意向排他锁(IX)：与表锁共享锁(read)和排他锁(write)都互斥。意向锁之间不会互斥。

ps：一旦事务提交后，意向共享锁和意向排他锁都会自动释放。

查看意向锁情况:

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

意向锁本身是InnoDB内部使用的一种优化机制，它的主要目的是提高并发性能和减少死锁的可能性，而不是直接提供给用户检查的锁类型。因此，在performance_schema中你可能看不到直接标记为“意向锁”的记录，但你可以通过观察其他类型的锁（例如表级别的锁）以及它们的模式（共享还是排他），结合你的事务操作逻辑，来间接了解是否有意向锁被使用。

演示：

1. 意向共享锁和表读锁是兼容的
   

2. 意向排他锁和表读写锁都互斥
   

3.4行级锁深入解读

3.4.1行锁（Record Lock）

行锁(也叫做记录锁)：锁定单个行记录的锁，针对索引加锁，防止其他事务对此进行update和delete等操作，在RC和RR隔离级别下支持。

InnoDB实现了以下两种类型的行锁：

1. 共享锁(S)：允许一个事务去读一行，防止其他事务加上排他锁。
2. 排他锁(X)：允许获取排他锁的事务更新数据，防止其他事务获得对

应行的共享锁和排他锁。
兼容情况：

哪些SQL加的是共享锁，哪些SQL加的是排他锁呢？

查看加锁情况：

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

记录锁演示：

1. 读锁与读锁兼容，读锁与写锁冲突【测试1】
2. InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB会对表中的所有记录加锁，此时就会升级成表锁。【测试2】

测试1

===>测试加共享锁和共享锁的兼容情况

===>测试加共享锁和排他锁的兼容情况

测试2
===>测试行锁是针对索引加的锁

3.4.2间隙锁（Gap Lock）

间隙锁： 间隙就是数据和数据之间的间隙。间隙锁是锁定索引记录的间隙(不锁定该记录，只锁间隙)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。

3.4.3临键锁（Next-key Lock）

临键锁： 行锁和间隙锁的组合，同时锁住数据，并且锁住记录前面的间隙Gap。在RR级别下支持。(左开右闭)
默认情况下，InnoDB在RR隔离级别下，使用next-key锁进行搜索和索引扫描，以防止幻读。

3.5锁优化策略及加锁情况实战演示

3.5.1唯一索引

3.5.1.1等值查询

• 记录存在
  会由临键锁优化为记录锁【测试1】
• 记录不存在
  会由临键锁优化为间隙锁【测试2】

测试1
===>测试唯一索引等值查询记录存在

测试2
===>测试唯一索引等值查询记录不存在

3.5.1.2范围查询

• 记录存在

锁该记录和满足条件的区域【测试3】

• 记录不存在

锁住满足条件的区域【测试4】

【测试3】
===>测试唯一索引范围查询记录存在加锁情况

【测试4】
===>测试唯一索引范围查询记录不存在加锁情况

3.5.2非唯一索引

给age列建立索引

3.5.2.1等值查询记录存在

【测试5】
===>测试非唯一索引等值查询记录存在加锁情况

3.5.2.1等值查询记录不存在

【测试6】
===>测试非唯一索引等值查询记录不存在加锁情况

3.6死锁问题探讨与避免

3.6.1死锁的原因和场景

原因：
多个事务之间形成循环依赖关系，导致彼此等待对方释放资源，无法继续执行。

场景：
例如：事务A读取了资源X，事务B读取了资源Y，然后事务A请求资源Y，同时事务B请求资源X，可能会发生死锁

3.6.2死锁的检测及处理策略

检测
MySQL使用等待图（Wait-for Graph）算法来检测死锁，通过检查事务之间的依赖关系来判断是否存在死锁。

死锁处理策略：

• 回滚事务
  发现死锁后，可以选择回滚其中一个或多个事务，解除死锁。
• 选择牺牲者
  通过选择一个事务作为牺牲者，回滚该事务以解除死锁。
• 返回错误信息：对于被回滚的事务，MySQL 将返回一个错误信息给客户端，例如 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction。应用程序可以根据此错误信息采取适当的行动，如重试事务。
• 超时设置和重试机制
  当一个事务发现自己无法获取所需的资源时，可以选择等待一段时间，如果超过了设定的超时时间仍未获得资源，则放弃请求并回滚事务。

在MySQL中通常采用等待超时的策略来处理死锁。MySQL的默认配置会自动检测死锁，并选择一个事务作为死锁的牺牲者，终止该事务并回滚。这种策略可以避免死锁的无限循环，并且通常能够保证系统的正常运行。

3.6.3死锁处理实战演示

1. 查看 innodb_deadlock_detect 状态：默认开启
   

SHOW VARIABLES LIKE 'innodb_deadlock_detect';

2. 查看 innodb_lock_wait_timeout （单位为秒）
   

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

===>等待超时的策略演示

===>选择牺牲品回滚演示：

3.6.4死锁的预防和避免

• 尽早提交事务：
  避免在事务中执行长时间的计算、网络操作或其他耗时操作，以减少锁的持有时间。
• 拆分大事务：
  将大事务拆分为多个较小的事务，以减少事务的持有锁时间。
• 使用合适的索引：
  确保查询语句使用合适的索引，以减少锁定的数据量。
  使用索引可以提高查询效率，并减少事务持有锁的时间。
• 优化查询语句：
  尽量使用更精确的条件来限制查询范围，避免长时间持有锁定的行。
• 使用乐观锁：
  对于一些并发较高的场景，可以考虑使用乐观锁机制。
  乐观锁不会持有实际的锁，而是通过版本号或时间戳等方式进行冲突检测，减少了事务持有锁的时间。