MySQL锁

MySQL锁有几类？

• 全局锁
• 表级锁
• 行锁
• 间隙锁（用来解决幻读，这个后面单独讲）

全局锁

什么是全局锁？

全局锁就是对整个数据库实例加锁，当数据库被加上全局锁以后，整个库会处于只读状态，处于只读状态下的库，以下语句会被阻塞：

• 数据更新语句（增删改）
• 数据定义语句（创建表、修改表结构等）
• 更新类事务的提交语句

整个库只读有什么危害？

• 如果是主库，那么只读期间不能执行更新，业务停止运行
• 如果是从库，那么只读期间不能执行主库同步过来的binlog，会导致主从延迟

如何加全局锁？

-- FTWRL
flush tables with read lock ;

全局锁的使用场景？

全库逻辑备份：把每个表都select成文件。

mysqldump

除了上述select可以进行逻辑备份，官方提供了mysqldump为逻辑备份工具。

-- single-transaction参数会在导数据前启动事务，拿到一致性视图。
-- 此逻辑备份期间，其他事务可以正常对数据进行更新
mysqldump -h 127.0.0.1 -P 3306 -u root  -p --databases test --single-transaction > test.sql

mysqldump --single-transaction的弊端？

single-transaction只适用于所有表使用事务引擎的库。这也是InnoDB引擎逐渐取代MyISAM的原因。

global readonly

除了对数据库加全局锁可以让整个数据库只读以外，我们可以使用以下命令也可以让全库进入只读状态：

set global read_only  = true;

global readonly的弊端

• 有些系统中会使用readonly来做逻辑判断，比如用来判断一个库是主库还是从库，所以不建议使用该参数
• FTWRL以后如果客户端发生异常断开，MySQL会自动释放此全局锁，但是如果使用global readonly客户端发生异常以后数据库会一直保持只读状态，导致数据库长时间不可写，风险较高
• 如果账号拥有super权限，依然可以进行更新

表级锁

表级锁的分类有哪几种

• 元数据锁(MDL)
• 表锁

如何加表锁

-- 给test1表加读锁
-- 给test表加写锁
lock tables test1 read, test write;

-- 释放锁
unlock tables ;

什么是元数据锁（MDL）?

元数据锁不需要显示使用，在访问一个表的时候会被自动加上。

元数据锁主要用来保证读写的正确性：

• 当对表做增删改查时，会对表加MDL读锁
• 当对表结构做变更时，会对表加MDL写锁

元数据的读写锁有啥特点？

• 读锁之间不互斥，因此可以有多个线程可以对同一场表做增删改查
• 读写锁、写锁之间互斥，也就是说两个线程同时对一个表做字段变更，其中另一个要等到另一个线程执行完成才可以成功。

如何安全的给表变更字段？

首先需要解决长事务，因为长事务会占用着MDL锁，此时需要考虑暂停DDL或者kill掉这个长事务。

如果是热点数据表，此时可能kill会无效果，因此最好在alter table的时候指定一个最大时间，如果在该指定时间内获取到MDL锁就执行，如果获取不到就放弃。

目前MySQL8在SQL语句上还不支持，但是可以通过配置参数lock_wait_timeout进行控制，但是MariaDB已经在SQL语句上支持该功能。

什么是Online DDL？

Online DDL的过程如下：

1. 获取MDL写锁
2. 降级成MDL读锁
3. 执行DDL
4. 升级MDL写锁
5. 释放MDL写锁

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

Online DDL可以在做代表DDL的同时进行DML。

如果Online DDL都无法解决实现，也可以使用pt-online-schema-change进行在线DDL。

行锁

全局锁和表锁的实现都是在Server层进行实现，但行锁是由各个引擎自己实现，不支持行锁意味着并发控制只能使用表锁。

什么是行锁？

行锁就是对数据表中的行记录加锁，比如事务A更新了一行，事务B也要更新同一行，则必须等事务A的操作完成以后才能进行更新。

行锁什么时候加，什么时候释放？

InnoDB的事务中，行锁是在需要的时候添加，但是释放是在事务结束时。

InndoDB的行锁是通过锁索引记录实现的，如果你update的条件没有索引的话，那么将会对整张表进行加锁。

什么是死锁？

并发系统中不同线程出现循环资源依赖，涉及的线程在等待别的线程释放资源，这几个线程之间就会形成死锁陷入无线等待。

上图中事务A在等待事务B释放id=2的行锁，而事务B又在等待事务A释放id=1的行锁，两个事务之间形成死锁。

如何解决死锁？

• 直接进入等待，直到超时，超时时间由参数innodb_lock_wait_timeout设置
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务继续执行。是否开启死锁检测由参数innodb_deadlock_detect控制。

使用超时等待主要有以下弊端：

• 如果超时时间过长，业务不可忍受
• 如果超时时间过短，容易将正常的锁等待误判为死锁。

死锁检测的弊端：

• 每个新来的线程如果发生阻塞，都需要判断是否因为自己的加入引发死锁，检测死锁的过程需要消耗CPU

其他解决死锁方案

• 确保业务无死锁，可以临时关闭死锁检测
• 在数据库服务端进行并发控制，对于相同行的更新，在进入引擎之前让其排队，这样在InnoDB引擎内部可以避免大量的死锁检测工作（需要能够修改MySQL源码）
• 如果有中间件研发团队，也可以考虑使用中间件来实现并发度的控制
• 业务上对单行进行多行拆分，比如我们可以将一个相同的账户在数据库里面变成10行，在更新账户余额的时候随机选择一条进行更新，账户总余额就是这10行的合，但是这种会让业务逻辑上变得复杂

备库使用--single-transaction做逻辑备份会有什么问题？

mysqldump --single-transaction进行逻辑备份的时候主要有以下过程：

-- 备份开始时设置隔离级别
set session transaction isolation level repeatable read;
-- 启用事务，获取一致性视图
start transaction with consistent snapshot ;
-- 设置一个保存点
savepoint `sp`;
-- 获取test表的表结构
-- 时刻1
show create table `test`;
-- 开始导数据
-- 时刻2
select * from `test`;
-- 时刻3
-- 回滚到保存点，释放test表的MDL锁
rollback to savepoint `sp`;
-- 时刻4

DDL的binlog从主库传递到从库的上述4个时刻逻辑备份会有不同的表现：

• 如果在时刻1之前到达，没有影响，备份拿到的是DDL后的表结构
• 如果在时刻2到达，说明表结构被改过，在select * from test;执行的时候会报错，mysqldump命令会被终止
• 如果在时刻3的时候到达（就是数据在导出过程中），由于mysqldump占用着mdl读锁，binlog会被阻塞，主从会发生延迟，直到rollback to savepoint sp结束
• 如果在时刻4达到，mysqldump已经释放了MDL读锁，逻辑备份拿到的是DDL前的表结构