MySQL-锁

目录

锁

详细说一下MySQL数据库中锁的分类 （数据库中的行锁，表锁，读锁，写锁均为悲观锁）

Innodb 存储引擎中的行级锁有哪些?

意向锁是表级锁

MySQL 的乐观锁和悲观锁了解吗？

在线上修改表结构，会发生什么？

一条Update语句没有带where条件，加的是什么锁?（全表扫描）

带了where条件没有命中索引，加的是什么锁?

两条更新语句更新同一条记录，加的是什么锁?

两条更新语句更新同一条记录的不同字段，加的是什么锁?

MySQL 是怎么加锁的？

唯一索引等值查询（对索引加锁）主键

唯一索引范围查询

非唯一索引等值查询

非唯一索引范围查询

没有加索引的查询 （全表扫描 加临键锁）

可重复读场景下可能发生死锁 （间隙锁可以兼容） 间隙锁的主要目的是防止其他事务在间隙中插入新记录，而不是排他性地锁定间隙 插入操作之前需要先获取到插入意向锁

mysql死锁场景

什么是两阶段锁，有什么意义？

了解过 MySQL死锁问题吗?

MySQL怎么排查死锁问题?

MySQL怎么避免死锁?

---

锁

详细说一下MySQL数据库中锁的分类 （数据库中的行锁，表锁，读锁，写锁均为悲观锁）

根据锁粒度的不同，MySQL的锁可以分为全局锁、表级锁、行级锁

我比较熟悉的是表级锁和行级锁，比如我们对一张表结构进行修改的时候，MySQL就会对这张表加一个元数据锁，元数据锁是属于表级锁的。

行级锁目前只有 Innodb 存储引擎实现了，MyISAM 存储引擎是不支持行级锁的，只有表锁。

Innodb 存储引擎实现的行级锁主要有记录锁、间隙锁、临键锁、插入意向锁这些，我们对表记录进行 select for update，或者增删改的时候，都会对记录加行级锁。

---------------------------------------------------------------------------------------

• 全局锁：通过flush tables with read lock 语句会将整个数据库就处于只读状态了，这时其他线程执行以下操作，增删改或者表结构修改都会阻塞。全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

• 表级锁：MySQL 里面表级别的锁有这几种：

  • 表锁：通过lock tables 语句可以对表加表锁，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

  • 元数据锁（表结构）：当我们对数据库表进行操作时，会自动给这个表加上 MDL，对一张表进行 CRUD 操作时，加的是 MDL 读锁；对一张表做结构变更操作的时候，加的是 MDL 写锁；MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

  • 意向锁：当执行插入、更新、删除操作，需要先对行加上「意向独占锁」，然后对该记录加独占锁。意向锁的目的是为了快速判断表里是否有记录被加锁。（协调行级锁但是为表级锁）

• 行级锁：InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

  • 记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的，满足读写互斥，写写互斥

  • 间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

  • Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

  • 插入意向锁（间隙锁）：处理多个事务尝试插入同一行数据时的兼容性锁，提高并发插入性能。（共享锁）

Innodb 存储引擎中的行级锁有哪些?

Innodb 实现的行级锁有记录锁、间隙锁（左开右开）、临键锁（左开右闭 间隙+记录）、插入意向锁。我们在使用增删改或者锁定读语句的时候，都会对记录加行级锁。

• Record Lock 记录锁

记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。

例如select * from t where id =6 for update;就会将id=6的记录锁定。

• Gap Lock 间隙锁

间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。

间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。

例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。

• Next-key Lock 临键锁

临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。

• Insert Intention Lock 插入意向锁

一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁 ，如果有的话，插入操作需要等待，直到拥有 gap 锁 的那个事务提交。但是事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个 间隙 中插入新记录，但是现在在等待。这种类型的锁命名为 Insert Intention Locks ，也就是插入意向锁 。两个插入意向锁锁不会互斥

-----------------------------------------------------------------------------

mysql 默认行锁类型就是临键锁(Next-Key Locks)。

当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

意向锁是表级锁

意向锁是一个表级锁，不要和插入意向锁搞混。

意向锁的出现是为了支持 InnoDB 的多粒度锁，它解决的是表锁和行锁共存的问题。

当我们需要给一个表加表锁的时候，我们需要根据判断表中有没有数据行被锁定，以确定是否能加成功。

假如没有意向锁，那么我们就得遍历表中所有数据行来判断有没有行锁；

有了意向锁这个表级锁之后，则我们直接判断一次就知道表中是否有数据行被锁定了。

有了意向锁之后：

要执行的事务 A 在申请行锁（写锁）之前，数据库会自动先给事务 A 申请表的意向排他锁。

当事务 B 去申请表的互斥锁时就会失败，因为表上有意向排他锁之后事务 B 申请表的互斥锁时会被阻塞。

MySQL 的乐观锁和悲观锁了解吗？

• 乐观锁（Optimistic Concurrency Control）

乐观锁认为数据的变动不会太频繁。

可以在数据库表增加一个版本号字段，利用这个版本号字段在数据库中实现乐观锁。

具体的实现，每次更新数据的时候，都要带上版本号，同时将版本+1，比如现在要更新id=1，版本号为2的记录，这时候先要获取id=1的版本号，然后更新。

如果这个版本号与表记录中的版本号一致的话，就能更新成功，如果不相等则不进行更新，然后需要重新获取该记录的最新版本号，然后再尝试更新数据。

• 悲观锁（Pessimistic Concurrency Control）：

悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

数据库中的行锁，表锁，读锁，写锁均为悲观锁。

update table set name = "小明"， version = version+1 where id = 1 and version = 2.

在线上修改表结构，会发生什么？

线上环境可能存在很多事务都在读写这张表，如果对这张表进行了表结构修改，就会发生阻塞，原因是有事务对这张表进行读写操作的时候，会生成元数据读锁，而修改表结构的时候，会生成元数据写锁，这时候就产生了读写冲突，所以修改表结构的操作就会阻塞，并且后续事务的增删改操作都会阻塞。

一条Update语句没有带where条件，加的是什么锁?（全表扫描）

• Innodb 加锁的对象是索引，可重复读级别下，加锁的基本单位是next-key锁，读已提交隔离级别下，加锁的基本单位是记录锁。

• 可重复读隔离级别下，更新没有带 where 条件，会全表扫描，会对每一条记录都加next-key锁，相当于锁住了全表；

• 读已提交隔离级别下，没有间隙锁，更新没有带 where 条件，是全表扫描，那么会对每一条记录都加记录锁。

带了where条件没有命中索引，加的是什么锁?

没有命中索引，是全表扫描

在可重复读级别下，全表扫描的话，会对每一条记录都加next-key锁

在读已提交隔离级别下，因为没有间隙锁，全表扫描的时候，会对每一条记录都加记录锁

两条更新语句更新同一条记录，加的是什么锁?

在可重复读级别下，可能有这些情况

• 如果更新条件的字段是唯一索引，还要看更新的记录是否存在:

  • 如果存在，那么这条记录加的记录锁，只锁住该条记录;

  • 如果这条记录不存在，则加间隙锁。

• 如果更新条件的字段是非唯一索引，还要看更新的记录是否存在:

  • 如果存在，由于非唯一索引会存在相同值的记录，所以非唯一索引等值查询，实际上是一个扫描的过程，那么会针对符合更新条件的二级索引记录，加next-key锁，最后扫描到第一个不符合更新条件的二级索引记录就会停止扫描，然后对第一个不符合更新条件的记录加间隙锁，同时，在符合更新条件的记录的主键索引上加记录锁。

  • 如果不存在，会对第一个不符合更新条件的二级索引记录加间隙锁

• 如果更新条件的字段是没有索引或者没有命中索引，那么就是全表扫描，会对每一条记录都加next-key锁.

第二条更新语句在记录存在的情况下，因为记录被第一条更新语句加了记录锁/next-key锁，所以要等待锁资源释放时，第二条更新语句才能按照相同规则加锁。当记录不存在时，由于间隙锁可以兼容，所以直接加上间隙锁

两条更新语句更新同一条记录的不同字段，加的是什么锁?

Innodb 加锁是加在行记录索引上的，不是针对更新的字段加锁。所以是不是更新同一个字段，没有关系。只要在这条记录上有更新操作，就会对这条记录加锁。所以这题的加锁的情况，也跟上一题一样。

MySQL 是怎么加锁的？

唯一索引等值查询（对索引加锁）主键

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

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。

• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

唯一索引范围查询

范围查询和等值查询的加锁规则是不同的。

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

1. 针对「大于」的范围查询的情况（不会退化）

• 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。

• 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：

  • 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

  • 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；

  • 如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。 （间隙锁+记录锁）

非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

没有加索引的查询 （全表扫描 加临键锁）

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

可重复读场景下可能发生死锁 （间隙锁可以兼容） 间隙锁的主要目的是防止其他事务在间隙中插入新记录，而不是排他性地锁定间隙 插入操作之前需要先获取到插入意向锁

事务 A 和事务 B 在执行完后 update 语句后都持有范围为(20，30) 的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件: 互斥、占有且等待、不可强占用、循环等待，因此发生了死锁

mysql死锁场景

• 行锁导致死锁 同时请求锁定相同行

• gap lock/next keys lock导致死锁 同时持有间隙锁，获取插入意向锁

• index merge 导致死锁

• 唯一索引冲突导致死锁

注：以下场景隔离级别均为默认的Repeatable Read；

什么是两阶段锁，有什么意义？

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

知道了这个设定，对我们使用事务有什么帮助呢？

那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

在加锁阶段，事务持有的锁不允许释放，以确保事务的操作不会被其他并发事务干扰。只有在解锁阶段，事务才开始逐步释放锁，使其他事务能够获得这些锁并执行操作。（悲观锁）

了解过 MySQL死锁问题吗?

在并发事务中，当两个事务出现循环资源依赖，这两个事务都在等待别的事务释放资源时，就会导致这两个事务都进入无限等待的状态，这时候就发生了死锁。

排查死锁的一般步骤是这样的：

（1）查看死锁日志 show engine innodb status;

（2）找出死锁 sql

（3）分析 sql 加锁情况

（4）模拟死锁案发

（5）分析死锁日志

（6）分析死锁结果

MySQL怎么排查死锁问题?

在遇到线上死锁问题时，我们应该第一时间获取相关的死锁日志。我们可以通过show engine innodb status命今来获取死锁信息

然后就分析死锁日志。

死锁日志通常分为两部分，上半部分说明了事务1在等待什么锁，下半部分说明了事务2当前持有的锁和等待的锁。

通过阅读死锁日志，我们可以清楚地知道两个事务形成了怎样的循环等待，然后根据当前各个事务执行的SQL分析出加锁类型以及顺序，逆向推断出如何形成循环等待，这样就能找到死锁产生的原因了。

MySQL怎么避免死锁?

实际上死锁是不能完全避免的，只要会加锁，在并发的场景就会发生死锁，但是我们可以通过一些手段，降低发生死锁的概率。

1. MySQL的锁是在事务提交的时候才会释放的，所以可以通过缩短锁持久的时间，来降低死锁的概率，比如:

   1. 如果事务中需要锁多个行，要把最可能造成锁冲突的锁的申请时机尽量往后放，这样事务的持久锁的时间就会比较短。

   2. 避免大事务，尽量将大事务拆成多个小事务来处理，因为大事务占用耗时长，意味着占用锁占用时间长，与其他事务冲突的概率也会变高;

2. 可以通过减少间隙锁，来降低死锁的概率:

如果能确定幻读和不可重复读对应用的影响不大，可以考虑将隔离级别改成 RC，因为 RC 隔离级别没有间隙锁，可以避免间隙锁导致的死锁;

可以通过减少加锁范围，来降低死锁的概率:

给表添加合理的索引，如果不走索引将会为表的每一行记录加行级锁，死锁的概率就会大大增大;

可以通过MySQL参数设置，来降低死锁的概率:

设置合适的锁等待超时阈值，当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了。

开启主动死锁检测，主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

自己整理，借鉴很多博主，感谢他们

原文地址：https://blog.csdn.net/m0_50846237/article/details/140390130

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