mysql范围查询跳过排它锁_MySQL InnoDB 锁介绍，及不同 SQL 语句分别加什么样的锁...

原标题：MySQL InnoDB 锁介绍，及不同 SQL 语句分别加什么样的锁

来源：iceman1952

blog.csdn.net/iceman1952/article/details/85504278

本文中，我们详细介绍MySQL InnoDB存储引擎各种不同类型的锁，以及不同SQL语句分别会加什么样的锁。

阅读提示

1、本文所参考的MySQL文档版本是8.0，做实验的MySQL版本是8.0.13

2、本文主要参考了MySQL官方文档 InnoDB锁定和事务机制

3、本文还参考了何登成的 MySQL加锁处理分析、一个最不可思议的MySQL死锁分析 以及阿里云RDS-数据库内核组的 常用SQL语句的MDL加锁源码分析

4、MySQL是插件式的表存储引擎，数据库的锁是和存储引擎相关的，本文讨论的锁都是InnoDB存储引擎的锁

文章正文开始

“加什么样的锁”与以下因素相关

1、当前事务的隔离级别

2、SQL是一致性非锁定读(consistent nonlocking read)还是DML(INSERT/UPDATE/DELETE)或锁定读(locking read)

3、SQL执行时是否使用了索引，所使用索引的类型(主键索引，辅助索引、唯一索引)

我们先分别介绍这几个因素

一、隔离级别(isolation level)

数据库事务需要满足ACID原则，“I”即隔离性，它要求两个事务互不影响，不能看到对方尚未提交的数据。数据库有4种隔离级别(isolation level)，按着隔离性从弱到强(相应的，性能和并发性从强到弱)分别是

1、Read Uncommitted。下面简称RU

2、Read Committed。下面简称RC

3、Repeatable Read(MySQL的默认隔离级别)。下面简称RR

4、Serializable

“I”即隔离性正是通过锁机制来实现的。提到锁就会涉及到死锁，需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。

-- 查看事务的 全局和session 隔离级别( MySQL 5.7.19及之前使用tx_isolation)

select @@global.transaction_isolation, @@session.transaction_isolation;

-- 设置 全局 事务隔离级别为repeatable read

set global transaction isolation level repeatable read

-- 设置 当前session 事务隔离级别为read uncommitted

set session transaction isolation level read uncommitted

事务隔离级别设置和查看的详细语法请见：

https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

二、一致性非锁定读和锁定读

InnoDB有两种不同的SELECT，即普通SELECT 和 锁定读SELECT。锁定读SELECT 又有两种，即SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE；锁定读SELECT 之外的则是 普通SELECT 。

不同的SELECT是否都需要加锁呢？

1、普通SELECT 时使用一致性非锁定读，不加锁；

2、锁定读SELECT 使用锁定读，加锁；

3、此外，DML(INSERT/UPDATE/DELETE)时，需要先查询表中的记录，此时也使用锁定读，加锁；

FOR SHARE 语法是 MySQL 8.0 时加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等价的，但，FOR SHARE 用于替代 LOCK IN SHARE MODE，不过，为了向后兼容，LOCK IN SHARE MODE依然可用。

1、一致性非锁定读(consistent nonlocking read)

InnoDB采用多版本并发控制(MVCC, multiversion concurrency control)来增加读操作的并发性。MVCC是指，InnoDB使用基于时间点的快照来获取查询结果，读取时在访问的表上不设置任何锁，因此，在事务T1读取的同一时刻，事务T2可以自由的修改事务T1所读取的数据。这种读操作被称为一致性非锁定读。这里的读操作就是普通SELECT。

隔离级别为RU和Serializable时不需要MVCC，因此，只有RC和RR时，才存在MVCC，才存在一致性非锁定读。

一致性非锁定读在两种隔离级别RC和RR时，是否有什么不同呢？是的，两种隔离级别下，拍得快照的时间点不同

1、RC时，同一个事务内的每一个一致性读总是设置和读取它自己的最新快照。也就是说，每次读取时，都再重新拍得一个最新的快照(所以，RC时总是可以读取到最新提交的数据)。

2、RR时，同一个事务内的所有的一致性读 总是读取同一个快照，此快照是执行该事务的第一个一致性读时所拍得的。

2、锁定读(locking read)

如果你先查询数据，然后，在同一个事务内 插入/更新 相关数据，普通的SELECT语句是不能给你足够的保护的。其他事务可以 更新/删除 你刚刚查出的数据行。InnoDB提供两种锁定读，即：SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE。它俩都能提供额外的安全性。

这两种锁定读在搜索时所遇到的(注意：不是最终结果集中的)每一条索引记录(index record)上设置排它锁或共享锁。此外，如果当前隔离级别是RR，它还会在每个索引记录前面的间隙上设置排它的或共享的gap lock(排它的和共享的gap lock没有任何区别，二者等价)。

看完背景介绍，我们再来看一下InnoDB提供的各种锁。

三、InnoDB提供的8种不同类型的锁

InnoDB一共有8种锁类型，其中，意向锁(Intention Locks)和自增锁(AUTO-INC Locks)是表级锁，剩余全部都是行级锁。此外，共享锁或排它锁(Shared and Exclusive Locks)尽管也作为8种锁类型之一，它却并不是具体的锁，它是锁的模式，用来“修饰”其他各种类型的锁。

MySQL5.7及之前，可以通过information_schema.innodb_locks查看事务的锁情况，但，只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

MySQL8.0删除了information_schema.innodb_locks，添加了performance_schema.data_locks，可以通过performance_schema.data_locks查看事务的锁情况，和MySQL5.7及之前不同，performance_schema.data_locks不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁，也就是说即使事务并未被阻塞，依然可以看到事务所持有的锁(不过，正如文中最后一段所说，performance_schema.data_locks并不总是能看到全部的锁)。表名的变化其实还反映了8.0的performance_schema.data_locks更为通用了，即使你使用InnoDB之外的存储引擎，你依然可以从performance_schema.data_locks看到事务的锁情况。

performance_schema.data_locks的列LOCK_MODE表明了锁的类型，下面在介绍各种锁时，我们同时指出锁的LOCK_MODE。

1、共享锁或排它锁(Shared and Exclusive Locks)

它并不是一种锁的类型，而是其他各种锁的模式，每种锁都有shard或exclusive两种模式。

当我们说到共享锁(S锁)或排它锁(X锁)时，一般是指行上的共享锁或者行上的排它锁。需要注意的是，表锁也存在共享锁和排它锁，即表上的S锁和表上的X锁，表上的锁除了这两种之外，还包括下面将会提到的意向共享锁(Shard Intention Locks)即IS锁、意向排它锁(Exclusive Intention Locks)即IX锁。表上的锁，除了这四种之外，还有其他类型的锁，这些锁都是在访问表的元信息时会用到的(create table/alter table/drop table等)，本文不讨论这些锁，详细可见：常用SQL语句的MDL加锁源码分析。

数据行r上共享锁(S锁)和排它锁(X锁)的兼容性如下：

假设T1持有数据行r上的S锁，则当T2请求r上的锁时：

1、T2请求r上的S锁，则，T2立即获得S锁。T1和T2同时都持有r上的S锁。

2、T2请求r上的X锁，则，T2无法获得X锁。T2必须要等待直到T1释放r上的S锁。

假设T1持有r上的X锁，则当T2请求r上的锁时：

T2请求r上的任何类型的锁时，T2都无法获得锁，此时，T2必须要等待直到T1释放r上的X锁

2、意向锁(Intention Locks)

表锁。含义是已经持有了表锁，稍候将获取该表上某个/些行的行锁。有shard或exclusive两种模式。

LOCK_MODE分别是：IS或IX。

意向锁用来锁定层级数据结构，获取子层级的锁之前，必须先获取到父层级的锁。可以这么看InnoB的层级结构：InnoDB所有数据是schema的集合，schema是表的集合，表是行的集合。意向锁就是获取子层级(数据行)的锁之前，需要首先获取到父层级(表)的锁。

意向锁的目的是告知其他事务，某事务已经锁定了或即将锁定某个/些数据行。事务在获取行锁之前，首先要获取到意向锁，即：

1、事务在获取行上的S锁之前，事务必须首先获取 表上的 IS锁或表上的更强的锁。

2、事务在获取行上的X锁之前，事务必须首先获取 表上的 IX锁。

事务请求锁时，如果所请求的锁 与 已存在的锁兼容，则该事务 可以成功获得 所请求的锁；如果所请求的锁 与 已存在的锁冲突，则该事务 无法获得 所请求的锁。

表级锁(table-level lock)的兼容性矩阵如下：

对于上面的兼容性矩阵，一定注意两点：

1、在上面的兼容性矩阵中，S是表的(不是行的)共享锁，X是表的(不是行的)排它锁。

2、意向锁IS和IX 和任何行锁 都兼容(即：和行的X锁或行的S锁都兼容)。

所以，意向锁只会阻塞 全表请求(例如：LOCK TABLES ... WRITE)，