根据加锁的范围，可以分为全局锁，表级锁，行锁。

全局锁

使用做全库逻辑备份的场景。

如果引擎不支持事务，那么建议使用FTWRL命令，而对于InnoDB引擎，建议使用官方自带的备份工具mysqldump，使用参数-single-transaction来拿到一致性视图，确保这个过程可以正常更新数据。

表级锁

表锁

加锁：lock tables xxx read/write

释放锁：unlock tables

对应InnoDB，建议使用行锁，毕竟锁住整个表影响太大

MDL

MDL是元数据锁（meta data lock）。

加锁是访问一张表的时候系统自动加上的，而释放锁是事务提交的时候才会释放。所以在一个长事务中，可能会导致锁住线上查询和更新语句。

所以引出一个问题：如何安全的给小表加字段？

1、解决长事务：事务不提交，MDL锁不会释放，可以查看information_schema.innodb_trx表，如果发现长事务，先暂停DDL，在kill掉这个长事务

2、如果变更的表是一个热点表，请求很频繁，这时候Kill可能未必管用，因为新的请求马上就来了。理想情况：在alter table语句里面设定等待时间，如果在这 期间能够拿到MDL写锁最好，如果拿不到，也不要阻塞后面的业务语句，先放弃。然后通过重试命令重试

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

行锁

两阶段锁协议：

加锁：需要的时候加

释放锁：并不是不需要就释放锁，而是等整个事务结束才释放锁

由这个协议，可以对我们使用事务带来帮助：如果事务中需要锁多个行，要把最可能发生锁冲突、最可能影响并发度的锁尽量往后放。

举个例子：

现在有一个顾客A要在影院B购买电影票，涉及的操作如下：

1、从顾客A从账户中扣除电影票价

2、给影院B的账户余额加上电影票价

3、记录一条交易日志

为了保证交易的原子性，这3个操作在同一个事务中进行。如果现在有另外一个用户B需要在影院B购买电影票，那么这两个事务冲突的部分就是语句2了。由于两阶段协议，无论怎么安排语句的顺序，所有操作需要的行锁都是要在事务提交的时候才释放。所以，可以将语句2的顺序放在最后，比如3、1、2这样的顺序，那么影院账号余额这一行的锁时间就最少。这样就最大程度的 减少了事务之间的锁等待，提升了并发度。

但如果现在影院搞活动，可以低价预售一年内的所有电影票，而且这个活动只做一天，那么会出现什么情况呢？比如用户A购买了a这张电影票，需要给影院B的余额加上a的票价，用户B购买了b这张电影票，需要给影院B的余额加上b的票价。如果这个时候用户A要购买b这张电影票，准备给影院B的余额加上b票价的时候就会被锁住，对于用户B购买电影票a来说也是一样，也就是说，这个时候发生了死锁。就会导致MySQL挂掉，表现形式就是CPU利用率很高，但是执行的事务却很少。

死锁检测

发生了死锁，那么就要对死锁进行检测，有2种方式

1、进入等待，直到超时时间

2、发现死锁，回滚死锁链中的某一个事务

第一种方式，超时时间设置太大太小都不好，所以正常情况都是采用第二种。但是这样也会有额外的负担，比如每个新来的被堵住的线程，都要判断会不会由于自己的加入而导致死锁，时间复杂度为O(N)。

所以说，死锁检测会耗费大量的CPU资源。怎么解决由热点行更新导致的性能问题？

1、临时把死锁检测关掉，风险太大

2、控制并发度。

​ 但是不能在客户端控制，因为会出现客户端很多的情况，比如600个客户端，即使每个客户端控制到只有5个并发线程，汇总到服务端后，峰值并发数也有3000。

​ 所以需要在客户端控制，基本思路：进入引擎之前排队，这样在innoDB内部就不会有大量的死锁检测工作了。当然，也可以将一行改成逻辑上的多行，比如影院余额放在10个记录上，这样冲突概率就变成了原来的1/10。但是，这种做法就需要考虑当用户退款，一部分行记录变成0的时候，代码要有特殊处理。所以这种做法需要根据业务做详细设计。

幻读

首先要知道：

1、在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在”当前读“下才会出现。

2、幻读仅专指”新插入的行“。

先通过mysql45讲上的一个例子说明幻读导致数据不一致的问题：

假设扫描到的行都被加上了写锁

由于session A把所有的行都加了写锁，所以session B在执行第一个update语句的时候就被锁住 了。需要等到T6时刻session A提交以后，session B才能继续执行。 这样对于id=0这一行，在数据库里的最终结果还是 (0,5,5)。在binlog里面，执行序列是这样的：

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行，d改成100*/

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到，按照日志顺序执行，id=0这一行的最终结果也是(0,5,5)。所以，id=0这一行的问题解决了。 但同时也可以看到，id=1这一行，在数据库里面的结果是(1,5,5)，而根据binlog的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地，把所有的记录都 上了锁，还是阻止不了id=1这一行的插入和更新呢？

原因很简单。在T3时刻，我们给所有行加锁的时候，id=1这一行还不存在，不存在也就加不上锁：

因为session C是最开始完成的事务，它在session A给所有行加锁之前完成。

Next-Key Lock

Record Locks

相当于索引上的行锁，对索引记录的锁。

锁定一个记录上的索引，而不是记录本身，即锁是加在索引上的。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用

间隙锁

官方定义：间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。

间隙锁（gap lock）是为了解决幻读引入的。它锁的是两个值（索引）之间的空隙，不包含索引本身。间隙锁之间不存在冲突关系。跟间隙锁存在冲突 关系的，是“往在这个间隙中插入一个记录”这个操作。

需要注意的是，间隙锁在可重复读隔离级别下才有效，并且使用间隙锁会出现死锁的情况！！！

加锁范围：两边都是开区间 ( )

间隙锁会造成并发度低下，并且出现死锁的情况，所以可以用下面这种方式去掉gap lock:

RC（读提交）隔离级别+binlog_format=row，binlog格式设置为row，是为了解决可能出现的数据和日志不一致的问题。这也是不少公司使用的配置组合。

Next-Key Lock

行锁（实际上是索引记录锁）+间隙锁，不仅锁定一个记录上的索引，还锁定索引之间的间隙

前开后闭区间：( ]，例如(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]，这个supermum是InnoDB给每个索引加的一个不存在的最大值，因为正无穷是开区间，不符合前开右闭，官方是这样描述的：

对于最后一个时间间隔，next-key lock 锁定索引中最大值以上的间隙，并且“ supremum ” 伪记录的值高于索引中的任何实际值。supremum 不是真正的索引记录，因此，实际上，这个 next-key 锁只锁定最大索引值之后的间隙。

“有行”才会加行锁，如果查询条件没有命中行，那么就会加next-key lock或间隙锁，如果命中，那么还需要在next-key lock的基础上加这条记录本来就有的锁。比如使用select查询这条记录并加了读锁，那么就会加读锁和next-key lock

加锁规则

分析加锁规则的时候可以用next-key lock分析，但是具体执行的时候，是要分成间隙锁和行锁两段来执行的。

2个原则

1、加锁的基本单位是next-key lock，前开后闭区间

2、查找过程中访问到的对象才会加锁

2个优化

1、索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁

2、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

这两个优化其实是官方文档中InnoDB中不同SQL语句设置的锁15.7.3这节其中一段的定义

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.

    For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap 	 before it.

    For other search conditions, and for non-unique indexes, InnoDB locks the index range scanned, using gap 	 locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For 		information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

意思和上面说的2个优化是一样的：

对于锁定读取 （SELECT with FOR UPDATE或FOR SHARE）、 UPDATE和 DELETE语句，采用的锁定取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

	对于具有唯一搜索条件的唯一索引， InnoDB只锁定找到的索引记录，而不锁定它之前的间隙。

	对于其他搜索条件，以及对于非唯一索引， InnoDB锁定扫描的索引范围，使用间隙锁 或 next-key 来阻止其他会话插入范围所覆盖的间	 隙。

1个bug

唯一索引上的范围查询会访问到不满足条件的第一个记录为止。

但是我自己测试的话这个bug已经没有了，mysql版本是8.0.20，应该是官方已经修复了（查询资料发现是8.0.18版本修复的）。

表结构如下：

mysql> CREATE TABLE `t20`(
    -> `id` int(11) NOT NULL,
    -> `c` int(11) NOT NULL,
    -> `d` int(11) DEFAULT NULL,
    -> PRIMARY KEY(`id`),
    -> KEY `c` (`c`)
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.06 sec)

mysql> insert into t20 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

bug还未修复的时候，执行以下语句

session A是一个范围查询，所以按照原则1，在索引id上会加(10,15]这个next-key lock，并且id是唯一索引，扫描到id=15这行就停止扫描了。但是按照1个bug说的那样，InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20，由于这是个范围扫描，所以会将(15,20]这个next-key lock加上。所以session B更新id=20这行和session C更新id=16这行都会被锁住。也据就是说锁被扩大化了。

而当我使用8.0.20测试的时候，session B和session C的更新操作都不会被锁住。

InnoDB： 在SELECT...FOR [SHARE|UPDATE]使用WHERE 指定范围的条件执行查询时获取了不必要的下一个键锁 ，导致锁定太多行。此问题最常见的情况已得到解决，因此仅锁定与搜索范围相交的行和间隙。（错误＃29508068）

测试结果如下：

mysql> update t20 set d=d+1 where id=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> insert into t20 values(16,16,16);
Query OK, 1 row affected (0.01 sec)

当然，如果要操作的是id=15这行，那么还是会被锁住。