全局锁、表锁、行锁

1.全局锁

MySQL加全局读锁：Flush Table With Read Lock 此时装个库处于只读状态，所有更新修改语句全部会被阻塞。

全局锁使用场景：全库逻辑备份。但是为了不影响业务，可以在可重复隔离级别下开启事务，拿到一致性视图进行备份更新。一般有如下几种方法：

• 使用single-transaction启动事务。(InnoDB支持)
• 如果当前数据库表不支持事务，采用set global readonly=true。但是同时有以下缺点：
  • 改变全局变量影响大
  • 用户如果异常断开连接，那么数据库会一直保存readonly状态，导致整个库处于不可写状态。

2.表级锁

2.1表锁

lock tables t1 read，t2 write：限制当前和其它线程只能够对t1表进行读，当前线程只能够对t2写，其他线程对t2读写都会被阻塞。通过unlock tables释放锁。

2.2元数据锁

MDL在访问每个表时会自动加锁(主要是用于对表结构的更改)，系统会默认添加的锁。对表做增删改查时，加MDL读锁；对表结构进行变更操作时，添加MDL写锁。事务提交才会释放锁。

MDL读锁之间不互斥，读写锁互斥。

小表添加字段的安全问题

• A线程获取“MDL读锁”，对数据库进行增删改查
• B线程要获取“MDL写锁”，给表添加新的字段。但是由于A线程没有提交事务，因此B线程被阻塞。
• C线程要获取“MDL读锁”，但是因为要读写的内容是B添加后的新表，因此B被阻塞会导致C以及后续所有读写请求都会被阻塞。整个表此时完全不可读写。

添加字段如何安全解决

• 本质上是长时间占据事务不提交的问题，在添加字段之前查询innodb_trx表，查看所要添加字段的那个表是否在执行长事务，如果是则暂停当前添加操作或者是kill掉长事务。
• 如果该表是热点信息表，虽然不占据长事务但是频繁被其它线程申请读锁。这种情况下如果有字段添加操作，那么需要给这个alter操作设置一个等待时间，在等待时间内如果能拿到MDL写锁则正常进行DDL，超时后则放弃当前DDL操作，避免阻塞后续的增删改查操作。

2.3场景分析

当前线程使用single-transaction添加读锁备份数据，主库从binlog传来一个DDL语句，最后Q5读到的结果有什么不同？

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 设置可重复读隔离级别

Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT； 获取一致性视图
/* other tables */
Q3:SAVEPOINT sp; 设置保存点
/* 时刻 1 */
Q4:show create table `t1`; 拿到表结构
/* 时刻 2 */
Q5:SELECT * FROM `t1`; 导出数据
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp; 释放MDL读锁，并回滚到sp
/* 时刻 4 */

• DDL在时刻1到达，则Q5没有影响照常运行，导出的是DDL修改过后的表结构。
• DDL在时刻2到达，会占据MDL写锁，在执行Q5时会报错Table definition has changed, please retry transaction，mysqldump终止。
• DDL如果是在时刻3到达，那么因为Q5占据着MDL读锁，则DDL会被阻塞，直到Q6完成
• DDL在时刻4开始，因为Q6已经释放MDL读锁了，因此没有影响，导出的数据是DDL前的表结构

3.行锁

InnoDB支持行锁；而MyISAM不支持，因此并发控制只能够使用表锁，粒度太大。

💡加行锁是由InnoDB引擎完成，与server层无关。

3.1两阶段锁协议

行锁的获取时机：两个事务操作同一行数据，存在冲突时才会加行锁，先操作的事务会获得行锁。

行锁的释放：获取行锁的事务直到提交完事务，才会释放行锁。

以下图为例，事务A拿到行锁，因此B执行时会被阻塞，直到事务A释放锁之后，事务B才执行。

因此如果在一个事务中，需要操作多行数据，每个增删改查语句都会添加行锁，因此可以把最可能影响并发的语句尽可能往后放，减小和其它事务冲突的概率。

3.2死锁

由于行锁只有当事务提交后才会释放，存在“持有锁资源的同时请求其它锁资源”的死锁问题，两个事务出现锁资源循环依赖，具体如下：

• 事务A先拿到id=1的锁
• 事务B拿到id=2的锁
• 事务A要更改id=2记录，但是因为锁被事务B拿着，因此事务A持有锁的同时被阻塞。
• 同理事务B要操作id=1，也会被阻塞。

3.3死锁解决方式

死锁监测一般有如下两种方式：

• 方式1：等待事务超时，回滚。超时时间通过innodb_lock_wait_timeout设置。
• 方式2：通过innodb_deadlock_detect设置为on开启死锁监测，发现死锁后主动回滚其中一条事务。

一般采用方式2解决死锁问题，但是在方式二中，每个死锁的线程都会检查所依赖的其它所有线程是否死锁，这是一个O(n*n)的复杂度。对于一条🔥热点数据而言，同时有1000个线程请求，则死锁监测操作量级在百万以上，从而消耗大量CPU资源。

3.4死锁监测机制带来的热点数据更新的性能问题解决方式

• 关闭死锁监测

能够确保业务一定不会出现死锁，可以临时关闭死锁检测。否则当出现死锁的情况下还关闭监测，会导致大量业务超时。

• 控制并发度

如果在客户端进行并发控制，即使并发度很小，但是如果部署的客户端应用很多，汇总到的服务端的线程数量也很多，因此这种方法不能完全解决问题。

✨药到病除的方法是在数据库进行并发控制，比如在服务端打入数据库之前，加个中间件记录当前数据库的并发线程数量。

✨另一种方法可以将一行的冲突打散成多行，比如对于共享资源，分表或者分行进行存储。最终统计还原时再将所有记录相加。