MySQL全局锁和表锁

小咪咪 2023-10-06 18:53 11阅读 0赞

#### MySQL全局锁和表锁 ####

*  前言
 *  全局锁
 *   *  思考
 *  表级锁
 *   *  1 表锁
     *  2 元数据锁(metadata lock, MDL)
     *  思考
     *   *  如何安全的给小表加字段
         *  要变更的表是热点表，数据不大，但请求频繁，现在不得不加字段，该怎么办？

## 前言 ##

数据库锁设计的初衷是为了处理并发问题。

作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源访问的规则，锁就是用来实现这些访问规则的重要数据结构。

在MySQL里锁分为三类，全局锁，表级锁，行锁。

## 全局锁 ##

全局锁就是对整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是 `Flush tables with read lock (FTWRL)`，使用了全局锁，会让整个库处于只读状态。

我们只要一听到整个库加锁，就感觉很危险。为什么这样讲呢？

1、如果在主库上进行备份的话，备份期间更新操作是无法执行的，业务基本上就处于停摆状态；  
2、如果在从库上进行备份，备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

这时你可能会想，既然这么不好，为什么还要使用全局锁呢？

**全局锁的典型使用场景是用来做全库备份使用。**设想一下，不加全局锁会有什么问题？

比如：

用户买东西，首先会从余额里扣除金额，然后在订单里添加商品。

如果备份数据库，不加锁，并且备份顺序为先备份用余额，再备份订单商品，有可能备份了用户余额后，用户下订单买东西提交事务，然后再备份订单商品表， 此时订单商品已存在。

最后备份出来的数据为。最后用户余额为买东西前的余额，没有减少，但是订单商品却多了。

也就是说，**如果不加锁，不同表之间执行顺序不同，进而导致备份时间不同，此时备份系统可能会得到不是一个时间的点的数据，视图是不一致的。**

看到这里，是不是会想到一个机制，我们让视图一致不就好了吗？

是的，说的没错。保证视图一致的事务隔离级别是什么？

可重复读。但是不要忘记了，不是所有的引擎都是支持事物的，所以也就是说，**不支持事务的引擎没有办法使用可重复读隔离级别，来保证一致性读。**

官方自带的逻辑备份工具是 MySQLdump， 当mysqldump 使用参数 --single-transaction 时候，会启用一个事务，来确保拿到一致性视图。

### 思考 ###

既然是全库只读，为什么不使用set global readonly=true的方式？

1、在有些系统中，readonly是被用作其他逻辑使用的, 比如判断一个库是否为主库还是备库, 修改 global 变量方式影响太大。

2、异常处理机制上有差异.如果FTWRL命令执行之后客户端发生异常断开, MySQL会自动释放这个全局锁, 整个库是可以正常更新的状态。

但是如果设置了readonly，即使发生异常，数据库会一直保持只读状态，长时间处于不可写的状态，风险极大。

## 表级锁 ##

MySQL中，表级锁有两种，一种是表锁，一种是元数据锁。

### 1 表锁 ###

表锁的语法是 lock tables 表名 read、write，可以使用unlock tables 进行主动释放锁，也可以在客户端断开时自动释放。

lock tables 语法除了会限制别的线程读写外，也会限制本线程的操作对象。

例子：

在某个线程A 中执行 lock tables t1 read， t2 write；这个语句，其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables 之前，也只能执行读 t1， 读写t2的操作。写t1都是不允许的。

**可以理解为写是排它锁，写锁意味着其他线程不能读也不能写。读锁是共享锁，加上后其他锁只能读，不能写，本线程也不能写。**

### 2 元数据锁(metadata lock, MDL) ###

MDL 不需要显式使用, 在访问一个表时会自动加上。MDL的作用是，保证读写的正确性。

如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

**MDL 是server层的表级锁，也是表结构锁，主要是用于隔离 DML和DDL操作之间的干扰。对一个表进行正删改查操作的时候，加MDL读锁；对一个表做表结构变更的时候，加MDL写锁。（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）**

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

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

### 思考 ###

**这里我会产生一个疑问，为什么读锁之间不互斥，其他线程还可以进行增删改查？**

因为在对一个表进行增删改查的时候，系统会自动加上一个MDL读锁，这个读锁是表结构的读锁，增删改查并不会改变表结构，读锁自然会不会进行互斥，多个线程可以同时进行增删改查操作；

这个时候如果加了读锁，其它线程中有需要改变表结构的，这时改表结构的线程会加上一个MDL写锁，现在读锁和写锁就会进行互斥，所以读锁加了之后，写锁是需要等待的，同理，加了写锁，读锁也需要等待的，其他的写锁也是需要等待的。

**为什么我给一个小表加个字段，导致整个库挂掉了？**

我们知道，给一个表加字段，修改索引，添加索引，都会进行全表扫描。举个例子：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]

*  session A 启动，这时会对表 t 加上一个 MDL 读锁。
 *  session B 进行查询，这时也会对表 t 加上一个 MDL 读锁，读锁与读锁之间并不互斥，因此可以正常执行。
 *  session C 进行表结构修改，此时会对表 t 加上一个 MDL 写锁，这时session A 的 MDL 读锁还未释放，写锁和读锁同时存在，造成互斥，目前只有等待 读锁释放，所以需要等待。
 *  这时session D 进行查询，申请MDL读锁，这时候也会被阻塞，处于等待状态。

所有对表操作增删改查都需要申请MDL读锁，就都被锁住，此时的表完全不可读写了。

**这里为什么C等待拿锁之后，D也会被阻塞呢？**

如果按照并发理解的话，C,D应该是同一等级，都有可能拿到锁，但C读写锁互斥，D为读读锁应该是共享的呀，并不互斥啊？

因为MDL锁在申请时会形成一个队列，队列中 写锁获取优先级高于读锁。一但写锁出现等待，不但当前事务会造成阻塞，同时还会阻塞后续该表的所有操作。

事务一旦申请到MDL锁后，一直等到事务结束后才会进行释放锁。

#### 如何安全的给小表加字段 ####

事务一旦申请到MDL锁后，在语句执行开始申请，语句结束并不会释放锁，一直等到事务结束后才会进行释放锁。

首先要解决长事务，事务不提交，就会一直站着MDL锁。如果要做DDL变更的表刚好有长事务在执行，要考虑暂停DDL，或者kill掉这个长事务。

#### 要变更的表是热点表，数据不大，但请求频繁，现在不得不加字段，该怎么办？ ####

这时候kill掉长事务未必可用了，因为请求很频繁。

在 alter table 语句里面设置等待时间，如果在这个指定等待时间拿到 MDL 写锁最好， 拿不到就先放弃，之后等DBA重试命令重复这个过程。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/0782850b582f44769d10c92116f4b49a.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70