MySQL全局锁，表锁，行锁介绍

数据库锁设计的初衷是处理并发问题，作为多用户共享的资源，当出现并发访问的时候，数据库需要合理的控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构
根据加锁的范围，MySQL 里的锁大概可以分为全局锁，表级锁，行锁三类

一、全局锁

全局锁就是对整个数据库实例加锁，MySQL 提供了一个加全局锁的方法：
flush table with read lock; 又叫做FTWRL，常见的使用场景是全库逻辑备份，执行完这个语句后整个库处于只读状态，为了保证在备份期间数据逻辑上的完整性；
用unlock tables主动释放锁，也可以在客户端断开的时候自动释放

但是让整库处于只读，听上去就很危险：

1. 如果主库正在备份，那么备份期间都不能执行增删改，业务基本上处于停摆状态
2. 如果从库正在备份，那么备份期间不能执行主库同步过来的 binlog，导致主从延迟

在官方自带的mysqldump中，使用参数-single-transaction的时候，导出数据之前就会启动一个事务，来确保拿到一致性事务，由于 MVCC 的支持，这个过程中数据是可以正常更新的，但是使用这个参数需要保证存储引擎支持这个隔离级别，所以当存储引擎是 MyISAM 的时候是不支持事务的，所以只能使用FTWRL，这也是 InnoDB 替代 MyISAM 的原因之一

二、表锁

1. 表锁

MySQL 里表锁分为两种：一种是表锁，一种是元数据锁(mate data lock(MDL))
表锁的语法：lock tables T1 read,T2 wirte，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放，但是需要注意的是：lock tables语法除了会限制别的线程的读写外，也限定了当前线程接下来的操作对象

比如在线程 A 中执行：lock tables t1 read ,t2 write;则其他线程写 t1 和读写 t2 都会被阻塞，同时线程 A 在执行 unlock tables 之前，也只能是读 t1 和读写 t2 操作

2.MDL 锁

另一类表锁是 MDL，MDL 不需要显示的使用，在访问一个表的时候会被自动加上，DML 的作用是保证读写的正确性。假设线程 A 正在读写表 T，而此时线程 B 给 T 表删除了一列字段，这样就导致线程 A 读取的数据就不对了，这是不被允许的
因为在 MySQL5.5 版本引入 MDL，当对一个表进行增删改查操作的时候，加 MDL 读
锁，当要对表结构进行变更操作的时候也会加 MDL 写锁

• 读锁之间不互斥，所以可以有多个线程对同一张表进行增删改查
• 读写锁，写锁之间是互斥的，是用来保证表结构操作的安全性，所以当一个线程要给表加一个字段，要等另外一个线程事务执行完成之后才能继续执行

如何安全的给一个表加字段？

sessionA	sessionB	sessionC	sessionD
select * from t
	select * from t
		alert table add f int(阻塞)
			select * from t（阻塞）

从以上图中可以看出，sessionA 先启动，这时候会对 t 表加一个 MDL 读锁，此时 sessionB 需要的也是读锁，所以可以正常执行，但是 sessionC 来新增一个字段，此时 t 表是由 MDL 的读锁的，所以 sessionC 申请写锁是处于阻塞状态，这时候 sessionD 也来获取读锁，也会被 sessionC 阻塞，如果该表查询非常频繁，并且客户端有重试机制，也就是超时会重启一个新得 session 再请求的话，这个库的线程池很快就会爆满。

在事务事务中的MDL锁，在语句执行开始申请，等到整个事务提交后再释放

那么我们该怎样安全的给小表加字段？

1. 解决长事务，事务不提交就会一直占着 MDL 锁，如果存在长事务考虑 kill 或者暂停 DDL 操作
2. 设置等待时间，如果等不到事务结束就放弃 DDL 操作，后续再重试

三、行锁

我们知道 MyISAM 是只能支持到表锁级别的，而 InnoDB 是可以支持行锁的
顾名思义，行锁就是针对数据表中的行记录的锁

比如事务 A 更新了一行，而这个时候事务 B 也要更新同一行，则必须等事务 A 的操作完成了之后才能继续更新

在 InnoDB 中，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是要等到事务结束时才释放，这就是两阶段锁协议
知道了两阶段锁协议的话，如果你的事务中需要锁多个行，要把最可能造成锁冲突的锁放最后面，防止长事务导致锁时间过长；

1. 死锁

死锁： 当并发系统中不同线程出现循环资源依赖，涉及到的线程都在等待别的线程释放资源时，就会导致这几个线程进入无线等待的状态，称为死锁

事务 A	事务 B
begin;
update t set k = k + 1 where id = 1;	begin;
	update t set k = k + 1 where id = 2;
update t set k = k + 1 where id = 2;
	update t set k = k + 1 where id = 1;

以上情况：事务 A 拿着 ID=1 的锁，需要 ID=2 的锁，而事务 B 拿着 ID=2 的锁，需要 ID=1 的锁，事务 AB 都在等待对方释放自己需要的锁，就导致了死锁，当出现死锁后，有几种解决方案：

• 进入等待，直到超时，可以通过innodb_lock_wait_timeout设置，默认50s，但是这个时间太长基本上是无法被接受的，如果设置为 1s 又不能判断是否为线程等待，而不是死锁了
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务继续执行，将参数innodb_deadlock_detect设置为 on，表示开启这个功能

2.死锁检测

原理：当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待

由于超时时间不好确定，正常情况下我们都是采用死锁检测的方式，并且innodb_deadlock_detect默认值本来就是 on，虽然主动死锁检测在发生死锁的时候能够快速的处理掉，但是他也是有额外负担的。

假设一个秒杀场景，有 1000 个并发线程同时更新库存，那么死锁检测就是 100w 这个数量级别，虽然最终检测结果是没有死锁，但是这期间要消耗大量的 CPU 资源，因此就会发现 CPU 利用率很高，但是每秒却执行不了几个事务，那么该如何解决这个问题呢？

• 可以在代码里避免并发，在减少库存中加锁，依次扣减库存，这样就一定不会出现死锁，这时候死锁检测就不会那么忙了

问题：如果要删除一个表的前 10000 行数据，有以下三种方式可以做到，哪一种是最合适的呢？

1. 第一种，直接执行 delete from T limit 10000;
2. 第二种,在一个连接里，循环 20 次，每次 delete from T limit 500
3. 第三种，在 20 个连接中同时执行 delete from T limit 500

结果应该很明显，第一中一次删除行太多，会导致大事务，主从延迟，第二种是合理的，第三种由于并发执行，会人为的造成锁竞争，可能导致死锁问题

---

我是一零贰肆，一个关注Java技术和记录生活的博主。

欢迎扫码关注“一零贰肆”的公众号，一起学习，共同进步，多看路，少踩坑。