文江博客开发文档 MySQL 高级教程文章详情

文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

锁

发布于 2024-08-13 19:52:40 字数 11037 浏览 0 评论 0 收藏 0

锁是一种用于管理共享资源的并发访问的机制，一般都由数据库或存储引擎根据当前的事务隔离级别自动添加，我们只需要了解它的机制即可。

MySQL 几种并发情况

读-读并发

不会对数据有影响，不需要处理。

写-写并发

会发生脏写问题，SQL 规范的任何一个隔离级别都用锁解决了这个问题。

读-写并发

可能发生脏读、 不可重复读 、幻读的问题

读-写并发问题的解决方案

读写都加锁（读写操作彼此需要排队执行，性能低，但是读出来的数据始终是最新版本）
读操作采用 MVCC，写操作加锁（读写操作不冲突，性能高，无法保证读出来的数据最新）

锁的分类

从数据的操作类型划分

共享锁/读锁/S 锁 :多个事务的读操作可以同时进行，不会相互影响也不会相互阻塞。
排他锁/写锁/X 锁 :当前的写操作没有完成前，会阻塞其他写锁和读锁，确保在一段时间内，只有一个事务能执行写入，并且防止其他事务读取正在写入的资源。同理，在当前读操作执行时，也会阻塞其他的写操作。

在 InnoDB 中，共享锁和排他锁既可以作用在表上，也可以作用在行上。

读操作可以加共享锁和排他锁，写操作一般只加排他锁。

	共享锁	排他锁
共享锁	兼容	不兼容
排他锁	不兼容	不兼容

给读操作加锁

给读操作加共享锁

SELECT ... LOCK IN SHARE MODE;
# 或
SELECT ... FOR SHARE; #(8.0 新增语法)
# 加的是表锁

给读操作加排他锁

SELECT ... LOCK IN UPDATE MODE;
# 或
SELECT ... FOR UPDATE; #(8.0 新增语法)
# 加的是表锁

MySQL8.0 新特性:

在 5.7 及之前的版本，执行 SELECT ... FOR UPDATE ，如果获取不到锁，会一直等待，直到超时（ innodb_lock_wait_timeout 变量）。在 8.0 版本后，添加 NOWAIT 、 SKIP LOCKED 语法，跳过锁等待，或者跳过锁定。（ SELECT ... FOR UPDATE NOWAIT ）

NOWAIT:如果查询的行已经加锁，会立即报错返回
SKIP LOCKED:如果查询的行已经加锁，只返回结果中不包含被锁定的行

给写操作加锁

给写操作加排他锁

DELETE/INSERT/UPDATE ... LOCK IN UPDATE MODE;
# 或
SELECT ... FOR UPDATE; #(8.0 新增语法)
# 加的是表锁

写操作和锁的具体执行过程

DELETE:对一条记录做 DELETE 操作的过程其实是先在 B+树中定位到这条记录的位置，然后获取这条记录的 X 锁，再执行 delete mark 操作。
UPDATE:在对一条记录做 UPDATE 操作时分为三种情况:
- 情况 1:未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化。则先在 B+树中定位到这条记录的位置，然后再获取一下记录的 X 锁，最后在原记录的位置进行修改操作。
- 情况 2:未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化。则先在 B+树中定位到这条记录的位置，然后获取一下记录的 X 锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。新插入的记录由 INSERT 操作提供的隐式锁进行保护。
- 情况 3:修改该记录的键值，则相当于在原记录上做 DELECT 操作之后再来一次 INSERT 操作。
INSERT:一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

从锁的粒度划分

表锁 :锁定整张表，是 MySQL 的基本锁策略，不依赖于存储引擎。 锁的粒度最大，冲突概率高，并发度低，开销低，不容易死锁 。
行锁 :锁定某一行，依赖于存储引擎实现。 锁的粒度最小，冲突概率低，并发度高，开销大，容易出现死锁 。
页锁 :锁定某一页， 各项属性介于表锁和行锁之间 。

每个层级的锁的数量是有限制的。由于锁会占用内存，锁空间的大小也有限制。当某个层级的锁的数量超过这个层级的上限时，就会进行 锁升级 ，即用大粒度的锁取代小粒度的锁，从而降低锁空间的内存占用，但是会 降低并发度 。

不同粒度的锁之间不能共存。

表锁分类

共享锁、排他锁
InnoDB 对某个表执行 DML 语句时（CRUD），不会自动添加表级的 S 锁和 X 锁；执行 ALTER TABLE 之类的 DDL 语句时，阻塞其他的 DML 语句；同理，执行 DML 语句也会阻塞 DDL 语句。而 MyISAM 在执行查询语句前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。
手动给表加锁: lock tables t read/write （一般不用）
查看加锁的表: show open tables where in_use > 0
手动解锁所有表: unlock tables
意向锁（intention lock）
意向锁是一种特殊的表锁，它 可以和行锁共存 。 意向锁的作用是让粒度更高的锁知道其中是否上过粒度小的锁（因此意向锁相互之间都是兼容的） 。如果没有意向锁，当一个事务想要给一张表加表锁时，需要遍历该表的所有行，查看其中是否有行锁。
当我们给某一行数据加上行锁时，会自动给更高粒度的空间（页、表）上一个意向锁。这样当其他事务需要给这个空间上更高粒度的锁时，就不用再遍历了。
意向锁也分为意向共享锁，意向排他锁，自动根据行锁的类型进行选择。
自增锁（auto inc）
当表中有自增字段（auto increment）时，为了确保自增字段是连续自增的，就需要自增锁来实现。当执行插入时，就会自动添加一个表级的自增锁，执行完毕后再释放。由于每条插入语句都需要参与自增锁的竞争，并发度很低，所以可以通过 innodb_autoinc_lock_mode 变量来改变锁定机制。
MySQL 的插入分成三种:简单插入、批量插入、混合插入。
简单插入 是指可以预先知道插入的行数的语句，例如没有嵌套子查询的 insert；
批量插入 是指不能预先知道插入的行数的语句，例如嵌套子查询的 insert；
混合插入 与简单插入类似，但是部分数据手动指定了自动递增字段的值。
```
innodb_autoinc_lock_mode = 0
```
传统的模式，每个插入语句都添加一个表级自增锁。
```
innodb_autoinc_lock_mode = 1
```
MySQL8.0 之前的默认值。在这种情况下，批量插入仍然使用自增锁，但是简单插入则使用 mutex （轻量级锁，只在分配过程中保持）来获取所需数量的自动低增值。
```
innodb_autoinc_lock_mode = 2
```
MySQL8.0 后的默认值。在这种情况下，所有的插入语句都不会使用自增锁，但是执行批量插入时，生成的自增字段的值可能不连续。
元数据锁（DML）
元数据锁的作用是保证读写的正确性不被表结构影响。
当对表做 CRUD 操作时，自动加元数据读锁；当对表结构做变更操作时，自动加元数据写锁。
读锁与读锁兼容，读锁与写锁、写锁与写锁不兼容。

行锁分类

记录锁（record locks）
字面意思，给一条行记录加锁，也是最常用的锁。记录锁也分为读锁和写锁，规则与表级的相同。
间隙锁（gap locks）
间隙锁用于解决幻读问题（也可以用 MVCC 解决）。
插入间隙锁后，不允许其他事务在两条记录之间插入新数据。
临键锁（Next-key locks）
相当于是记录锁和间隙锁的结合体，是 InnoDB 的默认锁。
插入意向锁（insert intention locks）
MySQL InnoDB 中的锁-插入意向锁（Insert Intention Lock）小厂程序员的博客-CSDN 博客插入意向锁

从对待锁的态度划分

悲观锁 :总是假设最坏的情况，每次拿数据时都会加锁。例如行锁、表锁、读锁、写锁等。
乐观锁 :认为并发操作是小概率事件，不对操作加锁，而是在更新时判断在此期间数据有没有被改动。可以通过版本号或 CAS 机制实现。（JUC 的 atomic 就是通过 CAS 实现的）

悲观锁和乐观锁是锁的设计思想，而不是具体的某个锁。

乐观锁版本号机制

在表中设计一个 version 字段，对行数据的更新操作执行都执行以下步骤:

读取行数据和 version 的值。
在内存中对行数据进行操作。
再次读取 version 的值。
将 version 在 3 中的值与 1 中的值进行比较，如果相同则将行数据更新到磁盘，并且把磁盘中的 version 值+1；如果不同则从 1 重新开始。

两种锁的适用场景

乐观锁:适合读多写少。
悲观锁:适合写多读少。

从加锁的方式划分

显式锁 :（存储引擎或数据库自动生成、手动添加）创建锁结构来起到锁的作用。
隐式锁 :不创建锁结构，也可以起到锁的作用。

隐式锁的主要应用场景是插入语句。每条行记录（聚簇索引的叶子节点）中都有一个 trx_id 属性，表示最近对这条记录进行操作的事务的 id。如果有事务 2 要对这条数据添加锁，会先看这条记录的 trx_id 表示的事务 1 是否处于活跃状态。如果是，则表明该条数据还在被事务 1 操作中，那么事务 2 会帮其创建一个锁，并且自身进入等待事务 1 的状态中。这种情况就是隐式锁转化为显式锁。

参考: MySQL InnoDB 隐式锁功能解析 - 知乎 (zhihu.com)

全局锁

对整个数据库进行加锁，让整个库处于只读状态。

使用场景:全库逻辑备份。

死锁

两个事务互相持有对方需要的锁，并且等待对方释放，双方都不会释放自己的锁。

产生死锁的必要条件

两个或以上的事务
每个事务都已经持有锁并且申请新的锁
锁资源同时只能被同一个事务持有或者不兼容
事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于每个事务加锁的顺序不一致。如果一致，不会形成死锁。

解决死锁的方法

等待，直到超时
两个事务相互等待时，当一个事务等待事件超过阈值时，就将其回滚，从而释放锁，让另一个事务继续执行。通过 innodb_lock_wait_timeout 设置等待时间，默认 50s。
缺点:等待时间不好设置，太长影响业务正常执行，太短容易误伤正常事务的的等待。
使用死锁检测进行处理
使用 wait-for graph 算法检测死锁。 innodb_deadlock_detect 开启或关闭。
构建出以事务为点，锁为边的有向图，如果图中存在环，则存在死锁。innobb 引擎就会选择回滚 undo 量最小的事务，让其他事务继续执行。
缺点:算法本身需要耗费时间，如果同时并发的事务太多，会影响性能。
解决方法:用其他中间件对更新相同行的操作进行排队。

如何避免死锁

合理设计索引，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
调整业务 SQL 执行顺序，避免 update/delete 等长时间持有锁的 SQL 在事务前面。
避免大事务，尽量拆分成多个小事务处理。
降低隔离级别。
在并发高的场景下不要在事务中手动加锁。

锁的内存结构

给一条记录加锁的本质就是在内存中创建一个与之关联的锁结构。

加锁时，并不会对每条记录都创建一个锁结构，而是为了节约空间，将满足一些条件的记录都用同一个锁结构表示:

同一个事务中的加锁操作
被加锁的记录在同一个页中
加锁的类型一样
等待状态一样

结构解析

锁所在的事务信息:记录锁的基础信息的指针。
索引信息:（行锁特有）记录加锁的记录的索引信息的指针。
表锁/行锁信息:
- 表锁:记录当前表和一些其他信息。
- 行锁:记录当前行所在的表空间(Space ID)、页号(Page Number)、行标记(n_bits)。
type_mode:
一个 32 位的数，被分为 lock_mode 、 lock_type 、 rec_lock_type 三个部分。
- lock_mode:表示当前锁的模式
  - LOCK_IS（十进制的 0）:表示共享意向锁，也就是 IS 锁。
  - LOCK_IX（十进制的 1）:表示独占意向锁，也就是 IX 锁。
  - LOCK_S（十进制的 2）:表示共享锁，也就是 S 锁。
  - LOCK_X（十进制的 3）:表示独占锁，也就是 X 锁。
  - LOCK_AUTO_INC（十进制的 4）:表示 AUTO-INC 锁。
- lock_type:表示当前锁的类型
  - LOCK_TABLE（十进制的 1，即第 1 个 bit 为 1）:表示表级锁
  - LOCK_REC（十进制的 2，即第 2 个 bit 为 1）:表示行级锁
- rec_lock_type:行锁的具体类型
  - LOCK_ORDINARY（十进制的 0）:表示 next-key 锁。
  - LOCK_GAP（十进制的 512，即第 10 个 bit 为 1）:表示 gap 锁。
  - LOCK_REC_NOT_GAP（十进制的 1024，即第 11 个 bit 为 1）:表示记录锁。
  - LOCK_INSERT_INTENTION（十进制的 2048，即第 11 个 bit 为 1）:表示插入意向锁。
  - 补充:十进制的 1，即第 1 个 bit 为 1 时，表示 is_waiting=true，即当前事务处在等待状态，尚未获取到锁；为 0 时，表示 is_waiting=false，即当前事务获取锁成功。
其他信息:为了更好的管理各种锁结构而设计的哈希表和链表。
比特位:如果是行锁结构的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性表示的。InnoDB 数据页中的每条记录在记录头信息中都包含一个 heap_no 属性，伪记录 Infimum 的 heap_no 值为 0，Supremum 的 heap_no 值为 1，之后每插入一条记录，heap_no 值就增 1。锁结构最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no，即一个比特位映射到页内的一条记录。

锁的监控

mysql> show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits :当前正在等待锁定的数量；
Innodb_row_lock_time :从系统启动到现在锁定总时间长度；（等待总时长）
Innodb_row_lock_time_avg :每次等待所花平均时间；（等待平均时长）
Innodb_row_lock_time_max :从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits :系统启动后到现在总共等待的次数；（等待总次数）

其他监控方法:

MySQL 把事务和锁的信息记录在了 information_schema 库中，涉及到的三张表分别是 INNODB_TRX 、 INNODB_LOCKS 和 INNODB_LOCK_WAITS 。

MySQL5.7 及之前，可以通过 information_schema.INNODB_LOCKS 查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

MySQL8.0 删除了 information_schema.INNODB_LOCKS，添加了 performance_schema.data_locks ，可以通过 performance_schema.data_locks 查看事务的锁情况，和 MySQL5.7 及之前不同，performance_schema.data_locks 不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁。

同时，information_schema.INNODB_LOCK_WAITS 也被 performance_schema.data_lock_waits 所代替。

分享到QQ

分享到微博