Mysql的锁（史上最全）

按属性分

共享锁

共享锁又叫读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

select......lock in share mode

排他锁

排他锁又叫写锁，简称X锁，当一个事务为某个数据对象加上排他锁时，其他事务不能对该数据对象进行读取或修改，直到排他锁被释放。排他锁的主要目的是防止两个或多个事务同时修改同一数据对象，从而避免数据不一致的问题。

update , delete , insert

select......for update

  

按粒度分

全局锁

Flush tables with read lock（FTWRL）

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。

全局锁典型的应用场景是做全库的逻辑备份。

主动释放全局锁的命令为：UNLOCK TABLES。另外，当会话断开时，全局锁会被自动释放。

风险点：

主库备份风险：在主库上执行 FTWRL 会阻塞所有的写操作，导致业务停顿。

从库备份风险：在从库上执行 FTWRL 会阻塞从库应用主库的 binlog，从而可能导致主从延迟增加。

通过加全局锁会影响业务，那有更好的办法吗？

在事务隔离时，我们学习了事务隔离中的可重复读隔离级别是能够得到一致性视图的。而一致性视图能够确保数据的逻辑一致性。

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

既然要全库只读，为什么不使用set global readonly=true的方式呢？

虽然 readonly 也能够设置全库为只读状态。但还是建议使用 FTWRL，原因有两个：

1、在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大，不建议你使用。

2、在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

表级锁分为以下几种：

表共享锁（Table Read Lock）、表独占锁（Table Write Lock）。

意向共享锁、意向独占锁

AUTO-INC自增锁

元数据锁

表共享锁和表独占锁

在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候只锁住一行呢？ 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

MyISAM和MEMORY存储引擎采用的是表级锁

LOCK TABLES 表名 READ;

LOCK TABLES 表名 WRITE;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

UNLOCK TABLES;

意向锁

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；

在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

意向锁的目的是为了快速判断表里是否有记录被加锁。

意向锁是表锁还是行锁？

首先可以肯定的是，意向锁是表级别锁。意向锁是表锁是有原因的。

当我们需要给一个加表锁的时候，我们需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则我们直接判断一次就知道表中是否有数据行被锁定了。

AUTO-INC锁

自增锁是一种比较特殊的表级锁。并且在事务向包含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁，假设事务 A 正在做这个操作，如果另一个事务 B 尝试执行 INSERT语句，事务 B 会被阻塞住，直到事务 A 释放自增锁。

上面说的那种阻塞情况只是自增锁行为的其中一种，可以理解为自增锁就是一个接口，其具体的实现有多种。具体的配置项为 innodb_autoinc_lock_mode ，通过这个配置项我们可以改变自增锁中运行的一些细节。

其实在 InnoDB 中，把锁的行为叫做锁模式可能更加准确，那具体有哪些锁模式呢，如下：

传统模式（Traditional）

连续模式（Consecutive）

交叉模式（Interleaved）

分别对应配置项 innodb_autoinc_lock_mode 的值0、1、2.

传统模式：

传统模式（Traditional），说白了就是还没有锁模式这个概念时，InnoDB 的自增锁运行的模式。只是后面版本更新，InnoDB 引入了锁模式的概念，然后 InnoDB 给了这种以前默认的模式一个名字，叫——传统模式。

我们知道，当我们向包含了 AUTO_INCREMENT 列的表中插入数据时，都会持有这么一个特殊的表锁——自增锁（AUTO-INC），并且当语句执行完之后就会释放。这样一来可以保证单个语句内生成的自增值是连续的。

这样一来，传统模式的弊端就自然暴露出来了，如果有多个事务并发的执行 INSERT 操作，AUTO-INC的存在会使得 MySQL 的性能略有下降，因为同时只能执行一条 INSERT 语句。

连续模式

连续模式（Consecutive）之所以提出这种模式，是因为传统模式存在影响性能的弊端，所以才有了连续模式。

在锁模式处于连续模式下时，如果 INSERT 语句能够提前确定插入的数据量，则可以不用获取自增锁，举个例子，像 INSERT INTO 这种简单的、能提前确认数量的新增语句，就不会使用自增锁，这个很好理解，在自增值上，我可以直接把这个 INSERT 语句所需要的空间流出来，就可以继续执行下一个语句了。

但是如果 INSERT 语句不能提前确认数据量，则还是会去获取自增锁。例如像 INSERT INTO ... SELECT ... 这种语句，INSERT 的值来源于另一个 SELECT 语句。

交叉模式

交叉模式（Interleaved）下，所有的 INSERT 语句，包含 INSERT 和 INSERT INTO ... SELECT ，都不会使用 AUTO-INC 自增锁，而是使用较为轻量的 mutex 锁。这样一来，多条 INSERT 语句可以并发的执行，这也是三种锁模式中扩展性最好的一种。

并发执行所带来的副作用就是单个 INSERT 的自增值并不连续，因为 AUTO_INCREMENT 的值分配会在多个 INSERT 语句中来回交叉的执行。

优点很明确，缺点是在并发的情况下无法保证数据一致性，这个下面会讨论。

Binlog 一般用于 MySQL 的数据复制，通俗一点就是用于主从同步。在 MySQL 中 Binlog 的格式有 3 种，分别是：

Statement 基于语句，只记录对数据做了修改的SQL语句，能够有效的减少binlog的数据量，提高读取的性能

Row 只记录被修改的行，所以Row记录的binlog日志量一般来说会比Statement格式要多。基于Row的binlog日志非常完整、清晰，记录了所有数据的变动，但是缺点是可能会非常多，例如一条update语句，有可能是所有的数据都有修改；再例如alter table之类的，修改了某个字段，同样的每条记录都有改动。

Mixed  Statement和Row的结合，例如像alter table之类的对表结构的修改，采用Statement格式。其余的对数据的修改例如update和delete采用Row格式进行记录。

如果 MySQL 采用的格式为 Statement ，那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。如果此时我们采用了交叉模式,INSERT 同时交叉执行，并且 AUTO_INCREMENT 交叉分配将会直接导致主从之间同行的数据主键 ID 不同。而这对主从同步来说是灾难性的。

如果你的 DB 有主从同步，并且 Binlog 存储格式为 Statement，那么不要将 InnoDB 自增锁模式设置为交叉模式，会有问题。

而后来，MySQL 将日志存储格式从 Statement 变成了 Row，这样一来，主从之间同步的就是真实的行数据了，而且 主键ID 在同步到从库之前已经确定了，就对同步语句的顺序并不敏感，就规避了上面 Statement 的问题。

基于 MySQL 默认 Binlog 格式从 Statement 到 Row 的变更，InnoDB 也将其自增锁的默认实现从连续模式，更换到了效率更高的交叉模式。

MDL元数据锁

MySQL 中执行 DDL 语句时，经常会发现语句没有在你预期的时间完成，这时候我们通常会在mysql输入终端输入show full processlist ，来看看发生了什么状况。当你看到 waiting for table metadata lock 时，那就碰到元数据锁了。

Meta Data Lock

对数据库表进行操作时，会自动给这个表加上 MDL：

1、对一张表进行 CRUD 操作时，加的是 MDL 读锁；

2、对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

元数据锁是server层的锁，表级锁，每执行一条CRUD 、DDL语句时都会申请metadata锁， CRUD操作需要metadata读锁，DDL操作需要metadata写锁，metadata加锁过程是系统自动控制，无法直接干预，读锁和写锁的阻塞关系如下：读锁和写锁之间相互阻塞。写锁和写锁之间互相阻塞。读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行

申请metadata锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作, show processlist会发现大量‘waiting for table metadata lock的线程，数据库连接很快就会消耗完，导致业务系统无法正常响应。

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 等其他引擎并不支持行级锁。

Record Lock 记录锁：单个行记录(索引记录)上的锁。

Gap Lock 间隙锁：锁定一个范围，但不包括记录本身。

Next-Key Lock 临键锁 ：（记录锁+ 间隙锁）锁定一个范围，并且锁定记录本身。

Next-Key Lock

原则一：加锁的基本单位为next-key lock且范围为前开后闭。

原则二：查找过程中只有访问到的对象才会加锁，例如走全表扫描的情况，这种在扫描前就会给全表加上next-key lock。

优化一：索引上的等值查询，在给唯一索引加锁时，next-key lock会退化为行锁，因为主键是唯一的。

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

Next-Key Lock

创建一个表t 主键id 索引c

• CREATE TABLE `t` (
•   `id` int(11) NOT NULL,
•   `c` int(11) DEFAULT NULL,
•   `d` int(11) DEFAULT NULL,
•   PRIMARY KEY (`id`),
•   KEY `c` (`c`)
• ) ENGINE=InnoDB;
• insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、等值查询主键

2、 等值非唯一索引

1、所需要的信息我们在普通索引C上就可以找到，不会进行回表，根据原则一，Session A加锁范围为普通索引C上的(0,5]。

2、根据优化二，继续向右寻找，id = 10不满足条件，范围为(5,10]的next-key lock退化为间隙锁(5,10)。3、根据原则二，Session B的update sql可以执行，因为主键索引表上没有加锁。

3、 主键索引范围锁

4、非唯一索引范围锁

按模式分

悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法; 悲观锁指的是采用一种持悲观消极的态度，默认数据被外界访问时，必然会产生冲突，所以在数据处理的整个过程中都采用加锁的状态，保证同一时间，只有一个线程可以访问到数据，实现数据的排他性；通常，数据库的悲观锁是利用数据库本身提供的锁机制去实现的.

乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法；乐观锁（ Optimistic Locking ） 是相对悲观锁而言，乐观锁是假设认为即使在并发环境中，外界对数据的操作一般是不会造成冲突，所以并不会去加锁(所以乐观锁不是一把锁)，而是在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回冲突信息，让用户决定如何去做下一步，比如说重试，直至成功为止；

在表中增添一个字段作为该记录的版本标识，比如叫version，每次对该记录的写操作都会让 version+ 1。所以当我们读取了数据(包括version)，做出更新，要提交的时候，就会拿取得的version去跟数据库中的version比较是否一致，如果一致则代表这个时间段，并没有其他的线程的也修改过这个数据，给予更新，同时version + 1；如果不一致，则代表在这个时间段，该记录以及被其他线程修改过了， 认为是过期数据，返回冲突信息，让用户决定下一步动作，比如重试（重新读取最新数据，再过更新）update table set num = num + 1 , version = version + 1 where version = #{version} and id = #{id}