MySQL 全局锁、表锁和行锁「建议收藏」

大家好，又见面了，我是全栈君。

最近在极客时间看丁奇大佬的《MySQL45讲》，真心觉得讲的不错，把其中获得的一些MySQL方向的经验整理整理分享给大家，有兴趣同学可以购买相关课程进行学习。

今天分享的内容是MySQL的全局锁、表锁和行锁。

1、全局锁

全局锁，是指对整个MySQL数据库加锁，对应的命令是flush tables with read lock；（以下简称FTWRL）

当你需要让整个库处于只读模式的时候，可以使用这个语法，它的应用场景，一般是在全库逻辑备份的时候。我们知道MySQL自带的mysqldump逻辑备份工具可以使用–single-transaction参数来进行备份，因为Innodb存储引擎支持事务和MVCC的原理，所以该备份方法没有问题。但是，在你使用MyISAM等存储引擎时，该语句可以保证在备份期间的数据一致性。而

–single-transaction方法只适用于所有的表使用事务引擎的库;

2、表级锁

MySQL里面表级别的锁有两种，一种是表锁，一种是元数据锁(MDL)

表锁的加锁方式为lock tables xxx read，解锁方式是unlock tables xxx，需要注意的是：

lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。当前线程也不能对表t1做写的操作

MDL元数据锁是指在对一个表做增删改查的时候，MySQL会对该表加MDL读锁，防止另外一个线程对该表做变更操作，当对一个表做表结构变更的时候，会对该表加MDL写锁。MDL锁不需要显式使用，在访问一个表的时候会被自动加上

MDL锁可能会造成MySQL宕掉！！！举例如下：

当我们开启多个事务的时候，假设事务的内容都是一个begin+简单的select语句（加MDL读锁），当其中一个事务没有及时提交，此时如果有一个alter table的操作（导致MDL读锁升级为MDL写锁），会导致后续的select语句，都被阻塞，即使这个表的记录数很少，在事务不及时提交的情况下，也会导致整个库不可读。如果此时应用方面有重连机制，则会导致连接数被快速打满，这往往是灾难性的。此场景中，即使使用pt工具进行表结构变更，也无法解决问题。

还需要注意，如果事务中没有begin的话，这样select执行完成以后，MDL就自动释放了，则不会造成MDL锁等待。

3、行锁

行锁里面比较重要的一个概念：两阶段锁，它是指：

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时(commit动作完成之后)才释放。

从这个两阶段锁机制中我们不难发现一个好的习惯：

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

行锁的产生，可以大大降低死锁的概率(是降低，不是杜绝)，但是这种热点行的频繁更新，往往会导致MySQL的性能问题(因为死锁检测会消耗大量的CPU资源)。如何解决热点行的频繁更新带来的性能问题？

1、关闭死锁检测参数innodb_deadlock_detect，这种操作，往往不是最优的，因为可能出现大量因为死锁带来的超时问题。

2、控制并发，核心思路就是相同行的更新，在进入引擎之前进入队列排队。

最后，死锁的两个关键参数：

innodb_deadlock_detect：死锁检测参数，默认为on，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行

innodb_lock_wait_timeout：死锁等待的超时时间，默认为50s，意味着如果不开启死锁检测，则在发生死锁之后，会等待50s，直到超时。

—————————-

思考题：

备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

参考答案：

本质上，MySQLdump+transaction参数进行备份的过程中，我们可以认为它做了如下的动作：

T0：begin；

T1：savepoint sp;

T2: show create t1；

T3：select * from t1；(执行一段时间)

T4：rollback to sp;

T1时刻mysqldump设置一个保存点，然后拿到t1表的表结构(T2)和表数据(T3)，最后，再回到保存点sp(T4),整个过程中，如果：

在T2时间之前，在表上增加了一列，从库上没有影响。

在T2和T3之间，在表上增加了一列，则报错 Table definition has changed, please retry transaction

MySQL备份中止

在T3期间到达，则因为此时正在备份，mysqldump占着t1的MDL读锁，binlog被阻塞，现象：主从延迟，直到T4执行完成

在T3和T4之间到达，则没有影响，因为mysqldump已经释放了MDL读锁