mysql如果索引为uid间隙锁_理解MySQL——锁，事务，隔离级别，并发控制

缺乏、安全感 2022-10-27 05:13 178阅读 0赞

本文参考来源：微信公众号——vivo互联网技术，文章：深入理解MySQL——锁，事务，隔离级别，并发控制

1.MySQL服务器逻辑架构

图片来源：MySQL官网

![7f1d33df7a9b6e564d2383e9b59104c6.png][]

每个连接都会在MySQL服务端产生一个线程(内部通过线程池管理线程)。比如：一个select语句进入，MySQL首先会在查询缓存中查找是否缓存了这个select结果集，如果没有则继续执行解析→优化→执行得过程；否则会直接从缓存中获取结果集。

2.MySQL锁

2.1共享锁与排他锁(Shared and Exclusive Locks)

共享锁和排他锁都是标准的行级锁。

1)共享锁S：标准的读锁，读锁允许多个连接可以同一时刻并发的读取同一资源，互不干扰。

2)排他锁X：又称写锁，一个写锁会阻塞其他的写锁或读锁，保证同一个时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

锁类型

英文名

又称

同一时刻

共同点

共享锁S

Shared Locks

读锁

同一时刻，允许多个连接并发的读取同一资源，互不干扰。

两者都是锁机制本身的策略，通过这两种策略对锁进行了区分

排他锁X

Exclusive Locks

写锁

同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

同上

2.2意向锁(Intention Locks)

背景：多粒度锁的并存场景。

InnoDB支持多粒度锁(锁粒度：可分为行锁和表锁)，允许行锁和表锁共存。为了实现多粒度级别的锁定，InnoDB使用了意图锁。

意向锁：表级别的锁。先提前声明的一个意向，并获取表级别的意向锁(共享意向锁IS或排他意向锁IX)，如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁(S或X)了。

注意：除了LOCK TABLES...WRITE,会锁住表中的所有行，其他场景意向锁实际锁不住任何行。

意向协议锁：在事务能够获取表中的行上的共享锁之前，他必须首先获取表上的IS锁或更强的锁。在事务能够获取表中的行上的独占锁之前，它必须首先获取表上的IX锁。

意向锁实现的背景是多粒度锁的并存场景，兼容性如下：

Conflict

Compatible

Conflict

Compatible

Conflict

Compatible

Conflict

Compatible

Conflict：互斥

Compatible：兼容

意向锁仅表示意向，是一种较弱的锁，意向锁之间兼容并行(IS,IX之间兼容并行)。X与IS,IX互斥，S与IX互斥。意向锁是比X/S更弱的锁，存在一种预判的意义。先获取更弱的IS，IX锁，如果获取失败就不必再获取更强S,X锁。

2.3索引行锁(Record Locks)

eg：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果c1上的索引被使用到。防止其他事务变动c1=10的行。

record lock总会在索引行上加锁。即使一个表并没有设置任何索引，这时候innoDB也会创建一个隐式的聚集索引(primary Key)，然后在这个聚集索引上加锁。

当查询字段没有索引时，eg：update t setcolumn=“A” where column B=“B”，如果columnB字段不存在索引(或者不是组合索引前缀)，这条语句会锁住所有记录也就是锁表。如果语句的执行能够执行一个columnB字段的索引，那么仅会锁住满足where的行。

2.4间隙锁(Gap locks)

间隙锁：锁定索引记录之间的间隙，或者锁定一个索引记录之前的间隙，或者锁定一个索引记录之后的间隙。

例如：SELECT uid FROM t1 WHERE uid BETWEEN 1and 4 FOR UPDATE,阻止其他事务将值插入到列uid中，因为该范围内所有现有值之间的间隙都是锁定的。

对于使用唯一索引来搜索唯一行的语句select a from，不产生间隙锁定。(不包含组合唯一索引，即：gapLock不作用于单列唯一索引)

例如：SELECT \*FROM t WHERE id=100，如果id没有索引或者具有非唯一索引，则语句将锁定前面的间隙。如果id列有位移的索引，上面的语句只对id值为100的行使用索引记录锁，其他会话是否在前一个间隙中插入行并不重要。

间隙可以跨越单个索引值和多个索引值，甚至空的。

间隙锁是性能和并发锁之间权衡的以重折衷，用于某些特定的事务隔离级别，如RC级别。

在重叠的间隙中(或者重叠的行记录)允许gap共存。

同一个gap中，允许一个事务持有gap X-Lock(gap写锁/排他锁)，同时另一个事务在这个gap中持有(gap写锁/排他锁)

2.5next-key lock

next-key lock是record lock与gap lock的组合。

例如：存在一个查询匹配b=6的行(b上有一个非唯一索引)，NextLock就是：在b=6的行加上了RecordLock锁，并且使用了GapLock锁定了b=6之前(“之前”：索引排序)的所有行记录。

MySQL查询时执行行级锁策略，会对扫描过程中的行进行加锁(X或S)，也就是加上Record Lock，同时会对这个记录之前的所有行加上Gap Lock。

假设一个索引包含值10，11，13和20.该索引肯恩的Next Key Lock锁定以下区间：

![5985ad7db2b9ff7ee00b189927eac2bc.png][]

注意：innoDB默认隔离级别(RR)下，next key lock自动开启。(原因：gap作用于RR，如果是RC，gap Lock不会生效，那么next key lock自然也不会生效)。

2.6插入意向锁(Insert Intention Locks)

insert intention lock是一种发生在insert插入语句时的gap lock间隙锁，锁定插入行之前的所有行。

插入意向锁以这样一种方式表明插入的意图，如果插入到同一索引间隙中的多个事务没有插入到该间隙中的相同位置，则他们不需要等待对方。

例如：存在值为5和8的索引记录，尝试分别插入6和7的独立事务，在获得所插入行上的独占锁之前，每个事务使用insert intention lock锁定4和7之间的间隙，但不会阻塞彼此，因为这些行为不冲突。

![d517eed2c3a664df4239c18ff486012d.png][]

![6b8745444d817e74ce7bb8c2628739de.png][]

2.7AUTO-INC Locks

AUTO-INC 锁是一种特殊的表级锁，产生于这样的场景：事务插入(inserting into )到具有 AUTO\_INCREMENT 列的表中。

在最简单的情况下，如果一个事务正在向表中插入值，那么其他任何事务必须等待向该表中插入它们自己的值，以便由第一个事务插入的行接收连续的主键值。

3.事务

事务就是一组原子性的 sql，或者说一个独立的工作单元。 事务就是说，要么 MySQL 引擎会全部执行这一组sql语句，要么全部都不执行(比如其中一条语句失败的话)。

3.1自动提交(AutoCommit，MySQL 默认)

![f5a8c7ccbd614fced7d189d541a214fe.png][]

MySQL 默认采用 AutoCommit 模式，也就是每个 sql 都是一个事务，并不需要显式的执行事务。如果 autoCommit 关闭，那么每个 sql 都默认开启一个事务，只有显式的执行“commit”后这个事务才会被提交。

3.2显示事务 (START TRANSACTION...COMMIT)

示例：小Y 要给 大L 转账100块钱，需要执行一下三个操作：

1)检查 小Y 的账户余额是否大于100块；

2)小Y 的账户减少100块；

3)大L 的账户增加100块。

这三个操作就是一个事务，必须打包执行，要么全部成功， 要么全部不执行，其中任何一个操作的失败都会导致所有三个操作“不执行”——回滚。

3.3事务的ACID

A(atomiciy) 原子性：一个事务必须保证其中的操作要么全部执行，要么全部回滚，不可能存在只执行了一部分这种情况出现。

C(consistency) 一致性：数据必须保证从一种一致性的状态转换为另一种一致性状态。 比如上一个事务中执行了第二步时系统崩溃了，数据也不会出现 小Y 的账户少了100块，但是 大L 的账户没变的情况。要么维持原装(全部回滚)，要么小Y少了100块同时 大L 多了100块，只有这两种一致性状态的。

I(isolation)隔离性：在一个事务未完成执行时，通常会保证其他Session无法看到这个事务的执行结果。

D(durability)持久性：数据一旦commit，就会保存下来。及时数据提交完以后系统崩溃，数据也不会丢失。

4.隔离级别

隔离级别

脏读可能性

不可重复度可能性

幻读可能性

加锁读

READ  UNCOMMITED

Yes

READ  COMMITED

Yes

REPEATEBLE  READ

Yes

SERIALIZABLE

Yes

![482285603a1c70e5edd19ff24845c0bf.png][]

4.1 READ UNCOMMITED(未提交读，可脏读)

事务中的修改，即使没有提交，对其他会话也是可见的。可以读未提交的数据——脏读。脏读会导致很多问题，一般不用这个隔离级别。

![7e788fcfdce8c81e26615fe37ca54bf7.png][]

4.2 READ COMMITED(提交读或不可重复读，幻读)

一般数据库默认使用这个隔离级别(MySQL不是)，这个隔离级别保证了一个事务中的操作如果没有完全执行完(commit完成)，事务中的操作，对其他的会话是不可见的。

![fcc633f2fdfb1f7e59613825bea1d743.png][]

也就验证了 read committed 级别在事物未完成 commit 操作之前修改的数据对其他 Session 不可见，执行了 commit 之后才会对其他 Session 可见。 我们可以看到 Session B 两次查询得到了不同的数据。

READ COMMITED级别解决了脏读问题，但是对其他session两次读取产生了不一致的结果(因为另一个session执行了事务，一致性变化)

4.3 REPEATABLE READ(可重复读)

一个事务中多次执行统一读SQL，多次读取结果一致。这个隔离级别解决了脏读，幻读问题。这里指innoDB的RR级别。innoDB使用“next-key”锁解决当前读进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行。

具体可参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

4.4 SERIALZABLE(可串行化)

最强的隔离级别，通过给事务中每次读取的行加锁，写加写锁，保证不产生幻读为题，但是会导致大量超时以及锁争用问题。

5.并发控制与MVCC

5.1 MVCC(multiple-version-concurency-control，多版本并发控制)

行级锁的变种，在普通读情况下避免了加锁操作，因此开销更低。虽然实现不同，但是通常都是实现非阻塞读，对于写操作只锁定必要的行。

一致性读(读取快照)：select ✳ from table ……

当前读(读取实际的持久化数据)：特殊的读操作，增加/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。如下：select ✳ from table where ？ lock in share mode；

select ✳ from table where ？ for update ；

insert；update；delete；

注意：select …… from table where……(没有额外加锁后缀)使用MVCC，保证了快照读(MySQL成为consistent read)，所谓一致性读或者读取快照就是读取当前事务开始之前的数据快照，在这个事务开始之后的更新不会被读到。

对于加锁读，select with for update(排他锁)或者lock in SHARE MODE(共享锁)，update，delete语句，要考虑是否是唯一索引的等值查询。

INNO DB的MVCC通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列，用于事务回滚，此处省略)，一个保存行的创建版本号，另一个保存行的更新版本号，这个版本号是把每个事务的版本号，是递增的。这样保证了innoDB的对读操作不需要加锁也能保证正确读取数据。

5.2 MVCC select无锁操作与维护版本号

在MySql默认的RR级别下，MVCC操作如下：

5.2.1 select (快照读，所谓快照读就是读取当前事务之前的数据)：

1)innoDB只select查找版本号早于当前版本号的数据行，这样保证了读取的数据要么是在这个事务开始之前就已经commit(早于当前版本号)，要么在这个事务自身中执行创建操作的数据(等于当前版本号)。

2)查找行的更新版本号要么未定义，要么大于当前版本号(为了保证事务可以读到老数据)，这样保证事务读取到当前事务开始之后未被更新的数据。

注意：这里的select不能有for update，lock in share语句。总之要只返回满足以下条件的行数据，达到快照读的效果：

(行创建版本号≤当前版本号&&(行更新版本号==null  or 行更新版本号>当前版本号))

5.2.2 insert

innoDB为这个事务中插入新的行，保存当前事务版本号的行座位行的行创建版本号。

5.2.3 delete

innoDB为每一个删除的行保存当前事务版本号，作为行的删除标记。

5.2.4 update

将存在的两条数据，保持当前版本号作为更新后的数据的新增版本号，同时保存当前版本号作老数据的行更新版本号。

当前版本号-写→新数据行创建版本号 && 当前版本号-写→老数据更新版本号()；

5.3 脏读vs幻读vs不可重复读

5.4 如何保证rr级别绝对不产生幻读

在使用select.....where......语句中加入for update(排他锁)或者lock in share mode(共享锁)语句来实现。其实就是锁住了可能造成幻读的数据，阻止数据的写入操作。

其实是因为数据的写入操作(insert，update)需要写获取写锁，由于可能产生幻读的部分，已经获取到了某种锁所以要在另外一个会话中获取写锁的前提是当前会话中释放所有因加锁语句产生的锁。

5.5 显示锁，隐式锁

隐式锁：上文所述的锁都属于不要需要额外语句加锁的隐式锁。

显示锁：

1)SELECT; ...; LOCK; IN; SHARE; MODE;(共享锁)

2)SELEC ... FOR UPDATE;(排他锁)

5.6 查看锁情况

通过如下sql可以查看等待锁的情况：

select ✳ from information\_schema.innodb\_trx where trx\_state="lock wait";

或

show engine innodb status;

[7f1d33df7a9b6e564d2383e9b59104c6.png]: /images/20221024/d52f68fda57a4ca3a6ef34e5b3b2d7c0.png
[5985ad7db2b9ff7ee00b189927eac2bc.png]: /images/20221024/4e35e23e9b424f57a304776c904a120a.png
[d517eed2c3a664df4239c18ff486012d.png]: /images/20221024/b327374b8f314ae9a4222f09464a5b70.png
[6b8745444d817e74ce7bb8c2628739de.png]: /images/20221024/66d75938398d4a449af11b7027937f7e.png
[f5a8c7ccbd614fced7d189d541a214fe.png]: /images/20221024/9ee86dfa719444fe928a4234cf58b403.png
[482285603a1c70e5edd19ff24845c0bf.png]: /images/20221024/cada1e129a9243a891758206eba7984c.png
[7e788fcfdce8c81e26615fe37ca54bf7.png]: /images/20221024/438ed1970f4445408b76c95eaf63048c.png
[fcc633f2fdfb1f7e59613825bea1d743.png]: /images/20221024/6ac69869adb04837abe7e355cc945be5.png