本文主要是介绍【架构师面试-存储-3】-MySQL全局锁|表级锁|行锁，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

数据库为多用户共享的，当出现并发访问的时候，需要使用锁来控制资源的访问。根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个MySQL数据库加锁，MySQL中的命令是 Flush tables with read lock (FTWRL)。在执行这个命令之后，MySQL进入全局锁的状态，这个数据库处于只读状态，整个数据库会拒绝掉增删改这些请求，包括数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

Flush tables with read lock (FTWRL) 

全局锁使用场景

全局锁的典型使用场景是，做全库逻辑备份（mysqldump）。重新做主从时候 

也就是把整库每个表都 select 出来存成文本。 

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

数据库只读状态的危险性：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止。

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

注：上面逻辑备份，是不加--single-transaction参数

看来加全局锁不太好。但是细想一下，备份为什么要加锁呢？来看一下不加锁会有什么问题？

全局锁的缺点

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

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

FTWRL的替代方式

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

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

全局锁两种方法

FLUSH TABLES WRITE READ LOCK 
set global readonly=true

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

确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有几个原因： 

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

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

3：readonly 对super用户权限无效。

注 ：业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。

即使没有被全局锁住，加字段也不是就能一帆风顺的，还有表级锁了

表级锁

表级锁也分为两类： 表锁 、 元数据锁（meta data lock，MDL）

1：表锁

使用场景

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

表锁的语法

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

lock tables table1 read,table2 write;

lock tables语法不仅会限制别的线程（事务）读写操作，也限定了本线程（事务）的操作对象以及操作方式。即本线程只能按照加锁语句中规定的方式（读或者写）访问特定的资源（table1、table2）。

表锁实例

线程 Thread1 中执行 lock tables table1 write, table2 read

其他线程读、写 table1、写 table2 的语句都会被 阻塞。

线程 Thread1 在执行 unlock tables 之前，也只能执行读、写 table1、读 table2 的操作。连写 table2 都不允许，并且也不能访问其他表。

2：元数据锁（MDL）

使用场景

MDL的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列或者直接删除了表，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

元数据锁的语法

MDL不需要显式使用，在访问一个表的时候会被自动加上。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

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

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

元数据锁实例

T0： session1先执行select查询语句，对表加MDL读锁；

T1： session2执行select查询语句，需要MDL读锁，由于读锁之间不互斥，也可以正常执行；

T2： session3执行alert语句，修改数据库结构，需要MDL写锁，由于session1的MDL读锁还没有释放，读锁和写锁是互斥 的，所以session3会blocked被堵塞；

T3： 如果再执行session4的select查询语句，虽然是需要MDL读锁，但是由于第三步session3的MDL写锁被block住还没有释放，所以session4也申请不到MDL读锁，会被blocked，所以表就会完全变成不可读状态。

如何处理MDL锁

提交或者回滚这个事务

1：找到这个事务

# 查看事务超过60s的事务
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started 表示什么时候执行的这个事务
 
#查看系统当前时间
mysql> select now();

2：查看这个线程id

show full processlist

3：考虑先暂停DDL，或者kill掉这个长事务

如何安全地给小表加字段

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。

如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。这也是为什么需要在低峰期做ddl 变更，当然也要考虑具体做什么ddl，参考官方的online ddl。

online ddl 过程

拿MDL写锁

降级成MDL读锁

真正做DDL

升级成MDL写锁

释放MDL锁

1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个个表可以正常读写数据，是因此称为”online”

行锁

        MySQL的行锁是在存储引擎自己实现的。MyISAM引擎就支持行锁，所以其并发控制职能使用表锁。对于使用MyISAM引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。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。

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

在行锁的引擎中，行锁是执行到具体某一行才加上的。

行锁在本本事务commit之后才会被释放。

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

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

行锁中的死锁

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

资源必须互斥访问

请求并保持

不可抢占资源

形成一个环

如果一个项目要新上线一个新功能，如果新功能刚开始的时候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的消耗。这种方式需要在代码里做详细、严谨的逻辑分析。

如果您觉得文章好看，欢迎点赞收藏加关注，一连三击呀，感谢！！☺☻

参考文章

MySQL 全局锁、表锁、行锁 - 老张wahaha - 博客园

《MySQL必懂系列》全局锁、表级锁、行锁 - 码农编程进阶笔记 - 博客园

MySQL：全局锁，表锁和行锁_sayoko06的博客-CSDN博客

这篇关于【架构师面试-存储-3】-MySQL全局锁|表级锁|行锁的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！