【Mysql】Mysql锁详解（三）

锁分类

• 从性能上分为乐观锁(用版本对比来实现) 和 悲观锁
• 从对数据库 *** 作的类型分，分为读锁 和 写锁 (都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读 *** 作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写 *** 作没有完成前，它会阻断其他写锁和读锁

• 从对数据 *** 作的粒度分，分为全局锁 ，表锁，页锁 和 行锁

(重点）锁是存储引擎层的，不是服务器层的

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁

MySQL大致可归纳为以下3种锁：

1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

1.全局锁：

全局锁是针对整个数据库而言的，从名称我们也能了解到大概意思，当我们对数据库加了全局读锁之后，那么其他任何对请求都不能对数据库再加写锁了，当我们对数据加了写锁后，那么后续对数据的加读锁和写锁的请求都会被阻塞。

为什么需要全局锁

全局锁的典型使用场景是，做全库逻辑备份

因为我们在做备份数据或者导入导出数据 *** 作的时候，如果这个系统期间还可以进行数据的增、删、改，那么就会有一个数据“时点性”问题，会导致数据不一致。

以全局备份数据的过程为例：

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

2.表锁：

表级锁有两种
一种是针对于表记录数据的锁，
另外一种就是MDL(metadata lock)的锁，它是基于表元数据(表结构)的锁

（1）MDL(metadata lock)

MDL锁是为了保证并发环境下元数据和表数据的结构一致性。如果有其它事务对表加了MDL锁，那么其它事务就不能对表结构进行变更，同样对于正在进行表结构变更的时候也不允许其它事务对表数据进行增删改查。

• MDL读锁：在我们对表数据进行增删改查的的时候都需要对表加MDL读锁。
• MDL写锁：当我们对表结构进行修改的时候会加MDL写锁。

考虑一个生产问题： 线上给数据量很大的表修改字段的时候，怎么办？如果锁住表的话，后面读写请求全部被阻塞住，会拖垮整个服务

• 方案1：考虑将从库作为影子库，断开binlog同步，然后修改字段，然后等修改完，同步binlog，等到数据对齐后，锁住表（防止用户修改源表数据），再切库。
• 方案2：ghost方案

1、首先新建一张ghost表，结构与源表相同
2、使用alter命令修改ghost表
3.
	（1）模拟从库命令获取主库上该表的binlog(基于全镜像的行模式的binlog包含更改前和更改后的所有数据)，并解析成语句到ghost表上执行。
	（2）获取源表的数据范围（如按照主键获取到最大值和最小值），然后将数据拆分为多个批次拷贝插入到ghost表中
4、锁住源表，防止用户修改源表数据（锁的时间会很短，避免不了的）
5、将源表重命名，将ghost表改名为源表
6、释放表锁，清理gh-ost工具产生的表。

（2）表锁

表锁就是锁住整张表，其他的用户对该表的任何 *** 作都受影响，具体影响取决于锁的形式。

ＭySQL的表锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）：

1. 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2. 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写 *** 作；

语法：

#加锁，多个表通过逗号分隔
LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},……]
#解锁
unlock tables;

（2.1） 表锁细节需要区分是MyISAM还是InnoDB

• MyISAM

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新 *** 作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预

• InnoDB

必须显示加表锁语句（读的时候）

注意：如果没有索引，innodb的行锁会升级为表锁，效果和表锁一样，锁住全部索引

3. 行锁(record lock：记录锁)

行锁不是只能在一行上加锁，可以是若干行，可以是连续的，也可以是跳跃的

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

• 共享锁（s）：共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
• 排他锁（Ｘ）：排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改

事务可以通过以下语句显示给记录集加共享锁或排锁：
共享锁（Ｓ）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE

InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型（不加锁的是快照读，加锁是当前读）

（1）行锁的原理：（MySQL InnoDB的锁就是靠锁住索引项来实现的）

这个是由InnoDB索引的存储和检索方式决定的。辅助索引中存储的是二级索引和主键的ID，所以锁住辅助索引后，会根据主键ID找到对应的主键索引，也锁定之（图中红色箭头）。
而通过主键索引检索数据加锁，则只会锁住主键索引（图中绿色箭头）。

（2）为什么表没有索引，表里所有的记录都会被锁住？

当表上没有创建索引的时候，InnoDB会为每一行创建一个隐藏的主键作为聚集索引。这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

当不通过索引检索数据的时候，MySQL会使用全表扫描，此时所有行的索引都会被锁定，行锁升级为表锁。

4. 意向锁（不重要）

意向锁是InnoDB自动加的，不需用户干预，事务在请求行锁中的共享锁和排他锁前，会先自动获得意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

5. 间隙锁（Gap锁）（只有在可重复读RR隔离级别下面才有）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是是所谓的间隙锁（GAP锁）。

（1）问题环节

例如，“ SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;”。字面上意思是锁住10-15的数据，如果id=10的数据已存在，那么别的用户不可以修改该条数据，但是如果id=15的数据并不存在，那么可以插入id=15的数据吗？

答案是否定的：不可以，因为无论该列中是否已有这样的值，因为该范围中id (10,15)所有现有值之间的间隙是锁定的。

InnoDB使用间隙锁的目的，一方面是为了在当前读的情况下防止幻读，以满足相关隔离级别的要求

（2）为什么间隙锁只有在RR级别才有，RC级别没有，这两个级别的区别是什么（重点）？

RC 级别是解决了读未提交的，出现的脏读现象（因为读未提交是没有MVCC，没有快照读的，是没有加锁的当前读，数据库是什么就是什么）

RC级别产生幻读 和解决脏读的情况：

（1）如何解决脏读现象？

1. 快照读 在事务开启之后，如果没有加锁（快照读），每次发起查询，都重新生成一个新的ReadView, 根据trx_id是读不到其他未提交的数据。
2. 当前读需要for update 会加锁的，如果是未提交的数据，说明此时锁被未提交的事物A占有，此时事务B是读取不到数据的

（2）为什么会产生幻读现象 ？

1. 快照读 在事务开启之后，如果没有加锁（快照读），会生成视图MVCC, 当你一个事务设置他处于RC隔离级别的时候，他是每次发起查询，都重新生成一个ReadView，高水位和低水位变了，能读到其他事务提交的。
2. 当前读需要for update 会加锁的，但是是行锁，例如 select * from a where id >=1 and id <=5 , 记录里面只有1和5有数据，234是没有数据，此时加锁之会锁住1和5这个行，1和5之间的缝隙是没有锁住，可以进行插入，b的事务插入之后2，a事务在查询出来就会反查出来多了2，这就是幻读

RR级别解决幻读的情况：（只能解决当前读，快照读还是会产生幻读现象）

1. 快照读 在事务开启之后，如果没有加锁（快照读），会生成视图MVCC, 当你一个事务设置他处于RC隔离级别的时候，他是一个事务里面只会有一个视图。可避免部分幻读，但两次快照读中间有更新语句(包含其它事务提交的插入数据-在第一次快照读后开启并提交)，第二次快照读则出现幻读，因为最新的undo log 版本链trx_id是你的了，又可以读到了。
2. 当前读中：默认使用next-lock实现的行锁，不会导致幻读；别的事务根本插不进去。(实际是需要看走的索引-主键、唯一、非唯一、不走索引-这里暂时不做分析)；

6. Next-Key Locks

Next-Key Locks 是行锁底层实现中的概念
Next-Key锁是索引记录上的行锁定 + 索引记录之前的间隙上的间隙锁定

行锁与间隙锁组合起来用就叫做Next-Key Lock。组合起来就形成了一个区间，那么就会以区间为单位进行锁定，而不是单纯的以where条件中的范围来定。

next-key lock 是前开后闭区间，而间隙锁是前开后开区间。但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

其中，id 是主键索引（唯一索引），b 是普通索引（非唯一索引），a 是普通的列。

唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」

select * from test where id= 5 for update

加锁的基本单位是 next-key lock，SQL语句的加锁范围是(0, 5];
但是由于是用唯一索引进行等值查询，且查询的记录存在，所以 next-key lock 退化成记录锁，因此最终加锁的范围是 id = 5 这一行。

• 当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」

select * from test where id= 8 for update

加锁的基本单位是 next-key lock，因此主键索引 id 的加锁范围是(5, 10];
但是由于查询记录不存在，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

唯一索引范围查询

select * from test where id >= 5 and id < 10 for update

• 最开始要找的第一行是 id = 5，因此 next-key lock(0,5]，但是由于 id是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 5 这一行加锁；
• 由于是范围查找，就会继续往后找存在的记录，也就是会找到 id = 10 这一行停下来，然后加 next-key lock (5,10]，但由于 id = 10 不满足 id < 10，所以会退化成间隙锁，加锁范围变为 (5, 10)。

所以 ，这时候唯一主键索引范围查询的锁是记录锁 id=5 和间隙锁(5, 10）

非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁

select * from test where b = 5 for update

因为是非唯一索引。不知道是不是只有一行是符合数据的，所以得锁住这个等值的左右间隙

1. 先会对普通索引 b 加上 next-key lock，范围是(0,5];
2. 然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是(5,10)

• 当查询的记录不存在时，只会加next-key lock，然后会退化为间隙锁，也就是只会加一把锁。

select * from test where b = 7 for update

1. 先会对普通索引 b 加上 next-key lock，范围是(5,10];
2. 但是由于查询的记录是不存在的，所以不会再额外加个间隙锁，但是 next-key lock 会退化为间隙锁，最终加锁范围是 (5,10)。

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于普通索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

select * from test where b>= 3 and b < 9 for update

1. 最开始要找的第一行是 b = 5，因此 next-key lock(0,5]，但是由于 b 不是唯一索引，并不会退化成记录锁。
2. 但是由于是范围查找，就会继续往后找存在的记录，也就是会找到 b = 10 这一行停下来，然后加 next-key lock (5, 10]，因为是普通索引查询，所以并不会退化成间隙锁。
   所以，会有两个 next-key lock，分别是 (0, 5] 和(5, 10]。

7. 死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。

表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB

死锁产生的场景：

事务A *** 作 select * from table where id =1 for update
事务B *** 作 select * from table where id =2 for update
事务A *** 作 select * from table where id =2 for update
事务B *** 作 select * from table where id =1 for update

此时就卡住了事务A等着事务B释放锁,事务B等着事务A释放锁.

死锁排查：

1. 查看正在进行中的事务

SELECT * FROM information_schema.INNODB_TRX

show processlist// 查看当前正在进行中的进程

可以看到 进程id为3175 的事务在锁住了，而另一个id为3173的事务正在执行，但是没有提交事务

2. 查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3. 查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

4. 查看最近死锁的日志

show engine innodb status

线上死锁案例请看这一篇：https://blog.csdn.net/Prior_SX/article/details/123755889?spm=1001.2014.3001.5501

5. 解除死锁

如果需要解除死锁，有一种最简单粗暴的方式，那就是找到进程id之后，直接干掉。

• 查看当前正在进行中的进程

show processlist

// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;

• show processlist

// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;

6. 死锁优化建议

MySQL默认开启了死锁检测机制，当检测到死锁后会选择一个最小(锁定资源最少得事务)的事务进行回滚。

Innodb提供了wait-for graph算法来主动进行死锁检测，我们可以通innodb_deadlock_detect = on 打开死锁检测。mysql本身自带死锁检测超时时间在业务上是很难接受的

在InnoDB中，innodb_lock_wait_timeout的默认值是50s，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能较少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽可能低级别事务隔离（因为间隙锁只存在RR,RC不存在）