MySQL中的锁机制

本片文章总结下MySQL锁相关的知识，有问题欢迎大家指正讨论！

全局锁

全局锁会对整个数据库加锁，开启全局锁之后数据库处于Read Only状态，所有DML及DDL语句都会被阻塞；
通过命令Flush tables with read lock(FTWRL)来开启全局锁，对应使用unlocak tablse来解锁。
​

场景： 做全库逻辑备份，也就是把整个库每个表都select出来存成文本
​

弊端：

• 在主库备份，备份期间涉及DDL业务需要暂停
• 在从库备份，备份期间从库不能执行主库同步过来的binlog，导致主从延迟

为什么备份要加锁？
假如需要对整个库做备份，如果在备份过程中不将整个库锁住，那可能会出现备份过程中业务还在执行，导致业务关联表的数据不一致。
**举例：**假设目前维护的是一个商城系统，数据库中有订单表和用户账户余额表，现在要进行逻辑备份，先备份订单表，再备份账户余额表，当备份完订单表后有一个用户买了件商品，这时候备份的订单表中并没有刚刚这个订单，当备份余额表的时候，余额已经扣了，这种情况就比较严重了，用户发现钱少了， 但是没有订单。
​

mysqldump工具进行逻辑备份
使用全局锁其实就是为了保证备份期间拿到的是整个数据库的某个时间点的视图，在《事务的隔离性及MySQL多版本并发控制实现》中讲过，InnoDB为了实现事务的“可重复读”隔离级别，使用了Read Videw来保证从事务开启到事务提交或者回滚，所看到的数据库状态都不变，并且在这个过程中数据是可以正常更新的。
​

优点： 官方自带的逻辑备份攻击mysqldump，当使用参数-single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，由于MVCC的支持，这个过程中数据是可以正常更新的
缺点： 只能是支持事务的引擎才可以
​

readonly参数与FTWRL的区别

set global readonly=true也可以让全库只读，这个参数与FTWRL的区别如下

• 在有些系统中，readonly的值会被用来做其他逻辑，比如判断一个库是主库还是备库。因此，修改global变量的的方式影响面更大，不建议使用
• 异常处理机制不同，FTWRL命令如果由于客户端异常断开，MySQL会自动释放这个锁，整个库回到可以正常更新的状态；readonly参数设置之后，如果客户端发生异常断开，数据库会一直保持为readonly状态，这样会导致整个库长时间处于不可写状态
  

表级锁

表锁

命令：lock tables T read/write，unlock tables释放锁
特点： 除了会限制别的线程的读写外，也会限定本线程接下来的操作
​

由于InnoDB的锁粒度支持到行级锁，因此InnoDB一般不使用lock tables命令来控制并发

元数据锁（meta data lock）

元数据锁简称MDL锁，是在并发情况下维护数据地执行的，在表上有事务在执行的时候，不可用对元数据进行写操作，并且这个是在Server层实现的。
​

MDL锁分为MDL读锁和MDL写锁，当对表进行增删改查操作时会对表加MDL读锁；当对表进行DDL操作时会对表加MDL写锁。

• 读锁之间不互斥
• 读写锁互斥、写锁互斥

行锁

行锁就是针对数据表中行记录的锁，当两事务A更新了一行数据，在A事务提交之前，事务B也要更新同一行，则必须等待事务A提交之后事务B才能执行更新。
​
MySQL的行锁是在引擎层由各个引擎自己实现的，但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。
不支持行锁就只能使用表锁，在并发场景下，会影响到业务的并发度。
​
两阶段锁： 在InnoDB事务中，行锁是在需要的时候加上的，但并不是不需要了就立刻释放，而是等到事务结束时才释放，这就是两阶段协议。
因此在业务中，在不影响业务的情况下，要尽量将最后可能造成锁冲突的业务逻辑放到后边，减少持有锁的时间，最大程度减少事务之间的锁等待。

死锁

在并发系统中不同线程出现循环资源依赖，涉及的线程都在等待其他线程释放锁资源时，这几个线程互不相让，导致进入无限等待的状态，称为死锁。
举例：

1. 线程A开启事务A，更新id=1这行记录，持有这行记录的行锁
2. 线程B开启事务B，更新id=2这行记录，持有这行记录的行锁
3. 线程B又要更新id=1这行记录，由于线程A持有这行记录的行锁，导致线程B等待线程A释放id=1这行的记录的行锁
4. 线程A要更新id=2这行记录，由于线程B持有这行记录的行锁，导致线程A等待线程B释放id=2这行记录的行锁
5. 线程A和B互相等待对方释放锁，导致死锁
   

死锁应对策略

等待锁超时

当死锁后，直接进入等待，直到超时，通过参数innodb_lock_wait_timeout来设置，默认50s
缺点： 当设置的时间过长时，例如就是默认50s，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行，对于一个在线服务来说，这个等待时间是无法接受的。但是也不能设置的太小，太小又可能会把正常执行的线程误伤
​

死锁检测

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。通过设置参数innodb_deadlock_detect=on来开启死锁检测
缺点： 性能消耗，每当一个事务被锁的时候，就要遍历它所依赖的所有的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
​

假如有1000个并发线程要同时更新同一行，那么死锁检测的操作就是百万级的，需要耗费大量的CPU资源。
​

怎么解决热点行更新导致的性能问题？

1. 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭，这种操作有风险。
2. 控制并发度，控制同时操作同一行记录的线程数（客户端数），比如通过Redis中间件进行控制或者通过消息队列进行并发控制
3. 业务逻辑及数据库表结构优化，将一行数据分解成多行，将原来对一行数据的写操作分摊到多行，降低写压力，但这样业务设计上复杂度就会提高很多

间隙锁

间隙锁相关可参考此文章：https://blog.csdn.net/qq_43295093/article/details/120720035

加锁规则

tip：加锁规则这部分需要阅读过间隙锁文章的基础才可以阅读。
​

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

锁是在执行过程中一个一个加的

举例：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

当执行以下查询时，加什么锁？

begin;
select id from t where c in(5,20,10) lock in share mode;

通过explain命令可以看到使用到了索引"c"。
​

加锁过程如下：

• 在查找c=5的时候，先锁住(0,5]，由于c不是唯一索引，因此向后查找到10，不符合条件，加上(5,10)的间隙锁
• 同样，查找c=20的时候，先锁住(15,20]，向后查找25不符合条件，因此加上(20,25)间隙锁
• 查找c=10时同理

​

整个过程中的加锁是顺序的，由于锁是一个一个加，因此可能会在加锁过程中发生死锁（间隙锁不互斥，行锁才互斥），比如执行以下查询：

select id from t where c in(5,20,10) order by c desc for update;

这个查询会加上间隙锁和5、20、10这三个行锁，行锁的加锁顺序为20、10、5，因此可能会出现在加行锁的过程中和其他线程发生死锁。
如何查询死锁信息
通过show engine innodb status可以查看最后一次死锁相关信息，还有其他的一些具体的加锁信息。

所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的

举例：

sessionA	sessionB
begin; select * from t where id>10 and id<=15 for update;
	delete from t where id=10; insert into t values(10,10,10);(block)

sessionA在sessionB删除id=10这行记录前加锁为(10,15]，在sessionB删除id=10这行记录后，加锁范围会变为(5,15]，范围变大，导致sessionB插入被间隙锁阻塞。通过show engine innodb status也可以查看到相关信息。