MySQL深入学习---(4) 带你遨游 “锁” 家族

锁家族有哪些成员呢？

• 为了处理并发问题，数据库需要合理的控制资源的访问规则，这时候锁就是用来实现这些访问规则的重要数据结构。
• 根据加锁的范围，MySQL里面的锁大致分为“全局锁”，“表级锁”，“行锁”。

1、全局锁

• MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。其会整个库处于只读状态，而之后其它线程的增删改语句和事务的提交，表结构的修改与创建就会被阻塞。unlock tables可以解除全局锁。
• 全局锁的典型使用场景是：做全库逻辑备份。也就是把整个库的表都select出来存成文本。

全局锁的不足和优点：

（1）如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
（2）如果在从库上备份，那么备份期间从库不能执行主库同步过来得binlog，会导致主从延迟。
（3）如果不加锁，备份系统备份的得到的库不是同一个逻辑点，这个视图是逻辑不一致的。也就是说，不同表之间的执行顺序不同进而备份的时间不同，会导致发生数据不一致。
（4）可在重复读隔离级别下开启一个事务，这样就能保证一个方法能够拿到一致性视图。

• 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。但是只有支持事务的引擎才能使用这个参数。

• 如果像MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

• 所以，single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

2、表级锁

• MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

（1）表锁

语法： lock tables … read/write

• 与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。InnoDB这种支持行锁的引擎一般不使用lock tables命令来控制并发的，毕竟锁住整个表的影响面还是很大的。

例如： 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

（2）MDL

• MDL不需要显式使用，在访问一个表的时候会被自动加上。其作用是：保证读写的正确性；保证表数据与表结构一致、修改表结构的安全性。

• 在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁，其它线程可以操作表数据；当要对表做结构变更操作的时候，加 MDL 写锁，其它线程无法修改表结构和操作表数据。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

• 虽然MDL锁是系统默认会加的，但是不能忽略一个机制：那就是给一个小表加个字段，导致整个库挂了。

当给一张表加字段，或者修改字段，或者加索引，这时都需要扫描全表的数据。例如，假设表 t 是一张小表，这里MySQL是5.6版本。

解读过程：

• SessionA先启动，这时会对表 t 加上一个MDL读锁，sessionB也会加了MDL读锁，之后会导致后面的sessionC阻塞。是因为 session A 的 MDL 读锁还没有释放，那么sessionC申请写锁被阻塞，锁队列，一旦进去就开始影响后面的，这将会导致后面的sessionD等申请读锁都被阻塞。
• 事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

• 那么我们如何安全地给小表加字段呢？

首先要解决长事务，如果事务不提交，就会一直占着MDL锁。例如你要做DDL变更的表刚好有长事务在执行，这时可以考虑暂停DDL或者kill掉这个长事务。

• 如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加一个字段，这时又应该怎么做呢？

这时候 kill 掉可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再通过命令重复这个过程。简而言之，不管涉及到多线程还是锁相关的部分，不管是Java还是MySQL，解决的方法都差不多，即为了防止死锁，通过加入超时时间的方法解决。

总结：

• 非热点表：解决长事务；
• 热点表：在 alter table 语句里面设定等待时间。

3、行锁

• 行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。
• MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

MyISAM被Innodb取代的原因：

1. 不支持事务
2. 不支持行锁

（1）两阶段锁

• 在下面的操作序列中，事务 B 的 update 语句执行时会是什么现象呢？假设字段 id 是表 t 的主键。
  

这里事务 B 的 update 语句会被阻塞，直到事务 A 持有的两个记录的行锁执行 commit 释放之后，事务 B 才能继续执行。也就是说，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

两阶段锁，锁的添加与释放分到两个阶段进行，之间不允许交叉加锁和释放锁。 也就是在事务开始执行后为涉及到的行按照需要加锁，但执行完不会马上释放，而是在事务结束时再统一释放他们。

• 两阶段锁协议简单说：

假设一个事务中有多行更新操作，因为更新操作会给表中的数据加上行锁，当执行完更新操作后，锁不会立马释放，而是等待commit成功后才会释放。

• 两阶段锁带来的影响：

如果把加锁的操作（更新操作）放在整个事务的所有操作的前面，且此时对同一行的并发操作比较多，那么就意味着行被加锁的时间相对于把这些加锁的代码放在后面要长，阻塞的总体时间比较长，如果加锁的代码放在最后，阻塞的时间就会相对缩短，并且距离锁释放的时间更近，可以提高整体性能。

总结：

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

（2）死锁和死锁检测

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

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 超时检测：

直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置。

• 死锁检测：

发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

上面两种策略的缺点：

1）在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
2）我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
3）死锁检测要耗费大量的 CPU 资源，但是还是建议使用第二种策略。

热点行更新的解决策略：

1）关闭死锁监测，但是关闭会意味着可能会出现大量的超时。
2）降低并发度，Server层限流，即同一时间进入更新的线程数。
3）拆行，将一行拆成多行，减少单行上的锁冲突。

（3）面试题：

1、如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

• 第一种，直接执行 delete from T limit 10000;
• 第二种，在一个连接中循环执行 20 次 delete from T limit 500;
• 第三种，在 20 个连接中同时执行 delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

答：方法二

• 第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长，会导致其他客户端等待资源时间较长；而且大事务还会导致主从延迟。
• 第二种方式，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。
• 第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

2、如何避免死锁问题？

答：

• 超时检测
• 死锁检测