「MySQL高级篇」MySQL锁机制 && 事务 -- 临键锁与幻读

大家好，我是melo，一名大三后台练习生，最近赶在春招前整理整理发过的博客~🤣🤣🤣！

🍳引言

锁锁锁，到哪到离不开这桩琐事，并发琐事，redis琐事，如今是MySQL琐事，这其中琐事，还跟MySQL另一个重要的东西--事务息息相关。

这篇将从以下几点，带你解开这把爱情的苦锁：

🎏本篇速览脑图

锁机制&&事务.png

🎯常规表锁&行锁

这一部分较为常规，若有前置知识，可以直接跳到下边的【表级锁扩展】部分开始阅读
建议借助侧边栏，有emoji表情的属于重点

锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁分类

从对数据操作的粒度分：

1）表锁：操作时，会锁定整个表。

2）行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2）写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁(了解)
MyISAM	支持	不支持	不支持
InnoDB	支持	支持(默认)	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这3种锁的特性可大致归纳如下：

锁类型	特点
表级锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

粒度小，自然发生锁冲突的概率就低

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；

读锁案例

准备环境

create database demo_03 default charset=utf8mb4;

use demo_03;

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');



CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

读操作

客户端一对book表加了锁，并拿到了book表的锁，在该锁未释放之前，不能去查别的表；
而客户端二能查到book和其他表，是因为读锁是共享锁，他并没有真正拿到这把锁，自然可以肆意妄为，不受未释放锁的束缚；

写操作

客户端①直接报错，因为读锁会排斥写操作
客户端②陷入了阻塞状态，得等待客户端①释放锁

unlock后，客户端②的写操作就能正常执行了。

总结

读锁对于加锁的客户端：会限制对其他表的查询以及对任何表的写操作
读锁对于其他客户端：不会限制任何查询，但会阻塞对该表的写操作

🎈助记

自己拿到了读锁，那自己当然不能再去读其他表，而又因为读锁不会影响到其他客户端读的结果，那其他客户端自然可以任意读。

而对于写操作：自己还在读，就别想着去做写操作了！而对于其他客户端，如果对该表写操作。肯定会影响到当前客户端的读取结果，所以其他客户端不能对该表进行写操作

简而言之：自己不能三心二意【操作其他表】，而对他人则考虑自己所做的操作会不会导致两个客户端拿到不一致的数据，会的话就是不允许的。

写锁案例

客户端一 :

1）获得tb_book 表的写锁

lock table tb_book write ;

2）执行查询操作

select * from tb_book ;

3）执行更新操作

update tb_book set name = 'java编程思想（第二版）' where id = 1;

更新操作执行成功；

客户端二 :

4）执行查询操作

select * from tb_book ;

陷入阻塞状态，因为写锁是排他锁，排斥其他客户端的写和读操作。

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 select 语句就会立即执行

🎈总结

写的优先级很高，对于锁定的表可写可读，但同样不能三心二意！！！而其他客户端对于锁定的表啥也干不了

结论

锁模式的相互兼容性如表中所示：

由上表可见：

1）对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞其他用户对同一表的写请求；

2）对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

查看锁的争用情况

show open tables；

In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

show status like 'Table_locks%';

Table_locks_immediate ：指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

Table_locks_waited ：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

InnoDB 行锁

行锁介绍

行锁特点：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。（两者是息息相关的）

🎈🎋事务

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

ACID属性	含义
原子性（Atomicity）	事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（Consistent）	在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。
持久性（Durable）	事务完成之后，对于数据的修改是永久的。

并发事务处理带来的问题

问题	含义
丢失更新（Lost Update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后提交的事务修改的值覆盖。
脏读（Dirty Reads）	读到了另一个事务还未提交的数据
不可重复读（Non-Repeatable Reads）	一个事务执行同样的两次select语句，前后查询出来的结果不一致
幻读（Phantom Reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。

幻读

幻读：就像出现了“幻影”一般，原本查不到这个人，然后要插入的时候，突然又说这个人存在

场景：注册问题吧，查询某个主键id是否存在，第一次查询不存在，即将插入新数据时【刚好另一个人插入了该主键id】，导致这边注册失败

幻读在“当前读”下才会出现。
幻读仅专指“新插入的行”【update的不算】

🎈事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

隔离级别	丢失更新	脏读	不可重复读	幻读
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read（默认）	×	×	×	√
Serializable(串行化)	×	×	×	×

备注： √ 代表可能出现， × 代表不会出现。

读未提交：别人修改了某行数据，还未提交我们就能看到。
读已提交：别人修改了某行数据，得等到提交后我们才能看到。 -- 解决脏读
可重复读：别人修改了某行数据，我们也不去读那一行数据，还是读我们当前事务最初的那个未被修改的值。 -- 解决不可重复读
串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

例子

读未提交：v1=v2=v3=2；B还未提交，A就可以看到了。
读已提交：v1=1，v2=v3=2；等到B提交后，A才能看到。
可重复读：v1=v2=1，v3=2；也就是说，所谓的可重复读，是说在当前事务提交之前，只会读取当前事务最初的值，而不去读取其他的事务；
串行化：v1=1，v2=1，v3=2；事务A中查询得到值1的时候，就会加了“读锁”，会阻塞其他事务对该行的写操作（上文我们已经有提及到相关的读锁和写锁，忘记了的小伙伴可以翻阅上文看看）所以在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。

Mysql 的数据库的默认隔离级别为 Repeatable read，查看方式：

show variables like 'tx_isolation';

InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加**排他锁**（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE    （悲观锁）
						即手动锁定一行

悲观锁和乐观锁

悲观锁：事务必须排队执行。数据锁住了，不允许并发。（行级锁：select后面添加for update）

乐观锁：支持并发，事务也不需要排队，只不过需要一个版本号。

案例准备工作

create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

行锁基本演示

我们采用两个客户端，首先要关闭掉自动提交功能：set autocommit = 0；
- 普通的select不加锁，没有什么影响
- 而insert和update就不一样了，会加排它锁，其他客户端陷入阻塞状态，不能对该行（注意得两个客户端操作的是同一行，才会阻塞，因为是行锁）进行修改，直到加锁的客户端提交完事务（相当于释放锁）

无索引行锁升级为表锁

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

由于执行更新时， name字段本来为varchar类型，我们是作为数组类型使用，存在类型转换，索引失效，最终行锁变为表锁；(字符串类型，在SQL语句使用的时候没有加单引号，导致索引失效，查询没有走索引，进行全表扫描，索引失效，行锁就升级为表锁)

InnoDB 行锁争用情况

show  status like 'innodb_row_lock%';

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花平均时长
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远高于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议

尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围。
尽可能减少索引条件，及索引范围，避免间隙锁。
尽量控制事务大小，减少锁定资源量和时间长度。
尽可使用低级别事务隔离（但是需要业务层面满足需求）

🎯表级锁扩展

全局锁

特点

备份的一致性问题

来看下边这个场景，比如我们创建的购买操作，涉及到了用户余额表+订单表，流程顺序如下：

当前正在备份用户余额表，备份了小明同学的余额是100
此时小明刚好下了订单，理应扣减50元
1. 但由于用户余额表已经备份完毕，余额表不会受到影响
小明下好单了，如今来备份订单表了，能够备份到小明刚下的单

到这里是否发现问题了，就是备份后的结果是：小明的余额没扣钱，但却有相关的订单数据，出现了数据不一致的情况

那我们该如何规避这种现象呢？

1. 加全局锁

通俗易懂，就是锁住整个表，此时所有对数据的增删改操作都会被阻塞

2. 不加锁的一致性数据备份

上边提到，备份时加上参数 --single-transaction就能实现此效果，具体是怎么做到的呢？

如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

上文也提到了可重复读，顾名思义就是，开启事务后，无论其他事务是否更新了A数据，我们查到的依旧是开始事务时的原始A数据，而不会是更改后的，因此能保证在备份期间，即使有别的事务来更新，我们也不会备份到【进而就规避了数据不一致的情况】

元数据锁

当存在事务，在对表的增删查改语句时，其他事务若要改变表结构，会被阻塞。。

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

两者是互斥的，谁先来谁办事，直到一方前者处理完毕

MDL 不需要显示调用，那它是在什么时候释放的?

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

隐含问题

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞

那么在线程 C 阻塞后，后续所有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

如何解决

解决长事务。

为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

对于热点数据的表【kill掉后立马又有长事务】

此时单单kill是没用了，我们只能给这个alter语句设置等待时间，若超时未拿到MDL写锁，就放弃，不阻塞后续的select语句

🎈意向锁

为什么要引入意向锁

比如有两个事务A跟B，和一个表G

A对G中的某一行加了行锁，之后B要对G加表锁的时候，行锁跟表锁就会产生冲突

为了解决冲突，B就需要遍历全表，判断是否有行锁，这样效率太低了，因此引入了意向锁

如何解决

当A对G中的某一行加了行锁后，会顺便给表G加上意向锁

B要对G加表锁的时候，只需要判断表G的意向锁，跟自己要加的表锁是否兼容即可，无需再遍历全表

意向锁类型

意向锁跟表锁的兼容性

共享锁的话，跟表锁共享锁兼容，但跟表锁排它锁是互斥的
排它锁，自然都互斥

注意，意向锁之间是兼容的，并且意向锁不会与行级的共享锁和排它锁互斥

🎈AUTO-INC 锁

数据库的数据自增机制，就是基于这个锁机制实现的，使得我们可以在insert的时候，不用指明数据的值。

AUTO-INC 锁是特殊的表锁机制，锁不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放。【因此不遵循两阶段锁协议（下文会提及到该协议）】

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

当然，这样也有弊端

在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁；
当 innodb_autoinc_lock_mode = 2，就采用轻量级锁；
当 innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁。
自增值一旦分配了就会加一，即使回滚了，自增值也不会减一，而是继续使用下一个值，所以自增值有可能不是连续的。

总结

常规的锁住整个表，直到插入语句执行完毕后才释放
为被 AUTO_INCREMENT修饰的字段加上的轻量级锁无需等到插入语句执行完毕后才释放

🎯行级锁扩展

🎈两阶段锁协议

一个事务中，可能有多条语句，每条语句可能会加上锁，那么这些锁是什么时候才会释放呢？

答案是：需要在事务commit之后才释放，所以说，如果我们的事务中需要锁多个行，要把尽可能粒度大的操作放到后边！

行级锁分类

行锁（Record Lock）：单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。【解决幻读现象】
临键锁（Next-key Lock）：Record Lock+Gap Lock【行锁+间隙锁】，锁定一个范围，包含记录本身。行锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

🎈间隙锁&&临键锁

定义

间隙锁：锁定一个范围，但不包含数据本身
临键锁：锁定一个范围，并且包含数据本身

对记录加锁时，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是左开右闭区间，而间隙锁是左开右开区间。

假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间:

(-∞, 10]

(10, 11]

(11, 13]

(13, 20]

(20, ∞ ]

对于最后一个间隙，∞不是一个真正的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隙。

加锁原则

两个“原则”、两个“优化”和一个“bug”。

原则1：加锁的基本单位是next-key lock。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

🎈退化问题

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

案例准备

以下例子均在 MySQL 8.0.23版本下测试

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);

id【主键】	c【非唯一索引】	d
0	0	0
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25

唯一索引等值查询

若查询的记录【7】存在，则退化为记录锁，锁的只是id为7这一个索引
若查询的记录不存在：
1. 原则1：先统一加上next-key lock，(5,10]；
2. 再根据优化2，这是一个等值查询(id=7)，遍历到最后发现id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)。

🎈🎈理解

不要忘了我们引入间隙锁的初衷，是为了解决幻读现象，那这里我们是唯一索引：

如果查询出来的id=7已经存在了，则不可能还会有其他事务能够插入id为7的幻影进来，因为是唯一索引嘛，因此自然不需要再锁间隙了，只需要锁这一行就够了，退化为行锁
如果查询出来的id=7不存在，相当于索引树里边还没有7这个节点，我们要锁住他，就只能通过他的相邻节点5跟10，把这段区间锁住
1. 同时5跟10用不用锁呢？我们这里是唯一索引，而且是7，不等于5也不等于10，所以5跟10不会影响到我们的7，不需要锁，故只是锁（5,10）

非唯一索引等值查询

这里session A要给索引c上c=5的这一行加上读锁。

原则1，先加next-key lock，左开右闭，(0,5]
这里c是普通索引，不是唯一索引，所以不能确保只有当前c=5这一条记录，还需要锁住后边的【因为后边可能还会插入c=5】，因此还需要向后遍历，直到c=10这条记录，访问到的都要加锁【原则2】，(5,10]
优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)。

因此sessionC的操作会被阻塞，这是可以理解的。那sessionB呢？为什么不会被阻塞呢？

根据原则2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，因此sessionC不会被阻塞。

锁的是索引

在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样了。执行 for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

同时，如果你要用lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。
比如，将session A的查询语句改成 select d from t where c=5 lock in share mode。

这样就不得不回表，就会涉及到主键索引了【其实就是让覆盖索引失效】

唯一索引范围锁

等值查询，先给10加上间隙锁，(5,10]
优化1：退化成行锁，只锁10这一行
由于是范围查询，继续往后遍历，直到15这一行停下来，访问到的都要加next-key lock，(10,15]
由于15不满足查询条件，故会退化为间隙锁，(10,15)

因此最后的范围是[10,15)，sessionB的第二条insert会被阻塞，其他都不会

非唯一索引范围查询

跟唯一索引范围锁的区别在于，普通索引中的next-key lock不会退化为间隙锁和记录锁

next-key lock，(5,10]，由于c不是唯一索引，所以不会退化为行锁
继续往后遍历，直到15，next-key lock，(10,15]

因此最后的范围是：(5,15]，两条语句都会被阻塞

🍔非索引查询

如果使用的是没有索引的字段，比如update user set age=7 where name=‘xxx（即使没有匹配到任何数据）’,那么会给全表加入gap锁。同时，它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁，因为没有索引，则这些字段也就没有排序，也就没有区间。除非该事务提交，否则其它事务无法插入任何数据。

🎈死锁

间隙锁死锁

间隙锁潜在问题

注意，间隙锁与间隙锁之间是不会冲突的

session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

两个 session 进入互相等待状态，形成死锁。此时我们来看如何应对死锁...

解决死锁方案

直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

如果选用第一种策略，其实不好估量，我们不确定这个超时时间要设置为多少合适，因此一般使用第二种策略。

但是它也是有额外负担的。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待【死锁产生的条件之一】，也就是死锁。

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。

在操作系统里边，应对死锁的最好方法是：预防死锁的产生hhh，这个预防，可能很难跟我们开发工程师牵扯上，更多涉及到DBA那边了。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁

总结

还是那张脑图，再看一遍，尝试复述出来，就过关啦

锁机制&&事务.png

💠下篇预告

这篇我们主要讲的是锁相关的知识，事务只是入了门，关于事务背后的原理，以及MVCC多版本并发控制，这些我们留到后边再来详解。

🖨参考文献

小林coding
MySQL45讲
黑马MySQL视频

收藏=白嫖，点赞+关注才是真爱！！！本篇文章如有不对之处，还请在评论区指出，欢迎添加我的微信一起交流：Melo__Jun

🧿友链

posted @ 2022-10-31 09:02 Melo~ 阅读(1113) 评论(0) 编辑收藏举报

刷新页面返回顶部

MeloJun

「MySQL高级篇」MySQL锁机制 && 事务 -- 临键锁与幻读

🍳引言

🎏本篇速览脑图

🎯常规表锁&行锁

锁概述

锁分类

Mysql 锁

MyISAM 表锁

如何加表锁

读锁案例

读操作

写操作

总结

🎈助记

写锁案例

🎈总结

结论

查看锁的争用情况

InnoDB 行锁

行锁介绍

🎈🎋事务

事务及其ACID属性

并发事务处理带来的问题

幻读

🎈事务隔离级别

例子

InnoDB 的行锁模式

悲观锁和乐观锁

案例准备工作

行锁基本演示

无索引行锁升级为表锁

InnoDB 行锁争用情况

总结

优化建议

🎯表级锁扩展

全局锁

特点

备份的一致性问题

1. 加全局锁

2. 不加锁的一致性数据备份

元数据锁

隐含问题

如何解决

🎈意向锁

为什么要引入意向锁

如何解决

意向锁类型

意向锁跟表锁的兼容性

🎈AUTO-INC 锁

总结

🎯行级锁扩展

🎈两阶段锁协议

行级锁分类

🎈间隙锁&&临键锁

定义

加锁原则

🎈退化问题

案例准备

唯一索引等值查询

🎈🎈理解

非唯一索引等值查询

锁的是索引

唯一索引范围锁

非唯一索引范围查询

🍔非索引查询

🎈死锁

间隙锁死锁

间隙锁潜在问题

解决死锁方案

常见的解决死锁的方法

总结

💠下篇预告

🖨参考文献

🧿友链

公告