全局锁、表锁、行锁

全局锁

全局锁的典型使用场景是，做全库逻辑备份
历史做法：flush table with read lock;确保不会有其他线程对数据库做更新，然后对整个库做备份。

mysql> flush table with read lock;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   55 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
6 rows in set (0.01 sec)

mysql> update d set d = 99 where id = 0;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

缺陷：

• 备份期间都不能执行更新，业务基本上就得停摆；
• 主从同步导致数据不一致

官方自带的逻辑备份工具是 mysqldump，mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

能够支持事务隔离的，就可以通过事务隔离的手段来做到，有的引擎不支持（如：MyISAM不支持），需要通过FTWRL来给全局上锁

为什么不用set global readonly=true？

• readonly 的值会可能会被用来做其他逻辑，如：主备库判断
• 异常机制差异（客户端发生异常断开，FTWRL会释放全局锁，整个库回到可以正常更新的状态，readonly 不会）

表级锁

表锁

语法：lock tables … read/write
解除：unlock tables

写锁（排他锁）：自己能写
读锁（共享锁）：都能读，不能写

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL（metadata lock)

MDL（MySQL 5.5 版本中引入） 的作用是，保证读写的正确性，MDL 不需要显式使用，在访问一个表的时候会被自动加上。

• 读锁：对一个表做增删改查操作的时
• 写锁：当要对表做结构变更操作的时

读锁与读锁止键不互斥，写锁与写锁，读锁与写锁之间互斥，用来保证变更表结构操作的安全性

注意安全性：


session A 先启动，这时候会对表 t 加一个 MDL 读锁。
session B 需要的也是 MDL 读锁，因此可以正常执行。
session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放。session D 也会被 blocked。

导致问题：如果查询语句频繁，且客户端有重试机制会打爆Mysql
结论：事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

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

1. information_schema 库的 innodb_trx 表中如果有长事务，暂停 DDL，或者 kill 掉这个长事务。
2. alter table 语句里面设定等待时间。（如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程）思想：防止死锁，加入超时时间机制

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法

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

MySQL 8.0.1在select语法中给出了NOWAIT特性(官方博客)，也就是我们可以在查询时如果需要等待行锁，则立即退出报错。这种特性其实也可以用在MDL锁，这样我们如果在进行DDL语句时，通过NOWAIT检测一下是否有相关元数据锁等待，如果有则立即退出，这样运维人员即可及时判断出能否进行ddl操作，而不至于在执行ddl时，造成大量业务中断。

行锁

行锁是引擎层的各自实现，MyISAM 引擎就不支持行锁（并发控制只能使用表锁，任何时刻只能有一个更新在执行，这就会影响到业务并发度）。

两阶段锁

事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

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

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

场景：买电影票

1.从顾客 A 账户余额中扣除电影票价；
2.给影院 B 的账户余额增加这张电影票价；
3.记录一条交易日志。

步骤2可能造成事务冲突，因为都是修改同一行，所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁和死锁检测

死锁概念：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源，进入无限等待的状态。

策略：

1. 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置（不可能直接把这个时间设置成一个很小的值，比如 1s，容易造成误伤…）。

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

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

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.01 sec)

怎么解决热点行更新？

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

解决方案：

• 可以临时把死锁检测关掉（会出现大量的超时，这是业务有损）
• 控制并发度。（可以考虑在中间件实现；也可以做在 MySQL 里面。基本思路：对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。）
• 将一行改成逻辑上的多行来减少锁冲突（依赖详细的业务设计）