我可以和面试官多聊几句吗？只是想...
MySQL优化篇系列文章（基于MySQL8.0测试验证），上部分：优化SQL语句、数据库对象，MyISAM表锁和InnoDB锁问题。

面试官：咦，小伙子，又来啦。

我：面试官，您好。一面确实收获不少，二面想获取更多的经验。

面试官：不错，不错，不错，年纪轻轻，有我当年一半的风范，挺有觉悟。接着聊MySQL锁问题。

我：好呀，这次我准备了MyISAM和InnoDB锁一些总结，希望您多多指教。

面试官：那，让我们进入今天的话题，一起讨论MySQL锁问题。

我：好的，请接着往下看。

接着上一篇MySQL数据库SQL优化流程。在对MySQL进行举例并使用到示例数据库：大多数情况使用MySQL官方提供的sakila（模拟电影出租信息管理系统）和world数据库，类似于Oracle的scott用户。

第一篇MySQL优化篇SQL优化流程已经发布，原本想合一起发，但示例代码太多。导致篇幅很长，所以分篇发出来。（单篇太长，确实影响阅读体验，后续整理后会分篇发。）

分篇发送第二篇MySQL优化篇，MyISAM表锁和InnoDB锁问题。

你可以将这篇博文，当成过度到MySQL8.0的参考资料。友情提示：经验是用来参考，不是拿来即用。如果你能看到并分享这篇文章，我很荣幸。如果有误导你的地方，我表示抱歉。

如果没有进行特别说明，一般是基于MySQL8.0.28进行测试验证。官方文档非常具有参考意义。目前市面上针对MySQL8.0书籍还比较少，部分停留在5.6.x和5.7.x版本，但仍然具有借鉴意义。

文中会给出官方文档可以找到的参考内容，基本上在小标题末尾有提及并说明。辅助你快速定位出处，心里更有底气。如果想应对MySQL面试，我想这篇总结还是有一定的参考意义。需要有耐心看完，个人总结时参考书籍和MySQL8.0官方文档也很乏味，纯英文文档更令人头大。不懂的地方可以使用有道，结合实际测试进行理解。

个人理解有限，难免出现错误偏差。所有测试，仅供参考。

如果感觉对你起到作用，有参考意义，想获取原markdown文件。

可以访问我的个人github仓库，定期上传md文件，空余时间会制作目录链接：

https://github.com/cnwangk/SQL-study/tree/master/md/SQL/MySQL

• MySQL优化篇（二）
• 正文
  • 一、锁问题
    • 01 MySQL锁介绍
      • 1.1 什么是锁
      • 1.2 MySQL锁
    • 02 MyISAM 表锁
      • 2.1 查询表锁互掐
      • 2.2 MySQL表锁模式
      • 2.3 如何加表锁
      • 2.4 并发插入
      • 2.5 MyISAM锁调度
    • 03 InnoDB 锁问题
      • 3.1 行级锁的神话
      • 3.2 lock与latch
      • 3.3 锁类型
      • 3.4 一致性非锁（锁）定读
      • 3.5 自增长与锁
      • 3.6 外键与锁
      • 3.7 锁的算法
      • 3.8 阻塞、死锁、锁升级
• 莫问收获，但问耕耘

MySQL优化篇（二）

给出sakila-db数据库包含三个文件，便于大家获取与使用：

1. sakila-schema.sql：数据库表结构；
2. sakila-data.sql：数据库示例模拟数据；
3. sakila.mwb：数据库物理模型，在MySQL workbench中可以打开查看。

https://downloads.mysql.com/docs/sakila-db.zip

world-db数据库，包含三张表：city、country、countrylanguage。

只是用于用于简单测试学习，建议使用world-db：

https://downloads.mysql.com/docs/world-db.zip

正文

友情提示：在某些情况，你自己测试的结果可能与我演示有所不同，我省略了查询结果的部分参数。

本文侧重点在SQL优化流程以及MySQL锁问题（MyISAM和InnoDB存储引擎）。图片可能会挂，演示时尽量使用SQL查询语句返回结果进行示例。篇幅很长，因此使用markdown语法加了目录。

起初，也只是想看MySQL8.0.28有哪些变化，后面索性结合书籍和官方文档总结了一篇。花了将近两周，基本是每天完善一点，因为个人只有晚上和周末有时间总结并测试验证。或许后面会拆开发，如果拆开发会编写再详细一点。如果有错别字，也请多多担待。如果你能看到并分享这篇文章，我很荣幸。如果有误导你的地方，我表示抱歉。

如果你是从MySQL5.6或者5.7版本过渡到MySQL8.0。学习之前，建议线看官方文档这一章节：1.3 What Is New MySQL8.0 。在做对比的时候，文档中带有Note标识是你应该注意的地方。比如下面这张截图：

参考文档：refman-8.0-en.pdf

参考书籍：

• 《深入浅出MySQL 第2版 数据库开发、优化与管理维护》，个人参考优化篇部分。
• 《MySQL技术内幕InnoDB存储引擎 第2版》，个人参考索引与锁章节描述。

一、锁问题

简单概括锁：锁是计算机协调多个进程或线程并发访问某一资源的机制。

MySQL中的锁看上去用法和表面实现（对比其它DBMS），貌似很简单，但真正深入理解其实也不是那么容易。

01 MySQL锁介绍

1.1 什么是锁

为何要使用锁？开发多用户、数据库驱动的应用时，难点（痛点）：一方面要最大程度地利用数据库的并发访问，另一方面还要确保每个用户能以一致的方式读取和修改数据。因此有了锁（locking）的机制，同时也是数据库系统区别于文件系统的一个关键特性。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的消耗外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的重要因素。从描述来看，锁对数据库显得尤为重要，也更加复杂。接下来，会对锁机制特点进行介绍、常见的锁问题，以及解决MySQL锁问题的方法。

1.2 MySQL锁

相比其它数据库来说，MySQL的锁机制相对好理解一点，其最显著的特点是不同的存储引擎支持不同锁机制。比如MyISAM和MEMORY存储引擎采用表级锁（table-level locking）；BDB存储引擎（MySQL8.0文档没看到介绍）采用页面锁（page-level locking），但也支持表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，默认采用行级锁。

MySQL中3种锁特性：

• 表级锁：开销小，加锁块。不会出现死锁，锁粒度大，发生锁冲突概率最高，并发度最低。
• 行级锁：开销大，加锁慢。会出现死锁，锁粒度最小，发生锁冲突概率最低，并发度最高。
• 页面锁：开销和加锁时间介于表锁与行锁之间。会出现死锁，锁粒度介于表锁与行锁之间，并发度一般。

从上述各种锁特点来看，不能一概而论哪种锁更好，但可以从具体应用特点来判断哪种锁更合适。

单从锁角度出发：表锁较为适合以查询为主，少量按索引条件更新数据的应用。行级锁更适合有大量按索引条件、并发更新少量不同数据，同时有并发查询的应用。

02 MyISAM 表锁

MyISAM（存储引擎限制256TB）存储引擎只支持表锁，是MySQL开始几个版本中唯一支持的锁类型。

随着应用对事务完整性和并发性要求的不断提高，MySQL开发了基于事务存储引擎，后来出现了支持页面锁的BDB（逐渐被InnoDB替代，MySQL8.0文档已看不到介绍）存储引擎和支持行锁的InnoDB存储引擎。MyISAM表锁依旧是使用比较广泛的锁类型。

2.1 查询表锁互掐

通过检查Table_locks_waited和Table_locks_immediate状态变量来分析系统上表锁争抢：

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 164   |
| Table_locks_waited         | 0     |
+----------------------------+-------+
...
5 rows in set (0.00 sec)

如果Table_locks_waited值比较大，则说明存在严重的表锁争抢情况。

2.2 MySQL表锁模式

MySQL表锁有两种模式：

1. Table Read Lock：表共享读锁；
2. Table Write Lock：表独占写锁。

MyISAM表读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。MyISAM表的读操作和写操作之间，以及与写操作之间是串行的。

当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其它线程的读、写操作都会等待，直到被释放。

2.3 如何加表锁

MyISAM在执行查询（select）前，会自动给涉及的所有表加读锁；在执行更新（update、insert、delete等）操作前，会自动给涉及的表加写锁，这一过程并不需要用户干预。所以，一般情况不需要用户执行lock table命令给MyISAM显式加锁。

下面只是演示一下加锁和解锁，阻塞示例。如果想测试，可以在创表时指定存储引擎为MyISAM，或者使用alter table命令修改存储引擎。例如修改city表存储引擎为MyISAM，使用show create table命令查看：

mysql> alter table world.city engine=MyISAM;
mysql> show create table city;

city表测试添加写锁（lock table write）

mysql> lock table city write;
Query OK, 0 rows affected (0.00 sec)

当前session窗口，测试查询（select）、插入（insert）、修改（update）、删除（delete）均不受影响。

打开其它session窗口，测试增删改查均在等待中。分别给出查询上锁与解锁示例。

上了写锁（当前session），其它窗口查询显示等待中...

解锁（当前session），其它session窗口查询正常并显示查询等待时间：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city limit 0,1;
1 row in set (1 min 33.43 sec)

city表测试添加读锁（lock table read）

mysql> lock table city read;
Query OK, 0 rows affected (0.00 sec)

演示更新操作插入（insert）、修改（update）、删除（delete）：均提示有读锁（当前session）

mysql> insert into city values(9527,'ts','ts','ts',9527000);
ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated

mysql> update city set name='Kabuls' where id=1;
ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated

mysql> delete from city where id=1;
ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated

解锁（unlock）：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

tips：添加读锁，当前session的新增、修改删除均会提示已经上了锁，查询其它未上锁表也会提示报错。

其它session窗口依旧可以查询、更新未上锁的表。锁住表不会提示，但是会在等待中。

使用lock table时，可能需要一次性锁定用到的所有表，同一个表出现多次，需要对别名锁定。

2.4 并发插入

整体上看，MyISAM表读和写是串行。在一定条件下，MyISAM表也支持查询和插入操作并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，用于控制其并发插入行为，值可以为0、1或2，默认为AUTO。

如下所示，使用select @@参数形式查询系统变量值：

mysql> select @@concurrent_insert;
+---------------------+
| @@concurrent_insert |
+---------------------+
| AUTO                |
+---------------------+

1. 当concurrent_insert设置为0：不允许并发插入。
2. 当concurrent_insert设置为AUTO（or 1）：如果MyISAM表中间没有被删除的行，允许在一个进程读表的同时，另一个进程从表尾插入记录。也是MySQL默认设置AUTO（or 1）。
3. 当concurrent_insert设置为2：无论MyISAM有无空洞（表中间没有被删除的行），都允许在表尾并发插入记录。

示例：其中一个session获得一张表read local锁，该线程可以对当前表进行查询，但不能进行更新操作。其它线程（session），虽然不能进行删除和更新操作，但可以进行插入（insert）操作。假定条件：表中间没有空洞。

其中一个线程（session1）获取锁：查询不受影响，不能做更新操作

mysql> lock table xxls read local;						-- session1获取read local锁
Query OK, 0 rows affected (0.00 sec)
mysql> select * from xxls limit 0,1;					 -- 查询一条数据（可行）
mysql> insert into xxls values(1015,'xxls','女','B');	-- 演示更新操作是拒绝的
ERROR 1099 (HY000): Table 'xxls' was locked with a READ lock and can't be updated

另外一个线程（session2）演示：插入数据完成，不受影响

mysql> insert into xxls values(1015,'xxls','女','B');
Query OK, 1 row affected (0.01 sec)

总结：可以利用MyISAM存储引擎并发插入特性解决应用中对同一表查询和插入锁争用。设置concurrent_insert值为2，总是允许并发插入。与此同时，通过定期在系统空闲时段（不活跃时段）执行optimize table整理空间碎片，回收删除记录产生的空洞。

optimize用法如下：注意如果有读锁情况下，是不能进行操作的。

mysql> optimize table tolove;	-- 优化tolove表
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| test.tolove | optimize | status   | OK       |
+-------------+----------+----------+----------+
1 row in set (1.24 sec)

更多详细描述（MySQL8.0）可以参考：

5.1.8 Server System Variables（服务系统变量）

2.5 MyISAM锁调度

MyISAM存储引擎读锁与写锁互斥，读写操作串行。

一个进程请求某MyISAM表读锁，另一个进程请求同一表写锁，MySQL能友好的处理么？

答案是写进程先获得锁。即使读请求先等等待（排队时，读在前），写在后等的不耐烦了。读你给我挪挪位，写优先插队进入队列中。个人认为写锁优先是合理的，毕竟写（更新操作：insert、update、delete）比较重要，如何在最大限度保证数据完整性、一致性。

写锁优先，MySQL认为写请求一般比读请求重要。这也是为什么MyISAM表不适合同时有大量更新操作和查询操作的原因。大量更新操作会造成查询操作很难获得读锁，导致永远阻塞。好在可以通过一些系统参数来调节MyISAM调度行为。通过指定参数low_priority_updates，让MyISAM存储引擎默认给予读请求优先权利。

给出官方文档设置示范：

mysql> select @@low_priority_updates;	-- 查询出默认值是0

--low-priority-updates[={OFF|ON}] 		-- 命令行格式，也可以在配置文件中进行设置

set low_priority_updates=1				-- 在字符界面临时设置值，设置为1，降低连接发出更新请求

另外MySQL提供了折中方法调节锁冲突，给系统参数max_write_lock_count设置合适值。当一张表读锁达到这个值后，MySQL暂时将写请求优先级降低，给读进程（session）获得锁的机会。

也不能太依赖一条SQL查询语句解决问题，适当进行拆分成中间表进行合理控制查询时间。有些复杂查询（统计）操作是无法避免的，但可以人为定时操作，在夜深人静之时（凌晨），悄无声息执行。如果你是一名Java开发人员，或许在配置文件（xml、yml）中应该做过这种操作。

03 InnoDB 锁问题

ACID：在了解InnoDB锁问题之前，可以先看一下InnoDB存储引擎一些特性：简称ACID。

1. 原子性A（atomicity）：事务是一个原子操作单元，对数据的修改要么全执行，要么全不执行。举个例子：（银行存钱，典型事务），正常情况：小芳去银行存钱，银行要么将钱存到系统并显示正常增长后的余额，要没全部回退出来。不正常情况：小芳存了一百大洋，银行将钱吞了，账户余额没变；或者小芳账户余额增加了，钱退回来了。
2. 一致性C（consistency）：在事务开始和完成时，数据必须保持一致状态。
3. 隔离性I（isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响独立环境执行。
4. 持久性D（durability）：事务完成之后，它对数据的修改是永久性的，即使出现系统故障也能保持。

并发事务处理带来的问题：

1. 丢失更新（lost update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其它事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其它事务所做的更新。（可以想象多人在线编辑同一份文档，有多个版本控制，最后还原到锁问题上）
2. 脏读（dirty read）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并作进一步处理，会产生未提交的数据依赖关系。这种现象被称为脏读。
3. 不可重复度（non-repeatable read）：一个事务在读取某些数据后的某个时间再次读取以前读过的数据，却发现其读过的数据已经发生了改变或某些记录已被删除。这种现象被称为不可重复读。
4. 幻读（phantom read）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其它事务插入了满足其查询条件的新数据，这种现象称为幻读。

脏读与不可重复读区别：脏读是读到未提交的数据，而不可重复度读到的是已经提交的数据。

更多MySQL8.0数据库的ACID模型介绍可以参考：

15.2 InnoDB and the ACID Model（InnoDB和ACID模型）

3.1 行级锁的神话

InnoDB存储引擎较MySQL数据库其它存储引擎在锁这一方面技高一筹，实现方式类似于Oracle数据库，提供一致性的非锁定读、行级锁支持。行锁没有相关额外开销，并可以同时得到并发性和一致性。

行级锁的一个神话，锁总会增加开销。其实是这样的，当实现本身会增加开销时，行级锁才会增加开销。InnoDB不需要锁升级，因为一个锁和多个锁的开销是想同的。

对于MyISAM存储引擎，其锁是表锁设计。并发情况读没有问题，但是并发插入性能略微差了一些。如果插入在底部，MyISAM存储引擎还是有一定的并发写入操作的。这里重复介绍了，在介绍MyISAM表锁时也有提到过。

3.2 lock与latch

MySQL数据库区分锁过程中，有一个容易令人混淆的概念lock与latch。在数据库中，lock与latch都被称为锁，但二者有截然不同的含义。

1. latch：一般称为闩（shuan）锁（轻量级的锁），因为其要求锁定的时间必须非常短。若持续时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
2. lock：lock的对象是事务，用来锁定的是数据库中的对象，比如表、页、行。一般lock的对象仅在事务commit或rollback后进行释放，不同事务隔离级别释放的时间可能不同。此外，lock正如在大多数数据库中一样，是有死锁机制的。

对于InnoDB存储引擎中的latch，可以通过命令查看：

语法：SHOW ENGINE engine_name {STATUS | MUTEX}

mysql> show engine innodb mutex;
+--------+----------------------------+-------------+
| Type   | Name                       | Status      |
+--------+----------------------------+-------------+
| InnoDB | rwlock: fil0fil.cc:3360    | waits=6     |
| InnoDB | rwlock: dict0dict.cc:2508  | waits=4     |
| InnoDB | sum rwlock: buf0buf.cc:787 | waits=40351 |
+--------+----------------------------+-------------+
3 rows in set (0.00 sec)

参考MySQL8.0文档可以看到mutex更多介绍：13.7.7.15 SHOW ENGINE Statement

tips：在debug版本中，可以查看到status参数的更多信息。

3.3 锁类型

锁类型列表（InnoDB Locking）：

虽然上面列出了7种锁，但下面只介绍标准行级锁和意向锁，其它锁类型介绍可以参考MySQL8.0官方文档。

InnoDB存储引擎实现了以下两种类型标准行级锁：

1. 共享锁（S Lock）：允许事务读一行数据。
2. 排它锁（X Lock）：允许事务删除或更新一行数据。

如果一个事务T1持有行r上的一个共享(S)锁，那么来自不同事务T2的请求对行r上的一个锁处理如下:

1. T2对共享锁（S）的请求可以立即被授予（获得行r共享锁）。因此，T1和T2都对行r保持S锁定。
2. T2对排它锁（X）锁请求不能立即授予。

其它事务想获得行r共享锁，其它请求等待T1、T2释放共享锁。

如果事务T1持有行r上的排它(X)锁，那么来自不同事务T2对行r上任何一种类型的锁请求都不能立即被授予。相反，事务T2必须等待事务T1释放其对行r的锁。

两种标准行级锁兼容性如下表格所示：

从上面表格中可以发现X锁与任何锁都不兼容，而S锁仅与S锁兼容。

友情提示：S和X锁是行锁，兼容指对同一行记录（row）锁兼容性情况。

除此之外，InnoDB存储引擎支持多粒度（granularity）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称为意向锁（Intention Locks）。意向锁将锁定的对象分为多个层次，意味着事务希望在更加细粒度（fine granularity）上加行锁。如下图3-3所示：

如果将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度对象进行上锁，首先需要对粗粒度对象上锁。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁为表级别锁。设计目的是为了在一个事务中揭示下一行将被请求的锁类型，支持如下两种意向锁。

意向锁（Intention Locks）：

1. 意向共享锁（IS）：事务想要获得一张表中某几行的共享锁。
2. 意向排它锁（IX）：事务想要获得一张表中某几行的排它锁。

由于InnoDB存储引擎支持的是行级别锁，因此意向锁不会阻塞除全表扫描以外的任何请求。

表级锁与行级锁类型兼容性汇总在如下面表格所示，并使用中文进行标注：

用户可以通过命令show engine innodb status查看当前锁请求信息：

show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-03-23 22:16:07 0x32d0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
...

------------
TRANSACTIONS
------------
Trx id counter 16145
Purge done for trx's n:o < 16144 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283762070116728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283762070115952, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 16144, ACTIVE 237 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1249, OS thread handle 12132, query id 15048 localhost ::1 root statistics
select * from world.city where id=1 for update
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 6 n bits 248 index PRIMARY of table `world`.`city` trx id 16144 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000016c9; asc ;;
 2: len 7; hex 81000001410110; asc     A  ;;
 3: len 30; hex 4b6162756c20202020202020202020202020202020202020202020202020; asc Kabul                         ; (total 35 bytes);
 4: len 3; hex 414647; asc AFG;;
 5: len 20; hex 4b61626f6c202020202020202020202020202020; asc Kabol ;;
 6: len 4; hex 801b2920; asc   ) ;;
------------------
...
============================
END OF INNODB MONITOR OUTPUT
============================
... 
1 row in set (0.00 sec)

此处，主要截取了事务相关参数，其它参数省略掉了。如何看到事务锁具体信息，可以手动去加锁测试，制造一个场景。

示例用法：

前提使用时InnoDB类型表做测试，可以使用show create table table_name查询当前表存储引擎。

mysql> show create table world.city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

MySQL8.0中使用select @@autocommit查看到默认值是1，代表开启了自动提交。测试使用时建议通过 set autocommit=0 命令先关闭自动提交，或者手动控制事务（begin、start transaction）。详细示例不列举了，可以参考前面SQL优化步骤进行测试。

mysql> begin
Query OK, 0 rows affected (0.00 sec)

SELECT ... LOCK IN SHARE MODE	-- 给语句加上共享锁
mysql> select * from world.city where id=1 lock in share mode; -- 示例给city表加上共享锁

SELECT ... FOR UPDATE			-- 给语句加上排它锁
mysql> select * from world.city where id=1 for update;	-- 示例获取排它锁，超时会抛异常
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上面打印出来参数有很多，线程（BACKGROUND THREAD）、信号量（SEMAPHORES）、事务（TRANSACTIONS）、文件I/O（FILE I/O）、插入缓存和适配HASH索引（INSERT BUFFER AND ADAPTIVE HASH INDEX）、缓冲日志检查点（LOG）、缓冲池和内存（BUFFER POOL AND MEMORY）以及行操作（ROW OPERATIONS）。

个人感觉有必要说明一下缓存（cache）与缓冲（buffer）区别：

• 缓冲（buffer）：加速数据写入硬盘；
• 缓存（cache）：加速数据从硬盘读取。

在MySQL中information_schema架构下可以通过三张表：INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS监控当前事务并分析可能存在的锁问题。

友情提示：在5.6.x和5.7.x和MariaDB 10.5.6还能看到INNODB_LOCKS、INNODB_LOCK_WAITS；在MySQL8.0中已经移除，可以说换成另一种形式呈现：在performance_schema架构下有data_lock_waits、data_locks可以查询参考。

INNODB_LOCKS与data_locks参数变化：有变化的参数加粗显示

INNODB_LOCK_WAITS与data_lock_waits参数变化：有变化的参数加粗显示

如果命令字符界面查看不方便，可以借助客户端工具MySQL workbench或者SQLyog等等进行查看。

更多参数详细介绍，可以参考MySQL8.0官方文档进行查看测试。

同样可以使用其它命令查看InnoDB存储引擎信息：

SHOW ENGINE INNODB MUTEX\G
SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G 	-- 打印所有PERFORMANCE_SCHEMA状态信息
*************************** 1. row ***************************
  Type: performance_schema
  Name: events_waits_current.size
Status: 168
*************************** 2. row ***************************
  Type: performance_schema
  Name: events_waits_current.count
Status: 1536
...
248 rows in set (0.00 sec)

以上是对锁类型进行简单介绍，理论知识偏多，基本结合MySQL8.0进行说明。

与我之前一篇《MySQL8.0.28安装教程全程参考官方文档》是一样的用意，希望体会自学的好处，以及阅读官方文档自我成长。英语差不是借口，有道谷歌翻译也能很好辅助你学习。

3.4 一致性非锁（锁）定读

3.4.1 一致性非锁定读

查询默认事务隔离级别 tx_isolation和tx_read_only系统参数已经在MySQL8.0.3中移除掉了，MySQL5.x和MariaDB10.5.6版本还可以继续使用tx_isolation这个系统参数。

友情提示：新版MySQL8.0.3之后使用transaction_isolation和transaction_read_only替代。

select @@tx_isolation;	-- MySQL5.x版本可以继续用

mysql> select @@transaction_isolation;	-- MySQL8.0.3版本开始使用新的系统参数
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

你可以参考文档：15.7.2.3 Consistent Nonlocking Reads

一致性非锁定读（Consistent Nonlocking Reads）是指InnoDB存储引擎通过多版本控制（multi-versioning ）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE操作，这时读取操作不会因此去等待行上的锁释放。相反，InnoDB存储引擎会读取一个快照数据。如图：3-4所示

图3-4直观地展现了InnoDB存储引擎非锁定一致性读。之所以称其为非锁定读：因为不需要等待访问行上X锁的释放。快照数据是指该行之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有开销的。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史数据进行修改操作。

可以看出，锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎默认设置下，这也是默认读取方式，即读取不会占用和等待表上的锁。但在不同事务隔离级别下，读取方式不同，并不是在每个事务隔离级别下都采用非锁定一致性读。即使是使用非锁定一致性读，对于快照数据定义也各不相同。

通过图3-4可以知道，快照数据其实是当前行数据之前的历史版本，每行记录可能有多个版本。如图3-4所示，一个行记录可能不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（multi-version concurrency control (MVCC)）。

在事务隔离级别 READ-COMMITTED和REPEATABLE-READ（InnoDB存储引擎默认事务隔离级别）下，InnoDB存储引擎使用非锁定一致性读。然而，对于快照数据定义却不相同。在READ-COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

如下示例，在两session A和session B会话中进行对比。在模拟并发过程中，希望带着思考去测试，不然会晕乎乎的

前提设置事务非自动提交，或者手动开启事务，前面演示也多次提到过。关键字大小写不影响使用，个人使用统一规则就好。

修改事务隔离级（当前会话生效），便于测试：

mysql> set transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

session A：

-- session A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from parent where id=1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

会话A中已通过显示地执行命令BEGIN开启了一个事务，并读取parent表中id=1的这条数据，但事务并没有结束。与此同时用户再开启另一个会话B，可以模拟出并发场景，然后对session B做如下操作。

session B：

-- session B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update parent set id=7 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在会话B中将parent表id为1字段值记录修改为id=7，但事务同样未提交，此时id=1的行加了一个X锁。如果在会话A中再次读取id=1的记录，根据InnoDB存储引擎特性，即在READ-COMMITTED和REPEATABLE-READ事务隔离级别下会使用非锁定一致性读。此时，再回到会话A中，继续未提交的事务，执行SQL语句：select * from parent where id=1;操作，不管使用READ-COMMITTED还是REPEATABLE-READ事务隔离级别，显示数据应该是：

mysql> select * from parent where id=1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

由于当前id=1的数据被修改了1次，因此只有一个行版本记录。接着会话2未提交的事务，提交事务：

-- session B
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

会话B提交事务后，会话1再次执行select * from parent where id=1;SQL语句，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下得到结果就不一样了。对于READ-COMMITTED事务隔离级别，它总是读取该行版本最新一个快照（fresh snapshot）。在上述示例中，因为会话B已经提交事务，所以READ-COMMITTED事务隔离级别下会得到如下结果：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from parent where id=1;
Empty set (0.00 sec)

对于REPEATABLE-READ（默认事务隔离级别），总是读取事务开始时的行数据。此时将session A和session B步骤对调来操作。起初我看文档时，也误解了，多研读几次才明白。得到示例结果如下：

mysql> select @@transaction_isolation;	
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from parent where id=1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

梳理一下session A和session B执行步骤，从时间角度演示：

tips：测试时使用BEGIN显示开启也行，使用SET AUTOCOMMIT=0同样也行。因为AUTOCOMMIT默认是1，所以手动禁止自动提交。

3.4.2 一致性锁定读

可以找到文档：15.7.2.4 Locking Reads

默认配置下，事务隔离级别为REPEATABLE-READ模式下，InnoDB存储引擎的select操作使用一致性非锁定读。但在某种场景下，用户需要显示地对数据库读取操作进行加锁以保证数据逻辑一致性。需要数据库支持加锁语句，即使是对select的只读操作。InnoDB存储引擎对select语句支持两种一致性锁定读（locking reads ）

• SELECT ... FOR UPDATE
• SELECT ... LOCK IN SHARE MODE

友情提示：在MySQL8.0.22可以使用SELECT ... FOR SHARE替代SELECT ... LOCK IN SHARE MODE，但是SELECT ... LOCK IN SHARE MODE是向后兼容，这两个描述是相同的。然而，使用FOR SHARE支持table_name, NOWAIT（不等待），和越过LOCKED选项。

SELECT ... FOR UPDATE对读取的行记录加一个X锁，其它事务不能对已锁定的行加任何锁。SELECT ... LOCK IN SHARE MODE对读取的行记录加上一个S锁，其它事务可以向被锁定的行加S锁，但如果是X锁，则会被阻塞。

对于一致性非锁定读，即使读取的行已被执行SELECT ... FOR UPDATE，也是可以进行读取的。此外，SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE必须在一个事务中，当事务提交了，锁也就释放了。因此在使用上述两句select锁定语句时，务必加上begin，使用start transaction要设置set autocommit=0。前面也提到过autocommit值为0代表禁用自动提交。

3.5 自增长与锁

自增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。对含有自增长的极计数器的表进行插入操作时，这个计数器会被初始化，执行如下语句得到计数器的值：

select MAX(auto_inc_col) from tbl_name for update;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这种实现方式称作AUTO-INC Locks。这种锁其实是一个特殊的表锁机制，为了提高插入性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locks从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。对于有自增长值的列并发插入性能较差，事务必须等待前一个插入的完成（不用等待事务的完成）。此外，对于insert ... select 的大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞。

从MySQL5.1.22版本开始，InnoDB存储引擎提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来 控制自增长模式，该参数默认值为2（MySQL8.0）。一共有三个参数值可以设置，分别为（0、1、2），MySQL5.7默认值为1，MariaDB10.5.6版本默认也是1。

MySQL8.0查询innodb_autoinc_lock_mode默认值：

mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.00 sec)

自增长类型：

1. INSERT-like：有INSERT, INSERT ... SELECT，REPLACE， REPLACE ... SELECT，LOAD DATA等。包含 simple-inserts，bulk-inserts以及mixed-mode inserts
2. Simple inserts：有 INSERT 和 REPLACE，不包含INSERT ... ON DUPLICATE KEY UPDATE。
3. Bulk inserts：有INSERT ... SELECT，REPLACE ...SELECT，and LOAD DATA 。
4. Mixed-mode inserts：出入中有一部分是自增长的，有一部分是确定的。比如：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')；也可以是 INSERT ... ON DUPLICATE KEY UPDATE。

自增长分类，一共有三个参数值可以设置，分别为（0、1、2）：

1. innodb_autoinc_lock_mode=2：默认值为2。对于所有INSERT-like自增长值的产生都是通过互斥量，而不是AUTO-INC Locks方式。使用row-base replication，保证最大并发性能以及数据一致性，MySQL8.0推荐设置。包含 simple-inserts，bulk-inserts以及
   mixed-mode inserts。
2. innodb_autoinc_lock_mode=1：默认值为1。对于simple-inserts，该值会用互斥量去对内存的计数器进行累加操作。对于bulk-inserts，是使用传统表锁的AUTO-INC Locks方式。
3. innodb_autoinc_lock_mode=0（traditional lock mode）：老版数据库传统锁模式。

友情提示：InnoDB存储引擎中自增长实现与MyISAM存储引擎不同，MyISAM是表锁设计，自增长不用考虑插入问题。在某种场景下，主节点（master）使用InnoDB存储引擎，在子节点（slave）使用MyISAM存储引擎的replication架构，用户需要考虑这种情况。

此外，在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列。如果不是第一个列，MySQL数据库则会抛异常，而MyISAM存储引擎没有这个问题。

进行示例演示：出现1075异常，正常情况是c1在前，c2在后即可执行成功。

CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
KEY (c2,c1)
) ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
KEY (c1,c2)
) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

你可以找到参考文档：

15. 7.1 InnoDB Locking

16. 6.1.6 InnoDB AUTO_INCREMENT Counter Initialization

3.6 外键与锁

3.6.1 外键用法

tips：目前MySQL支持外键的存储引擎有InnoDB和NDB。

外键的作用：用来保证参照完整性。比如有两张表主表parent table和子表child table，在子表中拥有主表外键约束；你想同时干掉两张表；MySQL告诉你，没门，不给删；需要先删除约束，才能彻底删除，使用第三方工具删除表时深有体会。MySQL数据库InnoDB存储引擎完整支持外键。

外键语法定义：

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

13.1.20.5 FOREIGN KEY Constraints

示例创建一张父表（parent）和一张子表（child）：

CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),	-- 给parent_id添加索引
FOREIGN KEY (parent_id)		-- parent_id设置为外键引用主表主键id
REFERENCES parent(id)		-- 引用主表（parent）主键id
) ENGINE=INNODB;

演示插入数据外键冲突：主表插入1条数据，在子表插入一条数据，违反外键约束，主表没有id=2的行。此时无法级联更新。

mysql> INSERT INTO parent (id) VALUES (1); -- 主表插入1条数据
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO child (id,parent_id) VALUES(2,2); -- 在子表插入一条数据，违反外键约束，主表没有id=2的行
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

演示删除数据外键冲突：有外键约束和索引，此时无法级联删除。

mysql> DELETE FROM parent WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

如果想级联更新和删除，在创建子表（child）时加入CASCADE关键字。同样Oracle中也支持CASCADE，在Oracle中创建外键时注意给这个列加上索引，具体用法可能略有差异。删除原表，重新创建子表child，并加入给update与delete条件加入CASCADE属性。

DROP TABLE child;-- 删除原有创建子表child

-- 重新创建子表child，并加入给update与delete条件加入CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;

子表（child）插入测试数据：

mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

更新主表（parent）id值为2：

mysql> UPDATE parent SET id = 2 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查询验证主表（parent）：

mysql> select * from parent;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

查询验证子表（child）的parent_id值：此时已经全部更新（update）成 2

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+
3 rows in set (0.00 sec)

演示级联删除效果：此时可以删除数据内容

mysql> delete from parent where id=2;
Query OK, 1 row affected (0.01 sec)

再次查看子表（child）：此时子表中的数据内容一并删除掉

mysql> SELECT * FROM child;
Empty set (0.00 sec)

补充一点，如果想查看表约束，可以通过命令去验证show create table table_name：

查到子表（child）已经自动在外键列加入了索引。

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

友情提示：MySQL数据库外键是即时检查的，对每一行都会运行外键检查。导入数据，在检查外键约束上往往消耗大量时间。有时候，可以灵活处理，在导入过程中忽略外键检查：set foreign_key_checks=0，默认值是1，开启了外键约束检查。

前面列举示例进行外键功能说明，接下来配合锁进行描述。

3.6.2 外键与锁

在InnoDB存储引擎中，对于一个外键列，如果没有显示地（人为手动添加）对这个列添加索引，在InnoDB存储引擎会自动对其加一个索引，因此可以避免表锁。这一点比Oracle数据库做得更好，Oracle数据库使用外键时，需要人为手动给该列添加索引。

对于外键值插入和更新，首先需要查询父表（parent）中的记录，即select父表。但对于父表进行select操作，不是使用一致性非锁定读方式，这样会发生数据不一致问题。因此这时使用的是select ... lock in share mode方式（共享锁），主动给父表加一个S锁。如果父表已经加了X锁，子表操作会被阻塞。（可以在两个会话窗口进行测试）

示例阻塞：

分别在session1会话和session2会话窗口执行事务。session1会话进行删除父表（parent）id为1的内容，session2会话执行插入内容到子表（child），发现session2此时发生阻塞，阻塞等待超时发出警告（默认50秒）。

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from parent where id=1;
Query OK, 1 row affected (0.01 sec)

-- session2，阻塞等待超时发出警告（默认50秒）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into child select 4,1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此时子表（child）处于锁定等待中，在MySQL8.0中可以使用data_locks参数进行分析：

mysql> select * from performance_schema.data_locks order by event_id desc limit 0,1\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2079935859840:93:5:1:2079912818080
ENGINE_TRANSACTION_ID: 16653
            THREAD_ID: 48
             EVENT_ID: 12
        OBJECT_SCHEMA: test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: par_ind
OBJECT_INSTANCE_BEGIN: 2079912818080
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
1 row in set (0.00 sec)

锁等待，在MySQL8.0中可以使用data_lock_waits参数进行分析：

mysql> select * from performance_schema.data_lock_waits  limit 0,1\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 2079935860616:91:4:2:2079912823744
REQUESTING_ENGINE_TRANSACTION_ID: 16658
            REQUESTING_THREAD_ID: 49
             REQUESTING_EVENT_ID: 10
REQUESTING_OBJECT_INSTANCE_BEGIN: 2079912823744
         BLOCKING_ENGINE_LOCK_ID: 2079935859840:91:4:2:2079912817048
  BLOCKING_ENGINE_TRANSACTION_ID: 16653
              BLOCKING_THREAD_ID: 48
               BLOCKING_EVENT_ID: 12
  BLOCKING_OBJECT_INSTANCE_BEGIN: 2079912817048
1 row in set (0.00 sec)

前面介绍锁类型也提到过data_locks和data_lock_waits这两个参数，MySQL8.0之前在information_schema架构下有INNODB_LOCKS、INNODB_LOCK_WAITS两个系统参数可以进行参考。此处进行示例，也算补足在锁类型章节没有进行示例演示。

下面是官方对外键锁定介绍：MySQL在必要时扩展元数据锁，通过外键约束关联表。扩展元数据锁可以防止DML和DDL操作在相关表上并发执行引起的冲突。该特性还支持在父表被修改时，更新外键元数据。MySQL早期版本中，外键元数据(由子表拥有)不能安全更新。如果一个表被LOCK TABLES显式锁定，那么任何与外键约束相关的表都会被隐式打开和锁定。对于外键检查，在相关表上获取一个共享只读锁(LOCK TABLES READ)。对于级联更新，在操作涉及的相关表上获取一个无共享的写锁(LOCK TABLES WRITE)。

外键定义和元数据（Foreign Key Definitions and Metadata）。查看外键定义，可以使用SHOW CREATE TABLE child\G，之前也提到过，这里不再赘述。

如下是查看到数据库中哪些表使用到的外键信息，显示数据库名（TABLE_SCHEMA）、表名（TABLE_NAME）、字段列名（COLUMN_NAME）以及外键约束名（CONSTRAINT_NAME）。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+-----------------+----------------------+---------------------------+
| TABLE_SCHEMA | TABLE_NAME      | COLUMN_NAME          | CONSTRAINT_NAME           |
+--------------+-----------------+----------------------+---------------------------+
| world        | city            | CountryCode          | city_ibfk_1               |
| world        | countrylanguage | CountryCode          | countryLanguage_ibfk_1    |
| test         | child           | parent_id            | child_ibfk_1              |
...
+--------------+-----------------+----------------------+---------------------------+
25 rows in set (0.02 sec)

查询INFORMATION_SCHEMA架构下的INNODB_FOREIGN，使用limit查询2条记录进行演示。world数据库与sakila数据库均为MySQL官方示例，前面有官方链接，可自行获取。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN limit 0,2 \G
*************************** 1. row ***************************
      ID: world/city_ibfk_1
FOR_NAME: world/city
REF_NAME: world/country
  N_COLS: 1
    TYPE: 48
*************************** 2. row ***************************
      ID: sakila/fk_address_city
FOR_NAME: sakila/address
REF_NAME: sakila/city
  N_COLS: 1
    TYPE: 4
2 rows in set (0.00 sec)

3.7 锁的算法

在描述锁类型，我已经将InnoDB存储引擎中锁类型全部列举出来了。

15.7.1 InnoDB Locking。

3.7.1 行锁的3种算法

InnoDB存储引擎有3种行锁算法：

1. Record Locks：单个行记录上的锁；
2. Gap Locks：间隙锁，锁定一个范围，不包含记录本身；
3. Next-Key Locks：Record Locks和Gap Locks，锁定一个范围，并且锁定记录本身。

Record Locks记录锁总是锁定索引记录。即使表没有定义索引，对于这种情况InnoDB会创建一个隐藏的聚集索引，并使用这个索引进行记录锁定。

Next-Key Locks是结合了Record Locks和Gap Locks的一种锁定算法，在Next-Key Locks算法下，InnoDB对于行的查询都是采用这种锁定算法。

InnoDB执行行级锁的方式是这样的：当它搜索或扫描一个表索引时，它会在遇到的索引记录上设置共享或排它锁。因此，行级锁实际上是索引记录锁。索引记录上的next-key锁也会影响该索引记录之前的间隙。也就是说，next-key锁是索引记录锁加上索引记录之前的间隙锁。如果一个会话对索引中的记录R有一个共享或排它锁，那么另一个会话不能在紧挨着索引顺序的R之前的间隙插入一个新索引记录。

假设一个索引包含值10、11、13和20。此索引可能的next-key锁覆盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

如果事务T1已经通过Next-Key Locks锁定如下范围：

(10, 11]、(11, 13]

当插入新记录12时，锁定范围会变成：

(10, 11]、(11,12]、(12, 13]

当查询的索引包含唯一属性时，InnoDB存储引擎会对Next-Key Locks进行优化，将其降级为Record Locks，仅锁住索引本身，而不是范围。在InnoDB存储引擎中，对于insert操作，会检查插入记录的一条记录是否被锁定，如果已经被锁定，则不允许查询。

3.7.2 解决Phantom Problem

什么是Phantom Problem？：指在同一事务中，连续执行两次同样的SQL语句可能导致不同的结果，第二次执行的SQL语句可能返回之前不存在的行。

目的：解决数据一致性。你可以联想到幻读、脏读、更新丢失，其实也是为了解决数据一致性问题。

当同一查询在不同时间产生不同的行集时，就会在事务中出现所谓的幻影问题。例如，如果一个SELECT被执行了两次，但是第二次返回了第一次没有返回的一行，那么该行就是一个幻像行。

假设子表的id列上有一个索引，您希望读取和锁定表中标识符值大于100的所有行，以便稍后更新选中行的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查询从 id 大于 100 的第一条记录开始扫描索引。让表包含 id 值为 90 和 102 的行。如果在扫描的索引记录上设置的锁范围不锁定间隙锁记录（在这种情况下，90 和 102 之间的间隙记录），另一个 session 可以在表中插入id 为101的新行。如果在同一个事务中，要执行相同的 SELECT，此时查询返回，会在结果集中看到一个 id 为 101 的新行（幻像） 。如果将一组行视为一个数据项，则新的幻像将违反 一个事务运行的事务隔离原则，以便它拥有的数据 （read 操作） 在事务期间不会改变。

InnoDB存储引擎提供了SQL92标准所描述的四种事务隔离级别：

• READ UNCOMMITTED：未提交读
• READ COMMITTED：已提交读
• REPEATABLE READ ：可重复读
• SERIALIZABLE：可串行化（序列化）

而InnoDB默认事务隔离级别是REPEATABLE READ，通过如下命令可以查询到。transaction_isolation系统参数是动态的，可以在数据库运行过程中进行调整测试，你也可以在不同会话中测试不同事务隔离级别。

当然，你还可以在my.ini或者my.cnf配置文件中设置测试：transaction-isolation=name，name为上面介绍的事务隔离级别

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

为了解决phantoms problem，InnoDB使用了一种称为next-key locking锁的算法，它结合了索引行锁（index-row
locking ）和间隙锁（Gap Locks）。InnoDB执行行级锁的方式是这样的：当它搜索或扫描一个表索引时，它会在遇到的索引记录上设置共享或排它锁。因此，行级锁实际上是索引记录锁。此外，索引记录上的next-key锁也会影响索引记录之前的间隙。也就是说，next-key锁是索引记录锁加上索引记录之前的间隙锁。

当InnoDB扫描一个索引时，它也可以锁定索引中最后一条记录之后的间隙。就像上面的例子中所发生的那样：为了防止表中插入任何id大于100的行，InnoDB设置的锁包含了id值102后面的一个锁。

你可以在应用程序中使用next-key locking来实现唯一性检查：如果阅读了共享模式下的数据，并且看不到要插入行的重复项（看不到幻象），那么可以安全地插入行，并知道读取期间在行的后续设置的next-key locking锁，防止任何人同时在你所使用的行插入重复项。因此，next-key锁定能够锁定表中不存在的内容。

可以禁用间隙锁定，这可能会导致幻象问题，因为当间隙锁定被禁用时，其它会话可能会将新行插入到间隙中。

个人理解难免有些不到位，如果给你带来误解，我表示抱歉。你可以找到参考文档：

15.7.4 Phantom Rows

同时你还可以参考这本书籍《MySQL技术内幕InnoDB存储引擎 第2版》，如果作者能针对MySQL8.0进行更新就好了。虽然过去快10年了，依然是一本经典书籍，颇有参考意义，便于理解InnoDB。

3.8 阻塞、死锁、锁升级

3.8.1 阻塞

如何理解阻塞，想象一下有东西被堵住了，如何处理。

数据库中阻塞：因为不同锁之间的兼容性关系，在某些时刻一个事务中的锁需要等待另一事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，为了保证事务并发并且正常运行。

在InnoDB存储引擎中，控制阻塞等待时间参数innodb_lock_wait_timeout，默认值为50秒。

查询示例：说明一下，在文中多次用到select @@系统参数查询。当然，在官方文档中也有参数说明。

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set (0.00 sec)

临时设置生效，如下：

查看文档，参数时是动态的，在数据库运行时是可以修改的。

mysql> set @@innodb_lock_wait_timeout=60;	-- set和@符号之间可以不加空格
Query OK, 0 rows affected (0.00 sec)

如果想永久生效，可以在my.ini或者my.cnf中加入参数innodb-lock-wait-timeout=#（例如设置60），重启服务生效。

此外，还有一个参数innodb_rollback_on_timeout用于设定是否在等待超时时对进行中的事务进行回滚操作。默认值是OFF，查询出来值是0，代表不回滚。查询示例如下：

mysql> select @@innodb_rollback_on_timeout;
+------------------------------+
| @@innodb_rollback_on_timeout |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

查看文档，由于非动态是非动态，在数据库运行时，不允许被更改。一旦更改，会提示参数只读。

关于参数是不是动态，看文档参数说明Dynamic值（YES代表动态，NO为非动态），默认值参数说明为Default Value。

mysql> set @@innodb_rollback_on_timeout=1;
ERROR 1238 (HY000): Variable 'innodb_rollback_on_timeout' is a read only variable

当发生超时，MySQL数据库会抛出异常ERROR 1205：

mysql> begin
Query OK, 0 rows affected (0.00 sec)

mysql> select * from world.city where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

友情提示：在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。InnoDB存储引擎在绝大多数情况下，都不会对异常进行回滚。

15.14 InnoDB Startup Options and System Variables

3.8.2 死锁

死锁概念：死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待现象。如果没有外力作用，事务将无法推进。解决死锁问题最直接方式是不等待，将任何等待转换为回滚，并且事务重新开始。这种做法确实可以避免死锁产生，但在线上环境中，这可能导致并发性能下降，甚至任何一个事务都不能进行。带来的问题，比死锁更严重，很难发现问题并浪费资源。

解决死锁问题最简单一种方法是超时，当两个事务互相等待时，等待时间超过系统参数设置阈值时，其中一个事务进行回滚，另一个等待的事务继续进行。在InnoDB存储引擎中，参数innodb_rollback_on_timeout用来设置超时时间，前面讲解阻塞提到过。

超时机制是一种简单解决方法，仅通过超时后对事务进行回滚处理，或者是根据First In，First Out（FIFO），一进一出顺序选择回滚对象。如果超时事务所占权重比较大，事务操作更新很多行，占用较多undo log，这时采用FIFO方式并不那么合适。回滚事务时间相对一个事务所占用时间会更多。

除了超时机制，可寻求其它解决方案。当前数据库普遍采用wait-for graph（等待图）方式，主动检测死锁，判断是否存在回路。要求数据库保存以下两种信息：

• 锁信息链表；
• 事务等待链表。

等图方式是之前版本中的一种，当然也还有新的处理方式。

CATS算法通过分配一个调度权重对等待的事务进行优先级排序，该权重是根据一个事务块的事务数量计算出来的。例如，如果两个事务正在等待同一个对象上的一个锁，那么阻塞最多事务的事务将被分配更大的调度权重。如果权值相等，则优先级为等待时间最长的事务。

在MySQL 8.0.20之前，InnoDB也使用先进先出(FIFO)算法来调度事务，CATS算法只在重锁争用的情况下使用。MySQL 8.0.20中的CATS算法增强使FIFO算法冗余，允许删除它。之前由FIFO算法执行的事务调度是由MySQL 8.0.20的CATS算法执行的。在某种情况下，此更改可能会影响授予事务锁的顺序。

友情提示：MySQL8.0.20后，新版InnoDB使用争用感知事务调度(CATS)算法对等待锁的事务进行优先级排序。当多个事务在同一个对象上等待一个锁时，CATS算法确定哪个事务首先接收这个锁。

15.7.6 Transaction Scheduling

死锁概率：

一般而言，死锁概率应该发生非常少，如果经常发生，系统是不可用的。

死锁次数，应该少于等待，至少需要两次等待才会产生一次死锁。

1. 一定环境下，系统事务数量越多，发生死锁概率越大；
2. 每个事务操作数量越多，发生死锁概率越大；
3. 操作数据集合越小，发生死锁概率越大。

死锁示例：

如果程序是串行的，那么不可能发生死锁，比如MyISAM存储引擎不会出现死锁，要么全部获取，要么全不获取。死锁只存在于并发情况下，数据库本身是一个并发运行程序，可能会发生死锁。

具体SQL语句就不贴出来，可以参考上面使用进行模拟场景。在两个会话窗口session1和session2中进行执行获取排它锁，注意执行之前使用begin开始事务。

死锁原因：两个会话资源互相等待。大多数死锁InnoDB存储引擎可以侦测到，无需人为进行干预。发现死锁，InnoDB存储引擎会立刻回滚一个事务。

在Oracle数据库中产生死锁常见原因是没有对外键添加索引，而MySQL数据库InnoDB存储引擎会自动为外键上索引，避免这种情况发生。人为删除外键索引，MySQL会抛出一个异常。

3.8.3 锁升级

锁升级（lock escalation）是指将当前锁粒度降低。

打个比方，数据库可以将1000个行锁升级为一个页锁，或者将页锁升级为表锁。如果数据库设计人为锁是一种稀有资源，想避免锁开销，数据库中会频繁出现锁升级。

友情提示：MySQL中InnoDB事务模型目标是将多版本数据库（MVCC）最佳特性与传统两阶段锁结合起来。InnoDB在行级执行锁定，默认情况下以非锁定的一致读取方式运行查询，这是Oracle的风格。InnoDB中的锁信息被有效地存储在空间中，因此不需要锁升级。通常，允许多个用户锁定InnoDB表中的每一行，或者任意随机的行子集，而不会导致InnoDB内存耗尽。

MySQL8.0中InnoDB锁和事务模型可以参考refman-8.0文档：15.7 InnoDB Locking and Transaction Model

参考资料&鸣谢：

《深入浅出MySQL 第2版 数据库开发、优化与管理维护》，个人参考优化篇部分。

《MySQL技术内幕InnoDB存储引擎 第2版》，个人参考索引与锁章节描述。

MySQL8.0官网文档：refman-8.0-en.pdf，要学习新版本，官方文档是非常不错的选择。

虽然书籍年份比较久远（停留在MySQL5.6.x版本），但仍然具有借鉴意义。

最后，对以上书籍和官方文档所有作者表示衷心感谢。让我充分体会到：前人栽树，后人乘凉。

莫问收获，但问耕耘

只停留在看上面，提升效果甚微。应该带着思考去测试佐证，或者使用（同类书籍）新版本进行对比，这样带来的效果更好。最重要的一环，养成阅读官方文档，是一个良好的习惯。能编写官方文档，至少证明他们在这个领域是有很高的造诣，对用法足够熟练。

能看到这里的，都是帅哥靓妹。以上是本次MySQL优化篇（上部分）全部内容，希望能对你的工作与学习有所帮助。感觉写的好，就拿出你的一键三连。如果感觉总结的不到位，也希望能留下您宝贵的意见，我会在文章中定期进行调整优化。好记性不如烂笔头，多实践多积累。你会发现，自己的知识宝库越来越丰富。原创不易，转载也请标明出处和作者，尊重原创。

一般情况下，会优先在公众号发布：龙腾万里sky。

不定期上传到github仓库：

https://github.com/cnwangk/SQL-study