MySQL学习笔记：锁2

序言

《MySQL45讲》

锁的分类

按照标准划分：锁：共享锁和排他锁

按照加锁范围，锁分为：全局锁、表级锁、行锁。

全局锁

使用场景：全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
对于支持事务的引擎来说：使用mysqldump –single-transaction 参数，更好些。
如果是不支持事务的引擎，myisam，可以使用Flush tables with read lock (FTWRL)加全局读锁。

表级锁

表锁

表锁的语法是 ：lock tables … read/write，与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

元数据锁

metadata lock ： MDL

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。

所有对表的增删改查操作都需要先申请 MDL 读锁

通俗点说，就是当你打算修改表结构时，比如增加一个字段或者删除一个字段，此时另一个事务却在增删改查，如果不加锁，那么它得到的数据结构就会不一致。

这里需要注意一个问题：

如上图，从sessionC阻塞开始，后续sessionD也被阻塞啦，也就会导致即使是小表，也依然可能会导致整个库挂掉。

Q：如何安全地给小表加字段？
A：

1. 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

2. 但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
   语法如下：

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 
1
2

行锁

两阶段锁协议

定义：

在InnoDB中，行锁是在需要的时候加上去的，但并不是不需要了就立刻释放，而是要等到事务结束时，才释放。

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

但是调整SQL语句并不能避免死锁。

死锁

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

解决死锁的策略：

1. 设置超时时间：innodb_lock_wait_timeout设置超时时间
2. 另一个策略：发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以执行，将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

超时默认时间为：50s；

死锁检测

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

Q : 所有事务都要更新同一行的场景:

A :每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

**怎么解决由这种热点行更新导致的性能问题呢？**问题的症结在于，死锁检测要耗费大量的 CPU 资源。

解法：

1. 关闭死锁检测，利用锁超时。业务有损，会有大量的超时。
2. 控制并发度：如果有中间件（像公司有DAL中间件）。或者修改MySQL源码，思路：相同行的更新，在进入引擎之前排队。
3. 业务设计上优化：将一行改成逻辑上的多行来减少锁的冲突。可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

个人认为，最好的办法就是，控制并发度，利用中间件来做。

特别说明：

1. 如果他要加锁访问的行上有锁，他才要检测。

2. 一致性读不会加锁，就不需要做死锁检测；

3. 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：

   B在等A，
   D在等C，
   现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A