MYSQL锁的探索

最新推荐文章于 2024-08-23 20:00:57 发布

Yi Ian

最新推荐文章于 2024-08-23 20:00:57 发布

阅读量1.2k

点赞数 4

分类专栏： MYSQL 基础服务器框架文章标签： mysql 数据库后端数据库架构 1024程序员节

本文链接：https://blog.csdn.net/sanylove/article/details/126872249

版权

基础服务器框架同时被 2 个专栏收录

41 篇文章 4 订阅

订阅专栏

MYSQL

13 篇文章 1 订阅

订阅专栏

在MYSQL中，我们经常听到锁这个概念，那么MYSQL锁在加锁范围中主要分为：全局锁、表级锁和行锁三类，这三类锁细分如下图：

全局锁

全局锁就是给整个数据库实例加锁。可以通过执行下面指令加解锁：

flush tables with read lock //加锁

unlock tables //解锁

如果对数据库实例加了全局锁，那么整个数据库实例就处于只读状态。这个时候所有操作下面语句线程都处于堵塞的：

对数据的增删查改操作，比如 select、insert、delete、update等语句；

对表结构的更改操作，比如 alter table、drop table 等语句

当然还有一种情况会使全局锁失效（即主动解锁），那就是该会话断开了。

既然有全局锁，那么它典型使用场景是什么呢？答案全库逻辑备份。即在该备份期间，不会因为数据或者表结构的更新删除，而出现备份数据与预想的不一样。如我们购买商品，如果加了全局锁，不会在备份数据中出现用户钱没少，而库存少了等情况。

既然全局锁会使整个数据库实例就处于只读状态，那么它会带来什么影响呢？

在备份期间，如果备份在主库，那么在业务都不能执行更新，基本上处于停滞

在备份期间，如果备份在从库，那么从库也不能执行主库同步过来的binlog，而导致主从延迟

那么有没有方法可以避免这种加全局锁而影响业务的情况呢？

当然有了。如果数据库的引擎是支持事务的，那么在Repeatable Read（可重读）隔离级别中利用Read View（视图）和对MVCC（多版本并发控制）的支持来实现目标，具体如下：

在RR隔离级别中，在启动数据库备份之前，会先开启事务，而创建了一个Read View，之后的所有备份都是在这个Read View进行，而且有MVCC的支持，即使在这个过程中，有数据进行更新也可以正常进行。我们可以使用备份数据库的工具mysqldump 加 –single-transaction 参数来在备份数据库之前先开启事务，注意single-transaction只适用于所有的表使用事务引擎的库

说到这基本也了解完了，但是我们回过头发现我们是通过flush tables with read lock 加锁的，而且在MySQL数据库优化中我们还知道一个修改参数值的命令set global，那我们为什么不用set global readonly=true来加全局锁呢？原因如下：

1. 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大

2. 在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

表级锁在InnoDB 有 5 种表级锁：IS（意向读锁）、IX（意向写锁）、S（读锁）、X（写锁）、AUTO- INC。它们的兼容性，如下（+为兼容，-为不兼容）：

表锁

表锁的语法：

lock tables … read/write

如果加了表锁，除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。也就是说如果线程A执行了“lock tables t1 read”，那么本线程对t1表执行写操作都会被阻塞，其他线程对表t1写操作也会被阻塞，直到线程A释放锁。释放锁的办法有：

unlock tables 主动释放锁

会话退出（客户端断开连接）时自动释放

从上面介绍我们知道表锁的颗粒度太大，会影响并发性，所以 InnoDB 引擎的表尽量避免使用表锁，因为InnoDB实现了颗粒度更小的行锁，这也是InnoDB 引擎牛逼的地方~

元数据锁（MDL）

MDL不需要显式使用，在访问一个表的时候会被自动加上。它的作用就是为了保证当用户对表执行 CRUD（增删改查）操作时，防止其他线程对这个表结构做了变更，从而保证了读写的正确性。那么怎么加锁呢？如下：

对一张表进行 CRUD 操作时，加的是 MDL 读锁

对一张表做结构变更操作的时候，加的是 MDL 写锁

MDL 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查

MDL读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

那么这MDL 锁什么时候释放呢？

MDL 锁是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

MDL 锁容易发生的事故：

如果数据库有一个开启长事务一直不提交（或者很久才提交），那么当对表结构进行修改的时候，会出现意想不到的事故现场。如线程A开启了一个事务（一直不提交），然后执行select语句，而获对表加上 MDL 读锁；此时线程B要修改表结构，由于线程A一直不释放MDL 读锁，所以线程B会给一直堵塞着，直到线程A释放MDL 读锁。在线程B堵塞中，后面所有对该表的操作都会堵塞，因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。

MDL 锁发生的事故解决方案：

1. 首先要解决长事务，事务不提交，就会一直占着DML锁。通过show processlist或者performance_schema.data_locks（在MySQL5.7版本中，也可以使用sys.innodb_lock_waits试图查看，但是在MySQL8.0中，该视图连接查询的表不同(把之前版本中使用的information_schema.innodb_locks表和information_schema.innodb_lock_waits)表替换为了performance_schema.data_locks和performance_schema.data_lock_waits）表，可以查到当前执行的事务。如果要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务

2. 如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而又不得不加个字段，该怎么做？在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程

MDL 类型,如下：

MDL 类型的兼容性如下(+为兼容，-为不兼容)：

意向锁

意向锁的目的是为了快速判断表里是否有记录被加锁，从而减少遍历表里所有记录判断是否有锁的问题。

那么怎么加锁呢？

1. 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；

2. 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」

例如：

select ... lock in share mode; // 先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... for update; //先表上加上意向独占锁，然后对读取的记录加独占锁
update ... set ....; // 插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁

注意！意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

AUTO-INC 锁

AUTO-INC 锁是特殊的表锁，什么情况下会有AUTO-INC 锁呢？

在表机构中有某个字段是被声明 AUTO_INCREMENT 属性的，在我们要向该表中插入数据时，可以不指定该字段值，让数据库自动给该字段赋值递增的值，这就会产生 AUTO-INC 锁。

AUTO-INC 锁的生产与释放：

在插入数据时，会加一个表级别的 AUTO-INC 锁；而在执行完插入语句后就会立即释放，不需要等到事务提交之后

AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。那怎么控制和优化呢：

innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁

innodb_autoinc_lock_mode = 2，就采用轻量级锁

innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁

轻量级锁就是InnoDB 存储引擎提供的一个现自增锁。即在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。但是轻量级锁在并发插入数据的时候，自增长的值可能不是连续的。从而在主从赋值的场景中是不安全的。

行锁

行级锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；

Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身，即间隙锁是前开后开区间；

Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身，即next-key lock 是前开后闭区间

行锁就是针对数据表中行记录的锁，由各个引擎自己实现的，所以并不是所有引擎都支持行锁，比如MyISAM引擎。同时行锁的加锁的基本单位是 next-key lock，但在某些场景下next-key lock会退化为Record Lock或者Gap Lock。

注意！在update语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

行级锁的兼容性，如下(+为兼容，-为不兼容)：

下面对着三类行级锁进行实验，分别从唯一索引等值查询，唯一索引范围查询，非唯一索引等值查询，非唯一索引范围查询进行分析，准备数据如下：

注意，不同的MYSQL版本加锁规则是不一样的

在进行测试之前，我们要知道普通的 select 语句是不会对记录加锁的，要加锁我们需要显示调用：

select ... lock in share mode； //对读取的记录加共享锁

select ... for update； //对读取的记录加排他锁

但是一旦事务提交了，锁就会给释放了，因此我们在mysql之前使用 begin、start transaction 或者 set autocommit = 0，来实现自动提交事务。

唯一索引等值查询

当我们使用唯一索引等值查询的时候，加锁的规则会随记录是否存在而不同：

当记录存在时，next-key lock 会退化成「记录锁」

当记录不存在时，next-key lock 会退化成「间隙锁」

记录存在时的实验

实验图如下：

解析：

1. 加锁的基本单位是 next-key lock，因此会话1的加锁范围是(8, 16];

2. 但是由于是用唯一索引进行等值查询，且查询的记录存在，所以 next-key lock 退化成记录锁，因此最终加锁的范围是 id = 16 这一行

结论：会话 2 在修改 id=16 的记录时会被锁住，而会话 3 插入 id=9 的记录不会被锁住。

记录不存在时的实验

实验图如下：

解析：

1. 加锁的基本单位是 next-key lock，因此会话1的加锁范围是(8, 16];

2. 但是由于查询记录不存在，next-key lock 退化成间隙锁，因此最终加锁的范围是 (8,16)

结论：会话 2 插入 id=9 的记录会被锁住，会话 3 修改 id =16 记录不会被锁住。

唯一索引范围查询

范围查询和等值查询的加锁规则是不同的。即虽然可能两条语句查询的结果是一样的，但是加锁的范围是不一样的。

实验图如下：

解析：

1. 最开始要找的第一行是 id = 8，因此 next-key lock(4,8]，但是由于 id 是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 8 这一行加锁；

2.由于是范围查找，就会继续往后找存在的记录，也就是会找到 id = 16 这一行停下来，然后加 next-key lock (8, 16]

结论：会话 1 这时候主键索引的锁是记录锁 id=8 和间隙锁(8, 16]

非唯一索引等值查询

当我们使用非唯一索引等值查询的时候，加锁的规则会随记录是否存在而不同：

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

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

记录存在时的实验

实验图如下：

解析：

1. 先会对普通索引 b 加上 next-key lock，范围是(4,8];

2.然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是(8,16)

结论：会话1的普通索引 b 上共有两个锁，分别是 next-key lock (4,8] 和间隙锁 (8,16)

记录不存在时的实验

实验图如下：

解析：

1. 先会对普通索引 b 加上 next-key lock，范围是(8,16];

2. 但是由于查询的记录是不存在的，所以不会再额外加个间隙锁，但是 next-key lock 会退化为间隙锁，最终加锁范围是 (8,16)。

结论：会话 2 插入了 b = 9 的记录会被锁住，而会话 3修改b = 16记录不会被锁住。

非唯一索引范围查询

非唯一索引的加锁时，next-key lock 不会退化为间隙锁和记录锁。

实验图如下：

解析：

1. 最开始要找的第一行是 b = 8，因此 next-key lock(4,8]，但是由于 b 不是唯一索引，并不会退化成记录锁

2.但是由于是范围查找，就会继续往后找存在的记录，也就是会找到 b = 16 这一行停下来，然后加 next-key lock (8, 16]，因为是普通索引查询，所以并不会退化成间隙锁

结论：会话 1 的普通索引 b 有两个 next-key lock，分别是 (4,8] 和(8, 16]