剖析MYSQL锁机制

ゝ一世哀愁。 2022-12-10 01:25 126阅读 0赞

### 剖析MYSQL锁机制 ###

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，数据是一种供许多用户共享的资源。数据库的锁机制，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

**从对数据操作的类型分类**：

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

**根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类**

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，因此需要根据具体的业务需求选用合适的锁。

#### 全局锁 ####

全局锁是对整个数据库实例加锁，加锁后整个数据库处于只读状态，之后的数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句都将被阻塞执行。

MYSQL加全局锁的方式是 `mysql> flush tables with read lock;` 释放全局锁的命令为：`mysql>unlock tables；`,或者断开加锁的session的连接即可。

显然，一旦加上全局锁，整个数据库就不能更新，即新数据无法写入，这是非常影响业务的。因此**全局锁的典型适用范围就是给数据库做全局逻辑备份(mysqldump)**,也就是把整库每个表都 select 出来存成文本。

让整个数据库处于只读状态是有比较大的风险的：

*  在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止
 *  在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

但是如果在全局备份时不加全局锁，可能会导致数据的不一致性，即如果已经将某一个数据备份了之后，又对该数据进行了修改，那么就会造成主从数据库不一致的现象，并且binlog日志也会有不一致。也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个数据是逻辑不一致的。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性快照视图。但是并不是所有的引擎都支持single-transaction，MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。因此single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

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

*  readonly=true可以令全局进行只读状态，但是要小心发生异常。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。并且readonly 对super用户权限无效。

#### 表级锁 ####

MySQL 里面表级别的锁有两种：**一种是表锁，一种是元数据锁（meta data lock，MDL)**

表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作，也不能去访问其他表。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上，MDL 的作用是，保证读写的正确性。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁是不互斥的，多个读锁可以同时加上去，但加上读锁后不可以加上去写锁，写锁是互斥的，加上写锁后不可以再加其他锁。

通常来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用。

MyISAM 的表锁有两种模式：

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

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

不过需要注意的是，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放，因此要谨慎使用，可能导致死锁。

因此如果有长事务需要MDL锁，那么由于事务一直不提交，就会一直占着 MDL 锁。在 MySQL 的information\_schema 库的 innodb\_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。对于热点表，请求非常频繁，那么在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

还有补充的是，在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。【因为没有事件】

#### 行锁 ####

行锁的开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB 是支持行锁的，MyISAM 引擎就不支持行锁。

行锁是两阶段锁，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。因此，为了尽可能提高并发度，加快处理速度，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行锁很容易引发死锁，如下面的例子:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center]

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态.

当出现死锁以后，有两种策略：

*  一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb\_lock\_wait\_timeout 来设置。
 *  另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb\_deadlock\_detect 设置为 on，表示开启这个逻辑。

第一种方案中，超时值的设定就比较麻烦，如果超时值设置太大，那么可能导致等待时间过长，对线上服务产生非常不好的影响，如果超时值设定太小，会产生比较多的误伤，正常的锁等待可能会被误判。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb\_deadlock\_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁，因此对每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作，因此整体是O( n 2 n^2 n2)的时间复杂度。

通常来说，行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

InnoDB 实现的两种类型的**行锁**：

*  共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
 *  排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

#### 加锁机制 ####

**乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题**

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，**悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/333bea68fac44d449479570967780f69.png