《MySQL必懂系列》全局锁、表级锁、行锁

MySQL提供了不同等级的锁，按限制能力的划分，分为全局锁、表锁、行锁。本文会描述不同锁的应用场景与实现原理。

全局锁

全局锁就是对整个MySQL数据库加锁，MySQL中的命令是 Flush tables with read lock (FTWRL)。在执行这个命令之后，MySQL进入全局锁的状态，整个数据库会拒绝掉增删改这些请求。

为什么需要全局锁

全局锁的目标是为我们维护一个数据库的逻辑一致性。如下场景中：在进行逻辑备份（即备份的数据是SQL语句）的时候，没有开启全局锁，那么很可能会导致出现数据库的逻辑一致性错误，例如两个表，一个余额表、一个订单表，在购物时（减余额、生成订单）如果逻辑备份在这两个操作之间，也就是说减完余额之后，逻辑备份，拒绝生成订单，那么这个时候，我们进行的逻辑备份就是一个错误的逻辑一致性状态。以后使用这个逻辑备份进行数据恢复的时候，就会出现用户余额已经减少，但并没有订单这种问题。

全局锁的缺点

• 对主库使用全局锁进行逻辑备份时，会造成业务的停摆
• 对从库使用全局锁进行逻辑备份时，会造成主从延迟的问题

FTWRL的替代方式

全局锁解决的就是上面的问题，我们可以结合数据库中事务的隔离级别，使用可重复读（各个事务之间没有相互影响，基于mvcc）的隔离级别，获取数据库的逻辑一致性视图。MySQL官方自带的逻辑备份工具mysqldump，在备份数据之前，会启动一个事务，以此来获得一个逻辑一致性视图。

但需要注意的是，虽然事务的可重复能解决FTWRL影响性能的问题，但事务并不是万能的，因为并不是所有的引擎都支持这个隔离级别，MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的逻辑一致性。

为什么不设置为全库只读？

我们的目的是实现数据库的逻辑一致性，那么为什么不建议直接把数据库设置成只读状态呢？ (set global readonly=true)
主要有一下原因：

1. FTWRL与readonly的异常机制不太一样。客户端（相对于MySQL）发生异常，FTWRL命令下会自动释放MySQL的全局锁。而readonly会一直停留在readonly状态，数据库长期处于不可写状态。
2. readonly会被一些逻辑判断使用，例如使用readonly判断是主库或者备库。

表级锁

表级锁也分为两类： 表锁 、 元数据锁（meta data lock，MDL）。
业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。

表锁

使用场景

在还没有更细粒度的行锁的时候，表锁是最长用的处理并发的解决方式。但是对于当前支持行锁的引擎例如innodb，都优先使用行锁来控制并发，以此来避免因为锁住整个表的影响。

表锁的语法

加锁 lock tables … read/write、主动释放锁unlock tables 。同时表锁也可以在客户端断开连接的时候自动释放。

读锁（共享锁）

事务A对数据d加上共享锁S，那么事务A只能对d进行读操作，并且后面的事务B、C、D都可以加锁S进行只读操作。在释放完S锁之前不能对数据d进行修改。

写锁（排它锁）

事务A对数据d加上排它锁X，那么事务A可以对数据d进行访问、修改，并且拒绝其他事务对数据d的读、写。

表锁需要注意的地方

lock tables语法不仅会限制别的线程（事务）读写操作，也限定了本线程（事务）的操作对象以及操作方式。即本线程只能按照加锁语句中规定的方式（读或者写）访问特定的资源（table1、table2）。例如：线程 Thread1 中执行 lock tables table1 write, table2 read;其他线程读、写 table1、写 table2 的语句都会被 阻塞。同时，线程 Thread1 在执行 unlock tables 之前，也只能执行读、写 table1、读 table2 的操作。连写 table2 都不允许，并且也不能访问其他表。

元数据锁 metadata lock MDL

元数据在这里其实指的就是表结构，MDL锁定的也就是我们表结构。防止出现一个线程A在执行表查询操作时候，线程B删除了一个字段，导致查询的结果与表结构不符合这种情况的出现。
所以为了解决上述问题，MDL分为了读锁与写锁。

• 在进行表的增删改查时候，会对表自动加上读锁，读锁之间不会互斥，所以多个线程可以对同一个表进行增删改查。
• 在进行表结构更改时候，会对表自动加上写锁，写锁是互斥，多个线程能依次对表结构进行修改，然后再加上读锁进行增删改查。

表锁并不是现在优先考虑使用的锁，应该尽量的使用行锁，如果在项目中遇到lock table1这样的SQL语句时，应该思考一下：

• 是否使用了过老的引擎，例如MYISAM就不支持行锁，可以考虑升级一下引擎，然后把业务代码中的lock tables unlock tables替换为begin commit就OK啦。

行锁

行锁顾名思义就是对每一行的数据加锁，这是MySQL数据库中最细粒度的锁，右innodb引擎支持。对于不能支持行锁的引擎，对于并发操作的处理只能使用表锁锁定整个表，这也是MyISAM被innoDB所替代的重要原因之一。

行锁的使用过程

使用行锁过程中，若一个事务A正在更新某一行数据d，这时候如果事务B也想对d进行更新操作，那么只能等A更新完毕然后再加自己的行锁对d进行更新操作。这其中就涉及到一个两阶段锁这个概念。

行锁的两阶段锁协议

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

其实就是规定了加锁与解锁的时机，两阶段锁协议不仅局限在行锁中。

上面的两个事务AB执行时候就会使用到两段锁协议：事务A先开始执行，id=1时加锁这一行，id=2时加锁这一行，事务A的两条语句执行完了但是还没有commit，事务B开始执行，但是这个时候事务B的update id=1会被阻塞，因为id=1还被事务A加着行锁，虽然事务A的update执行完了，但是事务A还没有commit，意味着事务A所占据着的行锁都没有释放，只有等A执行commit之后，事务B才能继续获得id=1的行锁进行update。

所以我们应该记住两段锁的特点：

1. 在行锁的引擎中，行锁是执行到具体某一行才加上的。
2. 行锁在本本事务commit之后才会被释放。

所以根据两段锁协议的特点，我们在开发过程中，应该在事务中把并发大的表放到后面执行，让它被行锁锁定的时间最短。

例如在减库存，生成订单这样的场景中，我们应该先在事务中生成订单，在减库存。因为库存的update并发量会大于订单insert的并发量，update需要使用行锁，如果先update库存，会使库存中的这一行一直被行锁锁定，在事务提交时候才能被释放，增加了许多无用的库存行锁锁定时间。

行锁中的死锁

数据库中死锁的概念很清晰，和我们操作系统中的一致：

1. 资源必须互斥访问
2. 请求并保持
3. 不可抢占资源
4. 形成一个环

如果一个项目要新上线一个新功能，如果新功能刚开始的时候MySQL 就挂了。登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。原因很可能就是死锁。

解决MySQL死锁策略

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

1. 设置等待的超时时间。innodb_lock_wait_timeout
2. 主动发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。innodb_deadlock_detect = on，表示开启死锁检测。

innodb_lock_wait_timeout在innoDB引擎中的的默认值是50s，意味着如果发生死锁的情况，第一个被锁住的线程等待50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，我们又不可能直接把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，通过设置超时时间通常不是一个好办法，这个更依赖经验值，也依赖不同项目的环境（请求并不均匀）。

所以通常情况下会采用主动死锁检测的策略，innodb_deadlock_detect默认值就是on的状态。主动死锁检测能及时发现并解决死锁，但主动死锁检测会消耗硬件资源。

主动死锁检测 流程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被 别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

主动死锁检测在热点行更新时产生的问题

上面我们提到更推荐使用主动死锁检测去解决死锁问题，但在这样的场景中：所有的事务都需要更新同一行的数据。使用主动死锁检测肯定能得出未死锁，但是这期间要消耗大量的cpu，导致虽然占用了大量cpu却实际没能执行几个事务。

这种由这种热点行更新导致的性能问题的原因在于：主动死锁检测要耗费大量的 CPU 资源。

热点行更新导致的性能问题的解决思路：

1. 如果能保证某个业务不会出现死锁，可以临时关闭死锁检测，但本身可能存在风险，如果发生死锁，会发生事务等待超时时间。
2. 控制并发度。例如一行数据只能允许20个事务进行同时更新，那么可以极大的减缓死锁检测的压力。如何去控制并发度，大体也有两个思路一是通过业务代码在客户端进行访问MySQL的控制，但是MySQL不一定只有这一个客户端，所以这个思路优缺点；二是考虑使用中间间或者是修改MySQL源码，对于相同行的update，在进入引擎之前排队，里面只允许存在20个事务进行update，这样update时候就不会有太大的死锁检测压力。（死锁检测时间复杂度为O（n平方））。 但是这个需要数据库方面的专家。。。
3. 可以考虑在业务层面减少对某一行的并发度。例如在收款这个场景中，我们把热点的某一行拆分出来，保证拆分出来的几行最后在收款的总数一致就可以了。如果分为20个，那么死锁的肯能性就变为了原来的20粉之一，与此同时由于不是同一行也减少了主动死锁检测cpu的消耗。这种方式需要在代码里做详细、严谨的逻辑分析。

综上：减少死锁的主要方向，就是控制访问相同资源的并发事务量。

文章来源: blog.csdn.net，作者：lxw1844912514，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/lxw1844912514/article/details/109900383