首页  思维导图  详情

mysql锁机制 -高频面试题必知必会系列

2022-02-16 14:55:16   12  举报





AI智能生成

架构师必知必会系列-mysql锁机制

云原生

mysql数据库

架构师必知必会

作者其他创作

大纲/内容

InnoDB存储引擎

锁

有哪些锁？

行级锁

共享锁（S Lock）

意向锁

意向共享锁（Intention S Lock）

事务想要获取一张表中某几行的共享锁

排他锁（X Lock）

意向锁

意向排他锁（Intention X Lock）

事务想要获取一张表中某几行的排他锁

一致性非锁定读 consistent nonlocking read

定义

指的是innoDB存储引擎通过多版本控制（MVCC） 的方式来读取当前执行时间数据库中的行的数据。

如果读取的行正在执行DELETE或UPDTE操作， 这时读取操作不会因此去等待行上锁的释放。 相反的，InnoDB存储引擎回去读取行的一个快照数据。

之所以称为非锁定读，因为不需要等待访问的行上X锁的释放。 快照数据是指该行的之前版本的数据，该实现是通过undo段来完成的。 而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。 此外，读取快照数据是不需要上锁的，因为没有事务需啊哟对历史的数据进行修改操作，

如何实现

InnoDB存储引擎中默认采用一致性非锁定读。 但是不同隔离级别读取方式不同。 并不是每个事务隔离级别都是采用非锁定的一致性读。 即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。

快照数据概述

1、快照数据是当前行数据之前的历史版本，每行记录可能有多个版本。

2、这种技术为行多版本技术。 由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control , MVCC）

各种隔离级别的实现方案

READ COMMITTED

锁方案

非锁定的一致性读

快照定义

总是读取被锁定行的最新一份快照数据。

案例分析

session-1

子主题

子主题

子主题

注意，这个时候读已提交和可重复读的差别就出来了

读已提交

子主题

读已提交，总是读取行的最新版本。

可重复读

子主题

可重复读，总是读取事务开始时的数据行。

REPEATABLE READ InnoDB默认的隔离级别

锁方案

非锁定的一致性读

快照定义

总是读取事务开始时的行数据版本

案例分析

按照时间线展示一下过程 特别说明下： 对于 READ COMMITTED这种事务隔离级别，从数据库理论看， 其违反了ACID的I的特性，即隔离性。

事务时间线

子主题

一致性锁定读 consistent  locking read

背景

InnoDB的默认隔离级别是可重复读，可重复读的实现方案是MVCC的非锁定的一致性读。 但是，特殊情况下，用户显示的对数据库读取操作进行加锁以保证数据逻辑的一致性。 而这要求数据库支持加锁语句，即使是 SELECT 操作。

InnoDB对于SELECT 语句支持李鞥中一致性的锁定读操作

SELECT ... FOR  UPDATE

SELECT ... FOR  UPDATE会对读取的行加 X 锁（排他锁）。 意味着其他事务不能对已锁定的行加上任何锁。

session-1

-- session 1 0. 查看当前会话事务级别是否修改成功 select @@global.tx_isolation,@@tx_isolation; 步骤1. 设置当前会话的事务级别为 REPEATABLE READ   SET SESSION TRANSACTION ISOLATION LEVEL  REPEATABLE READ ; -- 我们可以通过下面的命令来设置隔离级别。 -- SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE] 2. -- 查看当前会话事务级别是否修改成功（全局会话未变：READ-COMMITTED，当前会话已经更新为： READ UNCOMMITTED） select @@global.tx_isolation,@@tx_isolation; select @@global.transaction_isolation,@@transaction_isolation; 步骤2. 开启事务 START TRANSACTION ; 步骤3. 先读一次，确认数据为100 SELECT b from testinnodblock where a  = 1 for UPDATE; 步骤6 . 第二次读，session-2的事务修改但未提交后，session-1再读一次，确认数据，还是100，结果不变 SELECT b from testinnodblock where a  = 1; 步骤8. 第三次读，确认第二个事务进行了提交，读取到还是100，结果仍然不变 SELECT b from testinnodblock where a  = 1; COMMIT;

session-2

-- session 2 0. 查看当前会话事务级别是否修改成功 select @@global.tx_isolation,@@tx_isolation; 步骤4. 开启事务 START TRANSACTION ; 先读一次，确认数据中台 SELECT b from testinnodblock where a  = 1; 步骤5.更新数据 update testinnodblock set b = 102 where a = 1 ; 步骤7. 提交事务 COMMIT;   

SELECT ... LOCK IN SHARE MODE

SELECT ... LOCK IN SHARE MODE 会对读取的行加 S 锁（共享锁）。 意味着其他事务可以对读取的行记录加上一个 S锁，但是如果加 X 锁，则会被阻塞。

session-1

-- session 1 0. 查看当前会话事务级别是否修改成功 select @@global.tx_isolation,@@tx_isolation; 步骤1. 设置当前会话的事务级别为 REPEATABLE READ   SET SESSION TRANSACTION ISOLATION LEVEL  REPEATABLE READ ; -- 我们可以通过下面的命令来设置隔离级别。 -- SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE] 2. -- 查看当前会话事务级别是否修改成功（全局会话未变：READ-COMMITTED，当前会话已经更新为： READ UNCOMMITTED） select @@global.tx_isolation,@@tx_isolation; select @@global.transaction_isolation,@@transaction_isolation; 步骤2. 开启事务 START TRANSACTION ; 步骤3. 先读一次，确认数据为100 SELECT b from testinnodblock where a  = 1 LOCK IN SHARE MODE; 步骤6 . 第二次读，session-2的事务修改但未提交后，session-1再读一次，确认数据，还是100，结果不变 SELECT b from testinnodblock where a  = 1; 步骤8. 第三次读，确认第二个事务进行了提交，读取到还是100，结果仍然不变 SELECT b from testinnodblock where a  = 1; COMMIT;

session-2

-- session 2 0. 查看当前会话事务级别是否修改成功 select @@global.tx_isolation,@@tx_isolation; 步骤4. 开启事务 START TRANSACTION ; 先读一次，确认数据中台 SELECT b from testinnodblock where a  = 1; 步骤5.更新数据 update testinnodblock set b = 101 where a = 1 ; 步骤7. 提交事务 COMMIT;

搞清锁的概念

在数据库中，lock与latch都可以称之为“锁”，但两者截然不同

比对图

锁定的对象

latch

定义

线程的临界资源 在InnoDB存储引擎中，latch又可以分为mutex（互斥量）和 rwlock（读写锁）。

lock

定义

lock的对象时事务，用来锁定的是数据库中的对象，如表、页、行。

锁定的时间

latch

定义

一般称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短。 若持续的时间长，则应用的性能会非常差。

lock

定义

整个事务过程。 并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

死锁检测机制

latch

定义

其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

lock

定义

lock，正如在大多数数据库一样，是有死锁机制的。

锁的算法

InnoDB行锁的三种算法

Record Lock 单个行记录上的锁

Record Lock总是会去锁住索引记录

索引从哪里来？

InnoDB存储引擎表的索引，如果没有任何一个索引，则会使用隐式的主键来进行锁定

Gap Lock 间隙锁，锁定一个范围，但不包含记录本身 https://www.cnblogs.com/diegodu/p/9239200.html

什么是Gap Lock?

A place in an InnoDB index data structure where new values could be inserted. 说白了gap就是索引树中插入新记录的空隙。 相应的gap lock就是加在gap上的锁，还有一个next-key锁，是记录+记录前面的gap的组合的锁。

Gap Lock的作用？

http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row  locking with gap locking. InnoDB performs row-level locking in such a way that when it searches  or scans a table index, it sets shared or exclusive locks on the index records it encounters.  Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on  an index record also affects the “gap” before that index record. That is, a next-key lock is  an index-record lock plus a gap lock on the gap preceding the index record. If one session has  a shared or exclusive lock on record R in an index, another session cannot insert a new index  record in the gap immediately before R in the index order. 为了防止出现幻影，InnoDB使用了一种称为next key locking的算法，该算法将索引行锁定与间隙锁定相结合。 InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，它会对遇到的索引记录设置共享或独占锁定。 【重点1：】因此，行级锁实际上是索引记录锁。 此外，索引记录上的下一个键锁也会影响该索引记录之前的“间隙”。 也就是说，下一个键锁是索引记录锁加上索引记录前面的间隙上的间隙锁。 如果一个会话在索引中的记录R上具有共享或独占锁，则另一个会话无法插入新索引 按索引顺序在R之前的间隙中记录。

简单讲就是防止幻读。 通过锁阻止特定条件的新记录的插入，因为插入时也要获取gap锁(Insert Intention Locks)。

什么情况下取得Gap Lock？

http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html 这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

REPEATABLE READ

... For consistent reads, there is an important difference from the READ COMMITTED isolation level:  All consistent reads within the same transaction read the snapshot established by the first read. ... For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements,  locking depends 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,  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. ... 对于一致读取，与读取提交隔离级别有一个重要区别： 同一事务中的所有一致读取都会读取第一次读取所建立的快照。。。 对于锁定读取（选择with进行更新或在共享模式下锁定）、UPDATE和DELETE语句， 锁定取决于语句是否使用具有唯一搜索条件的唯一索引，或范围类型搜索条件。 【重点1：】对于具有唯一搜索条件的唯一索引， InnoDB只锁定找到的索引记录，而不锁定之前的间隔。 【重点2：】对于其他搜索条件，InnoDB使用间隙锁或下一个键锁锁定扫描的索引范围，以阻止插入 通过其他课程进入范围所涵盖的空白。

locking reads，UPDATE和DELETE时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

REPEATABLE READ 可重复读时gap lock现象

测试目的

在REPEATABLE READ下，更新一条记录不提交，然后看看能阻塞另外的会话哪些操作。 ... For consistent reads, there is an important difference from the READ COMMITTED isolation level:  All consistent reads within the same transaction read the snapshot established by the first read. ... For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements,  locking depends 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,  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.

SESSION 1: SESSION 1中更新id=20的记录，但是不提交。

插入数据测试

SESSION 2中，执行插入操作，发现[10,30)范围不能插入数据。

更新数据测试

对于更新操作，仅20这条记录不能更新，因为更新操作不会去获取gap锁。

特别注意！！！

如果SESSION 1的表扫描没有用到索引，那么gap或next-key锁住的范围是整个表，即任何值都不能插入。

READ COMMITTED 可重复读时gap lock现象

只会锁住已有记录，不会加gap锁。

SERIALIZABLE 可重复读时gap lock现象

和REPEATABLE READ的主要区别在于把普通的SELECT变成SELECT … LOCK IN SHARE MODE，即对普通的select都会获取gap锁或next-key锁。

子主题

REPEATABLE READ 可重复读时gap lock现象

测试表

建表sql: CREATE TABLE `tb2` (   `id` int(11) DEFAULT NULL,   `c1` int(11) DEFAULT NULL,   KEY `tb2_idx1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

准备测试数据： select * from tb2; mysql> select * from tb2; +------+------+ | id   | c1   | +------+------+ |   10 |    0 | |   20 |    0 | |   30 |    0 | +------+------+ 3 rows in set (0.01 sec)

Next-Key Lock = Gap Lock + Record Lock  锁定一个范围，并且锁定记录本身

事务

什么是事务？

定义

事务是逻辑上的一组操作，要么都执行，要么都不执行。

举个例子

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

分支主题

事务的特性ACID

原子性

事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用

一致性

执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

隔离性

并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性

一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题

带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

问题一

脏读（Dirty read）

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

问题二

丢失修改（Lost to modify）

指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

问题三

不可重复读（Unrepeatableread）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

问题四

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改。幻读的重点在于新增或者删除。

不可重复读案例

（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）：事务1中的A先生读取自己的工资为1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

幻读案例

（同样的条件, 第1次和第2次读出来的记录数不一样）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

事务的隔离级别

SQL 标准定义了四个隔离级别

READ-UNCOMMITTED(读取未提交)

最低的隔离级别，允许读取尚未提交的数据变更

存在的问题

可能会导致脏读、幻读或不可重复读

READ-COMMITTED(读取已提交)

允许读取并发事务已经提交的数据

可以解决的问题

可以阻止脏读

存在的问题

，但是幻读或不可重复读仍有可能发生

REPEATABLE-READ(可重复读)

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改

可以解决的问题

可以阻止脏读和不可重复读

存在的问题

，但幻读仍有可能发生

SERIALIZABLE(可串行化)

最高的隔离级别，完全服从ACID的隔离级别

可以解决的问题

所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，**该级别可以防止脏读、不可重复读以及幻读**。

MySQL

MySQL InnoDB 存储引擎的默认支持的隔离级别是 **REPEATABLE-READ（可重读）**。

我们可以通过`SELECT @@tx_isolation;`命令来查看，MySQL 8.0 该命令改为`SELECT @@transaction_isolation;` mysql&gt; SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+

存在的问题

MySQL InnoDB 的 REPEATABLE-READ（可重读）并不保证避免幻读

如何解决？

需要应用使用加锁读来保证。而这个加锁度使用到的机制就是 Next-Key Locks。

扩展阅读

MVCC我知道，但是为什么要设计间隙锁？ https://www.jianshu.com/p/fbec6d1fa16c

扩展

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 **READ-COMMITTED(读取提交内容)** ，但是你要知道的是 InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）** 并不会有任何性能损失。

分支主题

InnoDB 存储引擎在 **分布式事务** 的情况下一般会用到 **SERIALIZABLE(可串行化)** 隔离级别。

拓展一下(以下内容摘自《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章)：

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

实际情况演示

可重复读

防止幻读（可重复读）

背景说明

设置隔离级别

在下面我会使用 2 个命令行mysql ，模拟多线程（多事务）对同一份数据的脏读问题。

分支主题

MySQL 命令行的默认配置中事务都是自动提交的，即执行SQL语句后就会马上执行 COMMIT 操作。如果要显式地开启一个事务需要使用命令：`START TARNSACTION`。

分支主题

我们可以通过下面的命令来设置隔离级别。 SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

版本差异

5.7.20版本前

transaction_isolation作为tx_isolation的别名被引入，都可用来表示隔离级别

5.7.20版本后（含）

transaction_isolation作为tx_isolation的别名被引入

8.0版本后（含）

tx_isolation被废弃，只能使用transaction_isolation

事务

我们再来看一下我们在下面实际操作中使用到的一些并发控制语句:

分支主题

- - `START TARNSACTION` |`BEGIN`：显式地开启一个事务。 - - COMMIT`：提交事务，使得对数据库做的所有修改成为永久性。 - - `ROLLBACK`：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

数据库隔离级别

生产环境

数据库版本

show variables like '%version%';

子主题

事务隔离级别

全局事务级别

查看

select @@global.tx_isolation,@@tx_isolation; select @@global.transaction_isolation,@@transaction_isolation;

修改

我们可以通过下面的命令来设置隔离级别。 SET  GLOBAL  TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

当前会话隔离级别

查看

select @@global.tx_isolation,@@tx_isolation; select @@global.transaction_isolation,@@transaction_isolation;

修改

我们可以通过下面的命令来设置隔离级别。 SET   SESSION  TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

show variables like '%isolation%';

子主题

测试环境

事务隔离级别

select @@global.tx_isolation,@@tx_isolation;

读未提交（脏读） 如何做到的？

session-1

子主题

session-2

子主题

避免脏读（读已提交） 如何做到的？

session-1

子主题

session-2

子主题

子主题

不可重复读 如何做到的？

session-1

子主题

还是刚才上面的读已提交的图，虽然避免了读未提交， 但是却出现了，session-1事务还没有结束，就发生了 不可重复读问题。

子主题

session-2

子主题

子主题

可重复读 如何做到的？

session-1

子主题

还是刚才上面的读已提交的图，虽然避免了读未提交， 但是却出现了，session-1事务还没有结束，就发生了 不可重复读问题。

子主题

session-2

子主题

子主题

防止幻读（串行） 如何做到的？

session-1

子主题

还是刚才上面的读已提交的图，虽然避免了读未提交， 但是却出现了，session-1事务还没有结束，就发生了 不可重复读问题。

子主题

-- session 1 0. 查看当前会话事务级别是否修改成功 select @@global.tx_isolation,@@tx_isolation; 步骤1. 设置当前会话的事务级别为 REPEATABLE READ   SET SESSION TRANSACTION ISOLATION LEVEL  SERIALIZABLE ; -- 我们可以通过下面的命令来设置隔离级别。 -- SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE] 2. -- 查看当前会话事务级别是否修改成功（全局会话未变：READ-COMMITTED，当前会话已经更新为： READ UNCOMMITTED） select @@global.tx_isolation,@@tx_isolation; select @@global.transaction_isolation,@@transaction_isolation; 步骤2. 开启事务 START TRANSACTION ; 步骤3. 先读一次，确认数据为100 SELECT b from testinnodblock where a  = 1; 步骤6 . 第二次读，session-2的事务修改但未提交后，session-1再读一次，确认数据，还是100，结果不变 SELECT b from testinnodblock where a  = 1; 步骤8. 第三次读，确认第二个事务进行了提交，读取到还是100，结果仍然不变 SELECT b from testinnodblock where a  = 1; COMMIT;

session-2

子主题

子主题

如何做到的？

间隙锁（Gap Lock）

事务的原子性

定义

如何实现的？

常见的问题

事务的持久性

定义

如何实现的？