MySQL 并发教程（一）
一、简介
就数据库而言，并发和锁定是一些最复杂的主题。当条件“刚刚好”时，通常执行快速且没有问题的查询可能会突然花费更长时间或失败并出现错误，因此锁定或争用成为一个问题。你可能会问自己，为什么锁会引起这样的问题。这一章和接下来的 11 章将试图解释这一点，以及你如何最好地处理它们。本书的最后六章通过六个案例研究，将现实场景中的信息与分析以及如何避免或减少问题结合在一起。
在这一章中，你将首先学习为什么锁是重要的，尽管它们会引起一些问题。然后将解释与事务的关系。本章的其余部分介绍了本书中如何使用示例，以及本书中用于示例的world、sakila和employees数据库。
为什么需要锁？
这似乎是一个不需要锁定数据库的完美世界。然而，价格会很高，只有少数用例可以使用该数据库，而且对于 MySQL 这样的通用数据库来说，避免锁是不可能的。如果没有锁定，就不能有任何并发性。假设只允许一个到数据库的连接(你可以说它本身是一个锁，因此系统不是无锁的)——这对大多数应用来说不是很有用。
Note
通常，MySQL 中所谓的锁实际上是一个锁请求，它可以处于授权或挂起状态。
当您有几个连接同时执行查询时，您需要某种方法来确保这些连接不会互相妨碍。这就是锁进入画面的地方。你可以把锁想象成道路交通中的交通信号(图 1-1 )，它控制资源的使用以避免事故。在道路交叉路口，要保证两车不交叉，不发生碰撞。
图 1-1
数据库中的锁类似于交通灯
在数据库中，有必要确保两个查询对数据的访问不冲突。由于控制进入十字路口有不同的级别——让行、停车标志和交通灯——数据库中有不同的锁类型。
锁定级别
MySQL 中的锁有几种风格，在 MySQL 的不同级别上起作用，从用户级锁到记录锁。最高层是用户级锁，可以保护应用中的整个代码路径和数据库中的任何对象。中间是操作数据库对象的锁。这些锁包括保护表元数据的元数据锁和保护表中所有数据的表锁。用户级锁和表级锁的共同点是它们是在数据库的 SQL 层实现的。高级锁在第 6 章中讨论。
最底层是由存储引擎实现的锁。本质上，这些锁取决于您使用的存储引擎。由于 InnoDB 是 MySQL 中使用最多的存储引擎(也是默认的)，本书涵盖了 InnoDB 特有的锁。InnoDB 包括记录上的锁，这是最容易理解的，以及更难的概念，如间隙锁、下一个键锁、谓词锁和插入意图锁。此外，还有互斥和信号量(这也发生在 SQL 层)。特定于 InnoDB 的锁和互斥/信号量将在第 7 章中介绍。
锁和事务
乍一看，将锁和事务的主题合并到一本关于并发性的书中似乎有些奇怪。然而，正如你将在本书中看到的几个例子一样，它们是紧密相关的。一些锁在事务期间被持有，因此理解事务如何工作以及如何监控它们是很重要的。
在使用锁时，事务隔离级别的概念也很重要。隔离级别会影响使用哪些锁以及持有这些锁的时间。
第 3 和 4 章讲述了如何监控事务，第 11 和 12 章讲述了事务如何工作、它们的影响以及事务隔离级别。
例子
整本书都有例子来帮助说明正在讨论的主题，或者设置一个你可以研究的情境。除了第 17 章和第 18 章之外，列出了重现试验所需的所有声明。一般来说，对于这些示例，您将需要不止一个连接，所以查询的提示已经被设置为在重要的时候指示哪个连接用于哪个查询。例如，Connection 1>意味着查询应该由您的第一个连接执行。
本书中的所有例子都是在 MySQL Shell 中执行过的。为简洁起见，示例中的提示是mysql>，除非连接很重要或者语言模式不是 SQL。然而，这些示例也可以在旧的mysql命令行客户端上运行。
Tip
如果您不熟悉 MySQL Shell，那么它是第二代 MySQL 命令行客户端，同时支持 SQL、Python 和 JavaScript。它还带有几个内置的实用程序，包括用于管理 MySQL InnoDB 集群和传统复制拓扑的工具。有关 MySQL Shell 的介绍，请参见位于 https://dev.mysql.com/doc/mysql-shell/en/ 的用户指南或查尔斯·贝尔( www.apress.com/gp/book/9781484250822 )的《?? 介绍 MySQL Shell (Apress》)一书。
此外，这本书还附带了一个 Python 模块—concurrency_book.generate,可以导入到 MySQL Shell 中，用于重现除了最简单的例子之外的所有例子。本节的其余部分描述了如何使用 MySQL Shell 模块。这里的内容是附录 B 的摘录，其中包含该模块的更长的参考，包括如何实现您自己的示例。
Note
本质上，示例中的一些数据对于每次执行都是不同的。对于 id 和存储器地址等尤其如此。因此，当您试图重现这些示例时，不要期望在所有细节上都得到相同的结果。
concurrency_book.generate 模块的先决条件
使用本书提供的 MySQL Shell 模块的最重要要求是，您使用的是 MySQL Shell 8.0.20 或更高版本。这是一个严格的要求，因为模块主要使用shell.open_session()方法来创建测试用例所需的连接。此方法仅在 8.0.20 版中引入。shell.open_session()相对于mysql.get_classic_session()和mysqlx.get_session()的优势在于open_session()透明地与经典的 MySQL 协议和新的 X 协议一起工作。
如果出于某种原因，您被老版本的 MySQL Shell 所困，您可以更新测试用例，以包括protocol设置(参见附录 B 中的定义工作负载)来明确指定使用哪个协议。
还要求从 MySQL Shell 到 MySQL Server 的连接已经存在，因为模块在创建示例所需的附加连接时会使用该连接的 URI。
这些例子已经在 MySQL Server 8.0.21 上进行了测试；然而，大多数例子都适用于旧版本，有些甚至适用于 MySQL 5.7。也就是说，建议使用 MySQL Server 8.0.21 或更高版本。
安装 concurrency_book.generate 模块
要使用该模块，你需要从本书的 GitHub 库下载concurrency_book目录下的文件(链接可以在本书的首页 www.apress.com/gp/book/9781484266519 找到)。最简单的方法是克隆存储库或使用图 1-2 所示的菜单下载包含所有文件的 ZIP 文件。
图 1-2
用于克隆或下载资源库的 GitHub 菜单
点击剪贴板图标，使用您系统的 Git 软件复制用于克隆存储库的 URL，或者使用下载 ZIP 链接下载存储库的 ZIP 文件。只要保持concurrency_book目录下的结构，您可以自由选择任何路径作为文件的位置。对于这个讨论，假设您已经克隆了存储库或者将文件解压缩到了C:\Book\mysql-concurrency，所以generate.py文件在目录C:\Book\mysql-concurrency\concurrency_book\中。
为了能够在 MySQL Shell 中导入模块，打开或创建mysqlshrc.py文件。MySQL Shell 在四个地方搜索该文件。在 Microsoft Windows 上，路径按搜索顺序排列:
-
%PROGRAMDATA%\MySQL\mysqlsh\ -
%MYSQLSH_HOME%\shared\mysqlsh\ -
<mysqlsh binary path>\ -
%APPDATA%\MySQL\mysqlsh\
在 Linux 和 Unix 上
-
/etc/mysql/mysqlsh/ -
$MYSQLSH_HOME/shared/mysqlsh/ -
<mysqlsh binary path>/ -
$HOME/.mysqlsh/
始终搜索所有四个路径，如果在多个位置找到文件，将执行每个文件。这意味着，如果文件影响相同的变量，则最后找到的文件优先。如果你做出对你个人有意义的改变，最好的地方是在第四个位置。步骤 4 中的路径可以用环境变量MYSQLSH_USER_CONFIG_HOME覆盖。
您需要确保mysqlshrc.py文件将包含该模块的目录添加到 Python 搜索路径中，并且可以选择添加一个import语句，以便在启动 MySQL Shell 时该模块可用。mysqlshrc.py文件的一个例子是
import sys
sys.path.append('C:\\Book\\mysql-concurrency')
import concurrency_book.generate
双反斜杠用于窗口；在 Linux 和 Unix 上，不需要对分隔路径元素的斜杠进行转义。如果您没有在mysqlshrc.py文件中包含import，您将需要在 MySQL Shell 中执行它，然后才能使用该模块。
获取信息
该模块包括两个返回如何使用该模块的信息的方法。一个是help()方法，提供如何使用模块的信息:
mysql-py> concurrency_book.generate.help()
还有一个show()方法，它列出了run()方法可以执行的工作负载和load()方法可以加载的模式:
mysql-py> concurrency_book.generate.show()
工作负载以书中的代码清单命名，例如，名为“清单 6-1 的工作负载实现了清单 6-1 中的示例。
在开始执行工作负载之前，您需要加载一些测试数据，这个模块也可以为您完成。
加载测试数据
concurrency_book.generate模块支持将employees、sakila和world示例数据库加载到 MySQL 实例中。对于employees数据库，您可以选择带有分区的版本。对于这本书来说，world数据库是最重要的，其次是sakila数据库。employees数据库仅用于第 18 章中的案例研究。这三种模式的每一种都将在本章的后面进行更详细的描述。
Note
如果该模式存在，它将作为加载作业的一部分被删除。这实际上意味着load()重置了模式。
您可以用load()方法加载一个模式，该方法可以选择您想要加载的模式的名称。如果不提供模式名，系统会提示您。清单 1-1 展示了一个加载world模式的例子。
mysql-py> concurrency_book.generate.load()
Available Schema load jobs:
===========================
# Name Description
---------------------------------------------------------------------------
1 employees The employee database
2 employees partitioned The employee database with partitions
3 sakila The sakila database
4 world The world database
Choose Schema load job (# or name - empty to exit): 4
2020-07-20 21:27:15.221340 0 [INFO] Downloading https://downloads.mysql.com/docs/world.sql.zip to C:\Users\myuser\AppData\Roaming\mysql_concurrency_book\sample_data\world.sql.zip
2020-07-20 21:27:18.159554 0 [INFO] Processing statements in world.sql
2020-07-20 21:27:27.045219 0 [INFO] Load of the world schema completed
Available Schema load jobs:
===========================
# Name Description
---------------------------------------------------------------------------
1 employees The employee database
2 employees partitioned The employee database with partitions
3 sakila The sakila database
4 world The world database
Choose Schema load job (# or name - empty to exit):
Listing 1-1Loading the world schema
load()方法下载带有模式定义的文件，如果它还没有模式定义的话。下载的文件在微软 Windows 上存储在%APPDATA\mysql_concurrency_book\sample_data\中，在其他平台上存储在${HOME}/.mysql_concurrency_book/sample_data/中。如果您想要重新下载该文件，请将其从该目录中删除。
Tip
由于 MySQL Shell 的 Python 中只有相对低级的网络例程，如果您的连接速度慢或不稳定，下载 employees 数据库可能会失败。除了手动安装模式之外，还有一种选择是下载 https://github.com/datacharmer/test_db/archive/master.zip 并将其保存在sample_data目录中。在那之后，load()方法将获取它，并且不再尝试下载它。
如果您只想加载一个模式，您可以将名称指定为load()的参数。例如，当调用 MySQL Shell 时，在命令行上直接给出命令来启动模式加载时，这可能特别有用
shell> mysqlsh --user=myuser --py -e "concurrency_book.generate.load('world')"
当您加载完您需要的模式后，您可以用一个空答案来回答退出。您现在已经准备好执行工作负载了。
Note
如果加载过程崩溃，抱怨文件，例如，它不是一个 ZIP 文件，那么它表明文件损坏或不完整。在这种情况下，请删除该文件，以便重新下载，或者尝试使用浏览器手动下载该文件。
执行工作负荷
您用run()方法执行一个工作负载。如果指定已知工作负荷的名称，那么该工作负荷将立即执行。否则，将列出可用的工作负荷，并提示您输入工作负荷。在这种情况下，您可以通过数量(例如，列表 6-1 中的 15)或名称来指定工作量。使用名称时，只要至少有一个空格，Listing和列表编号之间的空格数并不重要。当您使用提示选择工作负荷时，您可以在前一个工作负荷完成后选择另一个工作负荷。
工作负载完成后，对于几个工作负载，您将获得一个您可以进行的调查的建议列表。例如，这可以是查询示例中使用的连接持有的锁。这些调查旨在激发灵感，鼓励您使用自己的查询来探索工作负载。在例子的讨论中也使用了一些调查。清单 1-2 展示了一个使用提示符执行工作负载的例子。
mysql-py> concurrency_book.generate.run()
Available workloads:
====================
# Name Description
---------------------------------------------------------------------------
1 Listing 2-1 Example use of the metadata_locks table
2 Listing 2-2 Example of using the table_handles table
3 Listing 2-3 Using the data_locks table
...
14 Listing 5-2 Example of obtaining exclusive locks
15 Listing 6-1 A deadlock for user-level locks
...
Choose workload (# or name - empty to exit): 15
Password for connections: ********
2020-07-20 20:50:41.666488 0 [INFO] Starting the workload Listing 6-1
****************************************************
* *
* Listing 6-1\. A deadlock for user-level locks *
* *
****************************************************
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 105 249 6
-- 2 106 250 6
-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_1', -1);
+---------------------------+
| GET_LOCK('my_lock_1', -1) |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.0003 sec)
-- Connection 2
Connection 2> SELECT GET_LOCK('my_lock_2', -1);
+---------------------------+
| GET_LOCK('my_lock_2', -1) |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.0003 sec)
Connection 2> SELECT GET_LOCK('my_lock_1', -1);
-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_2', -1);
ERROR: 3058: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
Available investigations:
=========================
# Query
--------------------------------------------------
1 SELECT *
FROM performance_schema.metadata_locks
WHERE object_type = 'USER LEVEL LOCK'
AND owner_thread_id IN (249, 250)
2 SELECT thread_id, event_id, sql_text,
mysql_errno, returned_sqlstate, message_text,
errors, warnings
FROM performance_schema.events_statements_history
WHERE thread_id = 249 AND event_id > 6
ORDER BY event_id
...
Choose investigation (# - empty to exit): 2
-- Investigation #2
-- Connection 3
Connection 3> SELECT thread_id, event_id, sql_text,
mysql_errno, returned_sqlstate, message_text,
errors, warnings
FROM performance_schema.events_statements_history
WHERE thread_id = 249 AND event_id > 6
ORDER BY event_id\G
*************************** 1\. row ***************************
thread_id: 249
event_id: 7
sql_text: SELECT GET_LOCK('my_lock_1', -1)
mysql_errno: 0
returned_sqlstate: NULL
message_text: NULL
errors: 0
warnings: 0
*************************** 2\. row ***************************
thread_id: 249
event_id: 8
sql_text: SELECT GET_LOCK('my_lock_2', -1)
mysql_errno: 3058
returned_sqlstate: HY000
message_text: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
errors: 1
warnings: 0
*************************** 3\. row ***************************
thread_id: 249
event_id: 9
sql_text: SHOW WARNINGS
mysql_errno: 0
returned_sqlstate: NULL
message_text: NULL
errors: 0
warnings: 0
3 rows in set (0.0009 sec)
Available investigations:
=========================
# Query
--------------------------------------------------
...
Choose investigation (# - empty to exit):
2020-07-20 20:50:46.749971 0 [INFO] Completing the workload Listing 6-1
-- Connection 1
Connection 1> SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.0004 sec)
-- Connection 2
Connection 2> SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.0002 sec)
2020-07-20 20:50:46.749971 0 [INFO] Disconnecting for the workload Listing 6-1
2020-07-20 20:50:46.749971 0 [INFO] Completed the workload Listing 6-1
Available workloads:
====================
# Name Description
---------------------------------------------------------------------------
1 Listing 2-1 Example use of the metadata_locks table
2 Listing 2-2 Example of using the table_handles table
3 Listing 2-3 Using the data_locks table
...
Choose workload (# or name - empty to exit):
mysql-py>
Listing 1-2Executing a workload using the prompt
从这个例子中可以注意到一些事情。选择工作负载后，会要求您输入密码。这是您正在使用的 MySQL 帐户的密码。其他连接选项取自 MySQL Shell 中的session.uri属性，但是出于安全原因，不会存储密码。如果您在一次调用run()中执行多个工作负载，您将只被提示输入一次密码。
在开始执行工作负载时，对于工作负载使用的每个连接，在工作负载开始之前，有一个进程列表 id(从SHOW PROCESSLIST开始)、(性能模式)线程 id 和最后事件 id 的概述:
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 105 249 6
-- 2 106 250 6
您可以使用这些 id 来执行您自己的调查查询，并且您可以使用 overview 来识别已经在concurrency_book.generate.run()中作为工作负载实现的清单。
在工作负载执行结束时，该示例有三个查询，您可以执行这些查询来调查该示例演示的问题。您可以通过指定查询编号(一次一个查询)来执行一个或多个查询。在本书的代码清单中，调查的输出前面有一个注释，显示已经执行了哪个调查，例如
-- Investigation #2
每个工作量的调查数量从零到十多个不等。书中的列表并不总是包括所有调查的结果，因为有些是作为灵感和对问题的进一步研究而留下的。
完成调查后，提交一个空答案以退出工作量。如果您不想执行更多的工作负载，请再次提交一个空答案以退出run()方法。
如果您只想执行一个工作负载，那么您可以将名称指定为run()的参数。例如，当调用 MySQL Shell 时，在命令行上直接给出命令来执行工作负载时，这可能特别有用
shell> mysqlsh --user=myuser --py -e "concurrency_book.generate.run('Listing 6-1')"
本章的剩余部分描述了本书中示例使用的三种模式。
测试数据:世界模式
world样本数据库是简单测试中最常用的数据库之一。它由三个有几百到几千行的表组成。这使它成为一个小数据集，这意味着它甚至可以很容易地用于小的测试实例。
计划
数据库由city、country和countrylanguage表组成。表格之间的关系如图 1-3 所示。
图 1-3
world数据库
country表包含关于 239 个国家的信息，并作为来自city和countrylanguage表的外键的父表。数据库中总共有 4079 个城市和 984 种国家和语言的组合。
装置
您可以从 https://dev.mysql.com/doc/index-other.html 下载包含表格定义和数据的文件。在图 1-4 所示的示例数据库部分，Oracle 提供了从该页面访问多个示例数据库的权限。
图 1-4
包含示例数据库链接的表
下载的文件由一个名为world.sql.gz或world.sql.zip的文件组成，这取决于您选择的是 Gzip 还是 zip 链接。在这两种情况下，下载的档案包含一个文件world.sql。数据的安装非常简单，只需执行脚本即可。
您可以从 MySQL Shell 或mysql命令行客户端获得world.sql。在 MySQL Shell 中，使用\source命令加载数据:
MySQL [localhost ssl] SQL> \source world.sql
如果您使用传统的mysql命令行客户端，请使用SOURCE命令:
mysql> SOURCE world.sql
在这两种情况下，如果world.sql文件不在您启动 MySQL Shell 或mysql的目录中，请添加该文件的路径。
如果您喜欢使用 GUI，那么您也可以使用 MySQL Workbench 加载world数据库。当连接到您想要加载world模式的 MySQL 实例时，您点击菜单中的文件，然后点击运行 SQL 脚本，如图 1-5 所示。
图 1-5
从 MySQL Workbench 运行 SQL 脚本
这将打开一个文件浏览器，您可以在其中浏览文件。导航到保存未压缩的world.sql文件的目录并选择它。结果是如图 1-6 所示的对话框，您可以在其中查看脚本的第一部分，并可选地设置默认的模式名和字符集。
图 1-6
MySQL Workbench 中用于检查脚本的对话框
在使用world模式的情况下，模式名称和字符集都包含在脚本中，所以不需要(也没有效果)设置这些设置。点击运行来执行脚本。MySQL 执行脚本时，会有一个对话框显示进度信息。操作完成后，关闭对话框。可选地，您可以通过点击如图 1-7 所示的两个互相追逐的箭头来刷新侧边栏中的模式列表。
图 1-7
通过单击两个箭头刷新模式列表
虽然world模式因为其简单性和小尺寸而非常适合于许多测试，但这也限制了它的有用性，并且有时需要稍微复杂一点的数据库。
测试数据:sakila 模式
sakila数据库是一个真实的数据库，它包含一个电影租赁业务的模式，其中包含关于电影、库存、商店、员工和客户的信息。它添加了一个全文索引、一个空间索引、视图和存储程序，以提供一个使用 MySQL 特性的更完整的示例。数据库大小仍然非常适中，这使它适合于小型实例。
计划
sakila数据库由 16 个表、7 个视图、3 个存储过程、3 个存储函数和 6 个触发器组成。这些表可以分为三组，客户数据、业务和库存。为了简洁起见，图中没有包括所有的列，大多数索引也没有显示。图 1-8 显示了表格、视图和存储程序的完整概览。
图 1-8
sakila数据库概述
包含客户相关数据的表格(加上员工和商店的地址)位于左上角的区域。左下角的区域包含与业务相关的数据，右上角的区域包含关于电影和库存的信息。右下角用于视图和存储的程序。
Tip
您可以通过在 MySQL Workbench 中打开安装中包含的sakila.mwb文件来查看整个图表(尽管格式不同)。这也是一个很好的例子，说明如何在 MySQL Workbench 中使用增强的实体关系(EER)图来记录您的模式。
由于对象的数量相对较多，所以在讨论模式时，将它们分成五组(每个表组、视图和存储例程)。第一组是客户相关数据，表格如图 1-9 所示。
图 1-9
sakila数据库中包含客户数据的表格
有四个表包含与客户相关的数据。customer 表是主表，地址信息存储在 address、city 和 country 表中。
客户和业务组之间存在外键，在业务组中，外键从客户表指向商店表。还有四个从业务组的表到地址和客户表的外键。业务组如图 1-10 所示。
图 1-10
sakila数据库中包含业务数据的表
业务表包含关于商店、员工、租金和付款的信息。商店和职员表有两个方向的外键，职员属于一个商店，而商店的经理是职员的一部分。租金和付款由员工处理，因此与商店间接相关，付款是为了租金。
表的业务组是与其它组关系最密切的组。staff 和 store 表有地址表的外键，而租赁和付款表引用客户。最后，租赁表有一个指向库存组中的库存表的外键。库存组的示意图如图 1-11 所示。
图 1-11
sakila数据库中包含库存数据的表格
inventory 组中的主表是 film 表，它包含关于商店提供的电影的元数据。此外，还有一个带有标题和描述的film_text表，带有全文索引。
电影与类别和演员表之间存在多对多的关系。最后，在业务组中有一个从库存表到商店表的外键。
这涵盖了sakila数据库中的所有表格，但也有一些如图 1-12 所示的视图。
图 1-12
sakila数据库中的视图
这些视图可以像报告一样使用，并且可以分为两类。film_list、nicer_but_slower_film_list和actor_info视图与存储在数据库中的电影相关。第二类包含与sales_by_store、sales_by_film_category、staff_list和customer_list视图中的商店相关的信息。
为了完善数据库，还有如图 1-13 所示的存储函数和过程。
图 1-13
存储在sakila数据库中的程序
film_in_stock()和film_not_in_stock()过程返回一个结果集，该结果集由给定电影和商店的库存 id 组成，基于电影是否有库存。找到的库存条目总数作为 out 参数返回。rewards_report()程序根据上个月的最低花费生成一份报告。
get_customer_balance()函数返回给定客户在给定日期的余额。剩下的两个函数检查一个库存 id 的状态，其中inventory_held_by_customer()返回当前租赁该商品的客户的客户 id(如果没有客户租赁该商品，则返回NULL)，如果您想检查给定的库存 id 是否有库存，可以使用inventory_in_stock()函数。
装置
您可以从 https://dev.mysql.com/doc/index-other.html 下载一个带有安装脚本的文件来安装sakila模式，就像安装world数据库一样。
下载的文件展开到一个包含三个文件的目录中，其中两个文件创建模式和数据，最后一个文件包含 MySQL Workbench 使用的格式的 ETL 图。
Note
sakila数据库也可以通过下载employees数据库获得；然而，本节和本书后面的例子使用了从 MySQL 主页下载的sakila数据库的副本。
这些文件是
-
sakila-data.sql: 填充表格所需的INSERT语句以及触发器定义。 -
sakila-schema.sql: 模式定义语句。 -
sakila.mwb**:**MySQL 工作台 ETL 图。这类似于图 1-7 所示，细节如图 1-8 至 1-12 所示。
通过首先获取sakila-schema.sql文件，然后获取sakila-data.sql文件来安装sakila数据库。例如，下面是使用 MySQL Shell:
MySQL [localhost+ ssl] SQL> \source sakila-schema.sql
MySQL [localhost+ ssl] SQL> \source sakila-data.sql
如果文件不在当前目录中，请添加文件的路径。
测试数据:雇员模式
employees数据库(在 MySQL 文档下载页面上称为雇员数据；GitHub 知识库的名字是test_db)最初是由王辅生和卡洛·扎尼奥洛创建的，是 MySQL 主页链接的最大的测试数据集。它提供了使用非分区表或对两个最大的表进行分区的选择。对于非分区版本，数据文件的总大小约为 180 MiB，而对于分区版本，约为 440 MiB。
计划
employees数据库由六个表和两个视图组成。您可以选择再安装两个视图、五个存储函数和两个存储过程。表格如图 1-14 所示。
图 1-14
employees数据库中的表格、视图和例程
按照今天的标准，它仍然是数据库中相对少量的数据，但是它足够大，您可以开始看到较低级别的争用，因此，它是第 18 章中用于导致信号量等待的模式。
装置
您可以下载一个包含安装所需文件的 ZIP 文件，也可以在 https://github.com/datacharmer/test_db 克隆 GitHub 库。在撰写本文时，只有一个名为 master 的分支。如果您已经下载了 ZIP 文件，它将解压到一个名为test_db-master的目录中。
有几个文件。在 MySQL 8 中与安装employees数据库相关的两个是employees.sql和employees_partitioned.sql。区别在于salaries和titles表是否被分区。这本书使用了非分区模式。(还有针对 MySQL 5.1 的employees_partitioned_5.1.sql，其中不支持employees_partitioned.sql中使用的分区方案。)
通过使用SOURCE命令获取.dump文件来加载数据，该命令仅在 MySQL Shell 8 . 0 . 19(由于一个错误，实际上是 8.0.20)和更高版本中受支持。转到源文件所在的目录，选择employees.sql或employees_partitioned.sql文件，这取决于您是否想要使用分区，例如
mysql> \source employees.sql
导入需要一点时间，并通过显示花费的时间来完成:
+---------------------+
| data_load_time_diff |
+---------------------+
| 00:02:50 |
+---------------------+
1 row in set (0.0085 sec)
或者，您可以通过获取objects.sql文件来加载一些额外的视图和存储的例程:
mysql> \source objects.sql
当您使用concurrency_book.generate.load()方法加载employees模式时，objects.sql文件总是包含在内。
现在，您已经准备好进入 MySQL 并发世界了。
摘要
本章开始了理解 MySQL 并发性的旅程，其中锁和事务是重要的主题。首先讨论了为什么需要锁以及它们存在于什么级别。然后讨论了事务必须包含在讨论中，因为一些锁在事务期间被持有，事务隔离级别影响锁的持续时间以及锁的数量。
本章的其余部分讨论了如何在本书中使用这些例子，并介绍了重现测试用例所需的三组测试数据。为了更容易加载数据和执行测试用例，还引入了 MySQL Shell 的concurrency_book.generate模块。
在下一章，我们将讨论如何监控锁。
二、监控锁和互斥锁
监控对于了解系统中出现瓶颈的位置至关重要。您需要使用监控来确定争用的来源，并验证您所做的更改是否减少了争用。
这一章和接下来的两章概述了性能模式中的锁和互斥体监控、InnoDB 事务监控和一般事务监控。本书的其余部分展示了如何使用这些监控资源来识别和调查争用的例子。特别是第13—18章在案例研究的讨论中广泛使用了监控。
在本章中，你将学习如何监控锁和互斥体。主要资源是首先介绍的性能模式。接下来，讨论sys模式中的现成报告。本章的后半部分涵盖了状态指标、InnoDB 锁监控和 InnoDB 互斥体监控。
Note
如果您还不知道各种锁和互斥锁是什么，请不要担心。稍后，您将通过本章中讨论的使用监控源的示例来了解这一点。
性能模式
性能模式包含除死锁之外的大多数可用锁信息的来源。您不仅可以直接使用性能模式中的锁信息；它还用于sys模式中两个与锁相关的视图。此外，您可以使用性能模式来研究低级同步对象，如互斥体。首先，将展示如何调查元数据和表锁。
元数据和表锁
元数据锁是最普通的高级锁，支持从全局读锁到低级锁(如访问控制列表(ACL ))的各种锁。使用包含用户级锁、元数据锁等信息的metadata_locks表来监控锁。要记录信息，必须启用wait/lock/metadata/sql/mdl性能模式工具(在 MySQL 8 中默认启用)。后面有一个例子展示了如何启用仪器。
metadata_locks表包含 11 列，汇总在表 2-1 中。
表 2-1
performance_schema.metadata_locks表
列名
|
描述
|
| --- | --- |
| OBJECT_TYPE | 持有的锁的种类，例如用于全局读锁的GLOBAL和用于表和视图的TABLE。附录 A 包括可能值的完整列表。 |
| OBJECT_SCHEMA | 锁定的对象所属的架构。 |
| OBJECT_NAME | 锁定对象的名称。 |
| COLUMN_NAME | 对于列级锁，是锁定列的列名。 |
| OBJECT_INSTANCE_BEGIN | 对象的内存地址。 |
| LOCK_TYPE | 锁访问级别，如共享、独占或意图。附录 A 包括可能值的完整列表。 |
| LOCK_DURATION | 锁保持多长时间。支持的值有STATEMENT、TRANSACTION和EXPLICIT。 |
| LOCK_STATUS | 锁的状态。除了授权和未决状态之外，它还可以显示锁请求超时、是受害者等。 |
| SOURCE | 源代码中请求锁的位置。 |
| OWNER_THREAD_ID | 请求锁的线程的性能架构线程 id。 |
| OWNER_EVENT_ID | 请求锁的事件的事件 id。 |
表格的主键是OBJECT_INSTANCE_BEGIN列。
清单 2-1 展示了一个获取表元数据锁并在metadata_locks表中查询它的例子。有些细节对你来说会有所不同。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 19 59 6
-- Connection 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
mysql> SELECT * FROM world.city WHERE ID = 130;
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.0005 sec)
mysql> SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE'
AND OBJECT_SCHEMA = 'world'
AND OBJECT_NAME = 'city'
AND OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
OBJECT_NAME: city
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2639965404080
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6162
OWNER_THREAD_ID: 59
OWNER_EVENT_ID: 10
1 row in set (0.0006 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.0006 sec)
Listing 2-1Example use of the metadata_locks table
这里你可以看到它是world.city表上的一个表级锁。这是一个共享的读锁，因此其他连接可以同时获得同一个锁。
如果您想找出一个连接等待其锁请求被批准的原因，您需要查询metadata_locks表中的一行，其中OBJECT_TYPE、OBJECT_SCHEMA和OBJECT_NAME与挂起的锁相同，并且LOCK_STATUS是GRANTED。也就是说，要找到所有挂起锁的情况以及阻塞它们的原因，您需要一个自连接表的查询:
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
w.OWNER_THREAD_ID AS WAITING_THREAD_ID,
b.OWNER_THREAD_ID AS BLOCKING_THREAD_ID
FROM performance_schema.metadata_locks w
INNER JOIN performance_schema.metadata_locks b
USING (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)
WHERE w.LOCK_STATUS = 'PENDING'
AND b.LOCK_STATUS = 'GRANTED';
您可以选择连接其他性能模式表，比如events_statements_current，以获得更多关于锁等待中所涉及的连接的信息。或者，正如后面将要讨论的，对于表元数据锁，您可以使用sys.schema_table_lock_waits视图。
一个不太常用的表是table_handles，它保存关于打开的表句柄的信息，包括哪些表锁当前被锁定。必须启用wait/lock/table/sql/handler性能模式仪器才能记录数据(这是默认设置)。可用的信息类似于metadata_locks表的信息，清单 2-2 展示了一个在world.city表上显式读锁的例子。有些细节对你来说会有所不同。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 21 61 6
-- Connection 1
mysql> LOCK TABLE world.city READ;
Query OK, 0 rows affected (0.0004 sec)
mysql> SELECT *
FROM performance_schema.table_handles
WHERE OBJECT_SCHEMA = 'world'
AND OBJECT_NAME = 'city'
AND OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
OBJECT_NAME: city
OBJECT_INSTANCE_BEGIN: 2639971828776
OWNER_THREAD_ID: 61
OWNER_EVENT_ID: 8
INTERNAL_LOCK: NULL
EXTERNAL_LOCK: READ EXTERNAL
1 row in set (0.0013 sec)
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.0004 sec)
Listing 2-2Example of using the table_handles table
INTERNAL_LOCK列包含 SQL 级别的锁信息，例如非 InnoDB 表上的显式表锁，而EXTERNAL_LOCK包含存储引擎级别的锁信息，包括所有表的显式表锁。
与metadata_locks表不同，您不能使用table_handles表来调查锁争用(但是metadata_locks表也包括显式的表锁，就像这个例子一样，所以您可以使用它)。
metadata_locks和table_handles表涉及最高级别的锁。锁粒度的下一步是拥有自己的表的数据锁。
数据锁
数据锁位于元数据锁和同步对象之间的中间级别。数据锁的特殊之处在于，它有很多种锁类型，比如记录锁、间隙锁、插入意图锁等。它们以复杂的方式相互作用，如第 7 章所述。这使得数据锁的监控表特别有用。
数据锁定信息分为两个表:
-
data_locks: 该表包含表的细节，并记录 InnoDB 级别的锁。它显示当前持有的或待定的所有锁。 -
data_lock_waits: 与data_locks表一样，它显示了与 InnoDB 相关的锁，但是只显示那些等待被授予关于哪个线程正在阻塞请求的信息的锁。
您将经常组合使用这些工具来查找有关锁等待的信息。
MySQL 8 见证了锁监控表工作方式的改变。在 MySQL 5.7 和更早的版本中，信息在信息模式中的两个特定于 InnoDB 的视图中可用，INNODB_LOCKS和INNODB_LOCK_WAITS。主要区别在于，性能模式表被创建为与存储引擎无关，并且关于所有锁的信息总是可用的，而在 MySQL 5.7 和更早版本中，仅公开关于锁等待中涉及的锁的信息。所有的锁总是可用于研究，这使得 MySQL 8 表对于了解锁更加有用。
data_locks表是包含每个锁的详细信息的主表。该表有 15 列，如表 2-2 所述。
表 2-2
performance_schema.data_locks表
列名
|
描述
|
| --- | --- |
| ENGINE | 数据的存储引擎。对于 MySQL 服务器，这将始终是 InnoDB。 |
| ENGINE_LOCK_ID | 存储引擎使用的锁的内部 id。您不应该依赖具有特定格式的 id。 |
| ENGINE_TRANSACTION_ID | 特定于存储引擎的事务 id。对于 InnoDB，您可以使用这个 id 连接到information_schema.INNODB_TRX视图中的trx_id列。您不应该依赖具有特定格式的 id，该 id 可能会在事务的持续时间内发生变化。 |
| THREAD_ID | 发出锁定请求的线程的性能架构线程 id。 |
| EVENT_ID | 发出锁定请求的事件的性能架构事件 id。您可以使用这个 id 来连接几个events_%表，以找到关于是什么触发了锁请求的更多信息。 |
| OBJECT_SCHEMA | 作为锁定请求主题的对象所在的架构。 |
| OBJECT_NAME | 作为锁定请求主题的对象的名称。 |
| PARTITION_NAME | 对于涉及分区的锁，是分区的名称。 |
| SUBPARTITION_NAME | 对于涉及子分区的锁，是子分区的名称。 |
| INDEX_NAME | 对于涉及索引的锁，是索引的名称。因为所有东西都是 InnoDB 的索引，所以索引名总是为 InnoDB 表上的记录级锁设置的。如果行被锁定，值将是PRIMARY或GEN_CLUST_INDEX，这取决于您是否有一个显式主键或表是否使用了隐藏聚集索引。 |
| OBJECT_INSTANCE_BEGIN | 锁定请求的内存地址。 |
| LOCK_TYPE | 锁定请求的级别。对于 InnoDB，可能的值是TABLE和RECORD。 |
| LOCK_MODE | 使用的锁定模式。这包括它是共享锁还是排他锁，以及锁的更详细信息，例如，REC_NOT_GAP表示记录锁，但没有间隙锁。 |
| LOCK_STATUS | 锁是待定(WAITING)还是已被授予(GRANTED)。 |
| LOCK_DATA | 关于被锁定数据的信息。例如，这可以是锁定索引记录的索引值。 |
表格的主键是(ENGINE_LOCK_ID，ENGINE)。
清单 2-3 显示了获取两个锁并查询data_locks表的例子。id 和内存地址等信息会因您而异。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 23 64 6
-- Connection 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
mysql> SELECT *
FROM world.city
WHERE ID = 130
FOR SHARE;
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.0068 sec)
mysql> SELECT *
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2639727636640:3165:2639690712184
ENGINE_TRANSACTION_ID: 284114704347296
THREAD_ID: 64
EVENT_ID: 10
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2639690712184
LOCK_TYPE: TABLE
LOCK_MODE: IS
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2\. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2639727636640:1926:6:131:2639690709400
ENGINE_TRANSACTION_ID: 284114704347296
THREAD_ID: 64
EVENT_ID: 10
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2639690709400
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 130
2 rows in set (0.0018 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.0007 sec)
Listing 2-3Using the data_locks table
在本例中，查询获得了对world.city表的插入意图(IS)锁和一个共享(S)记录，但没有获得值为 130 的主键的间隙锁(REC NOT_GAP)。
data_lock_waits表更简单，因为它只包括当前锁争用情况的基本信息，如表 2-3 所示。
表 2-3
performance_schema.data_lock_waits表
列名
|
描述
|
| --- | --- |
| ENGINE | 发生锁争用的存储引擎。 |
| REQUESTING_ENGINE_LOCK_ID | 挂起锁的ENGINE_LOCK_ID。 |
| REQUESTING_ENGINE_TRANSACTION_ID | 挂起锁的ENGINE_TRANSACTION_ID。 |
| REQUESTING_THREAD_ID | 挂起锁的THREAD_ID。 |
| REQUESTING_EVENT_ID | 挂起锁的EVENT_ID。 |
| REQUESTING_OBJECT_INSTANCE_BEGIN | 挂起锁的OBJECT_INSTANCE_BEGIN。 |
| BLOCKING_ENGINE_LOCK_ID | 闭锁锁的ENGINE_LOCK_ID。 |
| BLOCKING_ENGINE_TRANSACTION_ID | 闭锁锁的ENGINE_TRANSACTION_ID。 |
| BLOCKING_THREAD_ID | 闭锁锁的THREAD_ID。 |
| BLOCKING_EVENT_ID | 闭锁锁的EVENT_ID。 |
| BLOCKING_OBJECT_INSTANCE_BEGIN | 闭锁锁的OBJECT_INSTANCE_BEGIN。 |
该表没有主键。该表的主要目的是提供一种简单的方法来确定锁争用中涉及的挂起和阻塞锁请求。然后，您可以使用REQUESTING_ENGINE_TRANSACTION_ID和BLOCKING_ENGINE_TRANSACTION_ID列连接到data_locks表以及其他表，以获得更多信息。一个很好的例子就是sys.innodb_lock_waits视图。
到目前为止，已经讨论过的性能模式表是针对锁的，这些锁是执行语句的直接结果。在高并发性的情况下，还有一些较低级别的同步等待需要监控。
同步等待
同步等待是最难监控的，原因有几个。它们发生得非常频繁，通常持续时间很短，监控它们的开销很大。默认情况下，同步等待的检测也是不启用的。
同步等待分为五类:
-
cond: 线程间使用的条件信号。 -
mutex: 保护代码部分或其他资源的互斥点。 -
prlock: 一个优先级读/写锁。 -
rwlock: 读/写锁，用于限制对特定变量的并发访问，例如，用于改变gtid_mode系统变量。 -
sxlock: 共享-独占读/写锁。例如，目前只有 InnoDB 使用它来提高 B 树搜索的可伸缩性。
同步等待的仪器名称以wait/synch/开头，后面是类别名称、等待所属的区域(如sql或innodb)以及等待的名称。例如，保护 InnoDB 双写缓冲区的互斥体名为wait/synch/mutex/innodb/dblwr_mutex。
通过为您想要监控的仪器设置performance_schema.setup_instruments表中的ENABLED和可选的TIMED列，您可以启用同步等待的仪器。此外，您需要启用events_waits_current和可选的performance_schema.setup_consumers中的events_waits_history和/或events_waits_history_long。例如，监控 InnoDB 双写缓冲区上的互斥锁
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES',
TIMED = 'YES'
WHERE NAME = 'wait/synch/mutex/innodb/dblwr_mutex';
Query OK, 1 row affected (0.0011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_waits_current';
Query OK, 1 row affected (0.0005 sec)
Rows matched: 1 Changed: 1 Warnings: 0
一般来说，最好在配置文件中启用对同步工具的监控，以确保它们在 MySQL 启动时就已正确设置:
[mysqld]
performance_schema_instrument = wait/synch/mutex/innodb/dblwr_mutex=ON
performance_schema_consumer_events_waits_current = ON
然后重启 MySQL。
Caution
在生产系统上启用同步等待和相应消费者的工具时要非常小心。这样做可能会导致足够高的开销，以至于实际上会出现停机。启用的越多，开销就越高，监控干扰测量的可能性就越大，因此结论是错误的。
现在，您可以使用events_waits_%表之一来监控等待:
-
events_waits_current: 每个现有线程当前正在进行或上次完成的等待事件。这需要启用events_waits_current消费者。 -
events_waits_history: 每个现有线程的最后十个(performance_schema_events_waits_history_size选项)等待事件。这要求除了events_waits_current消费者之外，还要启用events_waits_history消费者。 -
events_waits_history_long: 全局最后 10，000 个(performance_schema_events_waits_history_long_size选项)事件，包括不再存在的线程。这要求除了events_waits_current消费者之外，还要启用events_waits_history_long消费者。 -
events_waits_summary_by_account_by_event_name: 由帐户的用户名和主机名(在性能模式中也称为参与者)分组的等待事件。 -
events_waits_summary_by_host_by_event_name: 按触发事件的账户主机名和事件名称分组的等待事件。 -
events_waits_summary_by_instance: 根据事件名称以及对象的内存地址(OBJECT_INSTANCE_BEGIN)分组的等待事件。这对于具有多个实例的事件非常有用，可以监控等待是否在实例之间均匀分布。一个例子是表缓存互斥锁(wait/synch/mutex/sql/LOCK_table_cache)，每个表缓存实例(table_open_cache_instances)有一个对象。 -
events_waits_summary_by_thread_by_event_name: 按线程 id 和事件名分组的当前存在线程的等待事件。 -
events_waits_summary_by_user_by_event_name: 按触发事件的账户用户名和事件名称分组的等待事件。 -
events_waits_summary_global_by_event_name: 按事件名称分组的等待事件。此表有助于了解等待给定类型的事件所花费的时间。
考虑到同步等待通常持续的时间有多短以及遇到的频率有多高，汇总表通常对使用性能模式研究等待最有用。也就是说，由于相关的等待工具在默认情况下是不启用的，并且在监控它们时开销相对较高，所以通常 InnoDB monitor 的信号量部分或本章后面描述的SHOW ENGINE INNODB MUTEX语句用于 InnoDB 互斥体和信号量。例外情况是当您想要调查特定的争用问题时。
使用性能模式进行锁分析的另一个有用方法是查询语句遇到的错误。
语句和错误表
性能模式包括几个表，可用于调查遇到的错误。由于由于超时或死锁而导致的获取锁的失败会触发错误，因此您可以查询与锁相关的错误，以确定哪些语句、帐户等受锁争用的影响最大。
在单个语句级别，您可以使用events_statements_current、events_statements_history和events_statements_history_long来查看是否发生了任何错误或特定错误。默认情况下，前两个表是启用的，而events_statements_history_long表要求您启用events_statements_history_long消费者。清单 2-4 展示了一个锁等待超时的例子，以及它如何出现在events_statements_history表中。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 63 179 6
-- 2 64 180 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SET SESSION innodb_lock_wait_timeout = 1;
Query OK, 0 rows affected (0.0003 sec)
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction
Connection 2> SELECT thread_id, event_id,
FORMAT_PICO_TIME(lock_time) AS lock_time,
sys.format_statement(SQL_TEXT) AS statement,
digest, mysql_errno,
returned_sqlstate, message_text, errors
FROM performance_schema.events_statements_history
WHERE thread_id = PS_CURRENT_THREAD_ID()
AND mysql_errno > 0\G
*************************** 1\. row ***************************
thread_id: 180
event_id: 10
lock_time: 271.00 us
statement: UPDATE world.city SET Popul ... Population + 1 WHERE ID = 130
digest: 3e9795ad6fc0f4e3a4b4e99f33fbab2dc7b40d0761a8adbc60abfab02326108d
mysql_errno: 1205
returned_sqlstate: HY000
message_text: Lock wait timeout exceeded; try restarting transaction
errors: 1
1 row in set (0.0016 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0472 sec)
-- Connection 2
Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.0003 sec)
Listing 2-4Example of a lock error in the statement tables
这个例子中有一些值得注意的地方。首先，锁时间只有 271 微秒，尽管在锁等待超时发生之前花了整整一秒钟。也就是说，在 InnoDB 中等待记录锁不会增加性能模式报告的锁时间，因此您不能使用它来调查记录级锁争用。
第二件事是，mysql_errno、returned_sqlstate和message_text包含了返回给客户端的相同的错误信息，这使得它对于查询很有用，因为在本例中也是这样做的。第三，errors列包含遇到的错误数量的计数。虽然计数并不说明错误的性质，但它很有用，因为与包含错误细节的列不同，错误计数器也出现在语句摘要表中，因此您可以使用它来查找哪些语句遇到了任何类型的错误。
Tip
记录应用中遇到的错误会很有用。例如，您可以使用 Splunk 之类的服务来分析应用日志，以生成显示遇到了哪些错误以及这些错误何时会成为问题的报告。
在这种情况下，一组特别重要的汇总表由汇总错误的表组成。有五个这样的表，分别按帐户、主机、线程、用户和全局分组:
mysql> SHOW TABLES FROM performance_schema LIKE '%error%';
+-------------------------------------------+
| Tables_in_performance_schema (%error%) |
+-------------------------------------------+
| events_errors_summary_by_account_by_error |
| events_errors_summary_by_host_by_error |
| events_errors_summary_by_thread_by_error |
| events_errors_summary_by_user_by_error |
| events_errors_summary_global_by_error |
+-------------------------------------------+
5 rows in set (0.0012 sec)
例如，检索锁等待超时和死锁的统计信息
mysql> SELECT *
FROM performance_schema.events_errors_summary_global_by_error
WHERE error_name IN ('ER_LOCK_WAIT_TIMEOUT',
'ER_LOCK_DEADLOCK')\G
*************************** 1\. row ***************************
ERROR_NUMBER: 1205
ERROR_NAME: ER_LOCK_WAIT_TIMEOUT
SQL_STATE: HY000
SUM_ERROR_RAISED: 4
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2020-06-28 11:33:10
LAST_SEEN: 2020-06-28 11:49:30
*************************** 2\. row ***************************
ERROR_NUMBER: 1213
ERROR_NAME: ER_LOCK_DEADLOCK
SQL_STATE: 40001
SUM_ERROR_RAISED: 3
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2020-06-27 12:06:38
LAST_SEEN: 2020-06-27 12:54:27
2 rows in set (0.0048 sec)
虽然这不能帮助您确定哪些语句遇到了错误，但它可以帮助您监控遇到错误的频率，并以此方式确定锁错误是否变得更加频繁。
Tip
从 MySQL 启动开始，所有已知的错误都会填充到events_errors_summary_global_by_error中，即使还没有遇到错误。因此，您可以随时安全地查询特定的错误，包括使用该表从名称中查找错误号。
性能模式表中的数据是原始数据，可以是单个事件，也可以是聚合数据。通常，当您调查锁问题或监控锁问题时，更感兴趣的是确定是否有任何锁等待，或者获取花费大部分时间的等待事件的报告。对于这些信息，您需要使用sys模式。
sys 架构
sys模式可以被认为是视图的集合，这些视图作为关于性能模式和信息模式以及各种实用函数和过程的报告。对于这个讨论，重点是两个视图，它们获取性能模式表中的信息并返回锁对，其中一个锁由于另一个锁而不能被授予。因此，它们显示了锁等待的问题所在。这两个视图是innodb_lock_waits和schema_table_lock_waits。
innodb_lock_waits视图使用性能模式中的data_locks和data_lock_waits视图返回 InnoDB 记录锁的所有锁等待情况。它显示诸如连接试图获取什么锁以及涉及哪些连接和查询之类的信息。如果您需要没有格式的信息，视图也以x$innodb_lock_waits的形式存在。
schema_table_lock_waits视图以类似的方式工作，但是使用metadata_locks表返回与模式对象相关的锁等待。该信息在x$schema_table_lock_waits视图中也是无格式的。
Tip
还存在一些视图，其中 x的视图相同，只是所有数据都是无格式的。这使得数据更适合处理信息的脚本和程序。
对于争用的高级视图，您还可以使用状态计数器和 InnoDB 指标。
状态计数器和 InnoDB 指标
有几个状态计数器和 InnoDB 指标提供关于锁定的信息。这些主要用于全局(实例)级别，对于检测锁问题的总体增加非常有用。
查询数据
状态计数器和 InnoDB 指标有两个来源。全局状态计数器可以在performance_schema.global_status表中找到，或者通过SHOW GLOBAL STATUS语句找到。InnoDB 指标可以在information_schema.INNODB_METRICS视图中找到。
InnoDB 指标类似于全局状态变量，可以提供一些关于 InnoDB 状态的有价值的信息。NAME列可用于按名称查询指标。在撰写本文时，有 313 个可见指标，其中 74 个默认启用。还有一个隐藏的指标是latch指标，它控制是否收集互斥等待统计数据。度量被分组到子系统中(SUBSYSTEM列)，对于每个度量，在COMMENT列中有一个度量测量什么的描述，以及度量的类型(计数器、值等)。)可以在TYPE一栏看到。
一起监控所有这些指标的一个好方法是使用sys.metrics视图。清单 2-5 展示了一个检索指标的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 27 69 6
-- Connection 1
mysql> SELECT Variable_name,
Variable_value AS Value,
Enabled
FROM sys.metrics
WHERE Variable_name LIKE 'innodb_row_lock%'
OR Variable_name LIKE 'Table_locks%'
OR Variable_name LIKE 'innodb_rwlock_%'
OR Type = 'InnoDB Metrics - lock';
+-------------------------------+--------+---------+
| Variable_name | Value | Enabled |
+-------------------------------+--------+---------+
| innodb_row_lock_current_waits | 0 | YES |
| innodb_row_lock_time | 2163 | YES |
| innodb_row_lock_time_avg | 721 | YES |
| innodb_row_lock_time_max | 2000 | YES |
| innodb_row_lock_waits | 3 | YES |
| table_locks_immediate | 330 | YES |
| table_locks_waited | 0 | YES |
| lock_deadlock_false_positives | 0 | YES |
| lock_deadlock_rounds | 37214 | YES |
| lock_deadlocks | 1 | YES |
| lock_rec_grant_attempts | 1 | YES |
| lock_rec_lock_created | 0 | NO |
| lock_rec_lock_removed | 0 | NO |
| lock_rec_lock_requests | 0 | NO |
| lock_rec_lock_waits | 0 | NO |
| lock_rec_locks | 0 | NO |
| lock_rec_release_attempts | 24317 | YES |
| lock_row_lock_current_waits | 0 | YES |
| lock_schedule_refreshes | 37214 | YES |
| lock_table_lock_created | 0 | NO |
| lock_table_lock_removed | 0 | NO |
| lock_table_lock_waits | 0 | NO |
| lock_table_locks | 0 | NO |
| lock_threads_waiting | 0 | YES |
| lock_timeouts | 1 | YES |
| innodb_rwlock_s_os_waits | 12248 | YES |
| innodb_rwlock_s_spin_rounds | 19299 | YES |
| innodb_rwlock_s_spin_waits | 6811 | YES |
| innodb_rwlock_sx_os_waits | 171 | YES |
| innodb_rwlock_sx_spin_rounds | 5239 | YES |
| innodb_rwlock_sx_spin_waits | 182 | YES |
| innodb_rwlock_x_os_waits | 26283 | YES |
| innodb_rwlock_x_spin_rounds | 774745 | YES |
| innodb_rwlock_x_spin_waits | 12666 | YES |
+-------------------------------+--------+---------+
34 rows in set (0.0174 sec)
Listing 2-5Lock metrics
innodb_row_lock_%、lock_deadlocks和lock_timeouts度量是最有趣的。行锁指标显示了当前有多少锁正在等待，并统计了等待获取 InnoDB 记录锁所花费的时间(毫秒)。lock_deadlocks和lock_timeouts指标分别显示遇到的死锁和锁等待超时的数量。
如果遇到 InnoDB 互斥或信号量争用，那么innodb_rwlock_%度量对于监控等待发生的速率以及等待花费了多少轮是有用的。
正如您所看到的，并非所有的指标都是默认启用的(这些都是 InnoDB 指标)，所以让我们研究一下如何启用和禁用来自INNODB_METRICS视图的指标。
配置 InnoDB 指标
可以配置 InnoDB 指标，因此您可以选择启用哪些指标，并且可以重置统计数据。您可以使用全局系统变量启用、禁用和重置指标:
-
innodb_monitor_disable: 禁用一个或多个度量。 -
innodb_monitor_enable: 启用一个或多个指标。 -
innodb_monitor_reset: 重置一个或多个指标的计数器。 -
innodb_monitor_reset_all: 重置所有统计信息，包括一个或多个度量的计数器、最小值和最大值。
可以根据需要打开和关闭指标，在INNODB_METRICS视图的STATUS列中找到当前状态。您可以指定指标的名称或子系统的名称，在前面加上module_作为innodb_monitor_enable或innodb_monitor_disable变量的值，并且您可以使用%作为通配符。值all作为一个特殊值影响所有指标。
Note
当您指定一个模块时，只有当没有与该模块匹配的度量时，它才会按预期工作。不能指定模块的例子有module_cpu、module_page_track和module_dblwr。
清单 2-6 展示了一个启用和使用所有匹配icp%的指标的例子(恰好是icp–索引条件下推–子系统中的指标)。查询完指标后，使用子系统作为参数再次禁用它们。COUNT的值取决于查询时的工作量。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 32 74 6
-- Connection 1
mysql> SET GLOBAL innodb_monitor_enable = 'icp%';
Query OK, 0 rows affected (0.0003 sec)
mysql> SELECT NAME, SUBSYSTEM, COUNT, MIN_COUNT,
MAX_COUNT, AVG_COUNT,
STATUS, COMMENT
FROM information_schema.INNODB_METRICS
WHERE SUBSYSTEM = 'icp'\G
*************************** 1\. row ***************************
NAME: icp_attempts
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Number of attempts for index push-down condition checks
*************************** 2\. row ***************************
NAME: icp_no_match
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Index push-down condition does not match
*************************** 3\. row ***************************
NAME: icp_out_of_range
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Index push-down condition out of range
*************************** 4\. row ***************************
NAME: icp_match
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Index push-down condition matches
4 rows in set (0.0011 sec)
mysql> SET GLOBAL innodb_monitor_disable = 'module_icp';
Query OK, 0 rows affected (0.0004 sec)
Listing 2-6Using the INNODB_METRICS view
首先，使用innodb_monitor_enable变量启用指标；然后检索这些值。除了显示的值，还有一组带_RESET后缀的列，当您设置innodb_monitor_reset(仅计数器)或innodb_monitor_reset_all系统变量时，这些列会被重置。最后，指标再次被禁用。
Caution
这些指标有不同的开销，因此建议您在生产中启用指标之前先测试您的工作负载。
InnoDB 锁监控器和死锁记录
InnoDB 很久以前就有了自己的锁监控器，锁信息在 InnoDB 监控器输出中返回。默认情况下，InnoDB 监控器包含关于最新死锁以及锁等待中涉及的锁的信息。通过启用innodb_status_output_locks选项(默认禁用)，将列出所有锁；这类似于性能模式data_locks表中的内容。
为了演示死锁和事务信息，您可以使用清单 2-7 中的步骤创建一个死锁。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 19 66 6
-- 2 20 67 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805;
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
-- Connection 1
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805;
2020-06-27 12:54:26.833760 1 [ERROR] mysqlsh.DBError ...
ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction
-- Connection 2
Query OK, 1 row affected (0.1013 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Listing 2-7An example of creating a deadlock
使用SHOW ENGINE INNODB STATUS语句生成 InnoDB 锁监控器输出。清单 2-8 显示了在执行清单 2-7 中的语句后，启用所有锁信息并生成监控器输出的示例。(清单 2-8 中使用的语句作为清单 2-7 工作负载的调查包含在concurrency_book Python 模块中。)完整的 InnoDB monitor 输出也可以从本书的 GitHub 资源库的listing_2_8.txt文件中获得。
-- Investigation #1
-- Connection 3
Connection 3> SET GLOBAL innodb_status_output_locks = ON;
Query OK, 0 rows affected (0.0005 sec)
-- Investigation #3
Connection 3> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-06-27 12:54:29 0x7f00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2532 srv_active, 0 srv_shutdown, 1224 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7750
OS WAIT ARRAY INFO: signal count 6744
RW-shared spins 3033, rounds 5292, OS waits 2261
RW-excl spins 1600, rounds 25565, OS waits 1082
RW-sx spins 2167, rounds 61634, OS waits 1874
Spin rounds per wait: 1.74 RW-shared, 15.98 RW-excl, 28.44 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-06-27 12:54:26 0x862c
*** (1) TRANSACTION:
TRANSACTION 296726, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 20, OS thread handle 29332, query id 56150 localhost ::1 root updating
UPDATE world.city
SET Population = Population + 1
WHERE ID = 130
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 259 page no 34 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap
Record lock, heap no 66 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000edd; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f2949; asc _)I;;
3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco ; (total 35 bytes);
4: len 3; hex 555341; asc USA;;
5: len 20; hex 43616c69666f726e696120202020202020202020; asc California ;;
6: len 4; hex 800bda1e; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 259 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000082; asc ;;
1: len 6; hex 000000048715; asc ;;
2: len 7; hex 01000000d81fcd; asc ;;
3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes);
4: len 3; hex 415553; asc AUS;;
5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;;
6: len 4; hex 8031fdb0; asc 1 ;;
*** (2) TRANSACTION:
TRANSACTION 296725, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 6576, query id 56151 localhost ::1 root updating
UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 259 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 296725 lock_mode X locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000082; asc ;;
1: len 6; hex 000000048715; asc ;;
2: len 7; hex 01000000d81fcd; asc ;;
3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes);
4: len 3; hex 415553; asc AUS;;
5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;;
6: len 4; hex 8031fdb0; asc 1 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 259 page no 34 n bits 248 index PRIMARY of table `world`.`city` trx id 296725 lock_mode X locks rec but not gap waiting
Record lock, heap no 66 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000edd; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f2949; asc _)I;;
3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco ; (total 35 bytes);
4: len 3; hex 555341; asc USA;;
5: len 20; hex 43616c69666f726e696120202020202020202020; asc California ;;
6: len 4; hex 800bda1e; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 296728
Purge done for trx's n:o < 296728 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283598406541472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406540640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406539808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406538976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 296726, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 20, OS thread handle 29332, query id 56150 localhost ::1 root
TABLE LOCK table `world`.`city` trx id 296726 lock mode IX
RECORD LOCKS space id 259 page no 34 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap
Record lock, heap no 66 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000edd; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f2949; asc _)I;;
3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco ; (total 35 bytes);
4: len 3; hex 555341; asc USA;;
5: len 20; hex 43616c69666f726e696120202020202020202020; asc California ;;
6: len 4; hex 800bda1e; asc ;;
RECORD LOCKS space id 259 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000082; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f296c; asc _)l;;
3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes);
4: len 3; hex 415553; asc AUS;;
5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;;
6: len 4; hex 8031fdb0; asc 1 ;;
...
-- Investigation #2
Connection 3> SET GLOBAL innodb_status_output_locks = OFF;
Query OK, 0 rows affected (0.0005 sec)
Listing 2-8The InnoDB monitor output
附录 A 包括报告各部分的概述。
靠近顶部的部分是LATEST DETECTED DEADLOCK部分，它包括最近一次死锁所涉及的事务和锁的详细信息以及它发生的时间。如果自 MySQL 最后一次重启以来没有发生死锁，则省略这一节。第 16 章包括一个调查死锁的例子。
Note
InnoDB 监控器输出中的 deadlock 部分仅包含涉及 InnoDB 记录锁的死锁信息。对于涉及非 InnoDB 锁(如用户级锁)的死锁，没有等效的信息。
输出再往下一点，是列出 InnoDB 事务的部分TRANSACTIONS。请注意，不持有任何锁的事务(例如，纯SELECT查询)不包括在内。在本例中，world.city表上有一个意向排他锁，主键等于 3805(第一个字段的记录锁信息中的 80000edd 表示值为 0xedd 的行，与十进制表示法中的 3805 相同)和 130 (80000082)的行上有排他锁。
Tip
现在，InnoDB 监控器输出中的锁信息最好从performance_schema.data_locks和performance_schema.data_lock_waits表中获得。然而，死锁信息仍然非常有用。
通过启用innodb_status_output选项，您可以请求每隔 15 秒将监控器输出转储到 stderr。请注意，输出非常大，所以如果启用它，请做好错误日志快速增长的准备。InnoDB monitor 输出也很容易隐藏关于更严重问题的消息。InnoDB 还支持在某些情况下自动将监控器输出输出到错误日志中，比如当 InnoDB 很难在缓冲池中找到空闲块或者有长时间的信号量等待时。
如果您想确保记录所有死锁，您可以启用innodb_print_all_deadlocks选项。这导致每次发生死锁时，InnoDB monitor 输出中的死锁信息都会打印到错误日志中。如果您需要调查死锁，这可能是有用的，但是建议您仅在需要时启用它，以避免错误日志变得非常大并可能隐藏其他问题。
Caution
如果启用 InnoDB 监控器的常规输出或关于所有死锁的信息，请小心。这些信息很容易隐藏错误日志中记录的重要消息。
InnoDB monitor 输出的顶部包含关于信号量等待的信息，这是最后要讨论的监控类别。
InnoDB 互斥和信号量
InnoDB 使用互斥对象(通常称为互斥锁)和信号量来保护代码路径，例如，在更新缓冲池时避免竞争情况。在 MySQL 中有三种资源可用于监控互斥体，其中两种已经遇到过。最通用的工具是性能模式中的同步等待；但是，它们在默认情况下是不启用的，启用后会导致性能问题。本节重点介绍 InnoDB 特有的另外两个资源。
Note
在 InnoDB 监控中，互斥体和信号量之间没有明显的区别。
如前一节所述，InnoDB monitor 输出包含一个信号量部分，该部分显示一些常规统计信息以及当前正在等待的信号量。清单 2-9 显示了正在等待的信号量部分的一个例子。(按需生成信号量等待并不简单，所以没有包括复制步骤。参见第 18 章，了解可能导致信号量等待的工作负载示例。)
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 831
--Thread 28544 has waited at buf0buf.cc line 4637 for 0 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 10676 has waited at buf0flu.cc line 1639 for 1 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 10900 has waited at buf0lru.cc line 1051 for 0 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 28128 has waited at buf0buf.cc line 2797 for 1 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 33584 has waited at buf0buf.cc line 2945 for 0 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
OS WAIT ARRAY INFO: signal count 207
RW-shared spins 51, rounds 86, OS waits 35
RW-excl spins 39, rounds 993, OS waits 35
RW-sx spins 30, rounds 862, OS waits 25
Spin rounds per wait: 1.69 RW-shared, 25.46 RW-excl, 28.73 RW-sx
Listing 2-9The InnoDB monitor semaphores section
在这种情况下，第一次等待是在第buf0buf.cc行 4637，这是指请求互斥锁的源代码文件名和行号。行号取决于你使用的版本号，编译器/平台甚至可以让行号变一个。buf0buf.cc指的是 MySQL 8.0.21 中第 4637 行左右包含以下代码(行号是每行的前缀):
4577 /** Inits a page for read to the buffer buf_pool. If the page is
4578 (1) already in buf_pool, or
4579 (2) if we specify to read only ibuf pages and the page is not an ibuf page, or
4580 (3) if the space is deleted or being deleted,
4581 then this function does nothing.
4582 Sets the io_fix flag to BUF_IO_READ and sets a non-recursive exclusive lock
4583 on the buffer frame. The io-handler must take care that the flag is cleared
4584 and the lock released later.
4585 @param[out] err DB_SUCCESS or DB_TABLESPACE_DELETED
4586 @param[in] mode BUF_READ_IBUF_PAGES_ONLY, ...
4587 @param[in] page_id page id
4588 @param[in] page_size page size
4589 @param[in] unzip TRUE=request uncompressed page
4590 @return pointer to the block or NULL */
4591 buf_page_t *buf_page_init_for_read(dberr_t *err, ulint mode,
4592 const page_id_t &page_id,
4593 const page_size_t &page_size, ibool unzip) {
...
4637 mutex_enter(&buf_pool->LRU_list_mutex);
...
该函数试图将一个页面读入缓冲池，并在第 4637 行请求缓冲池的 LRU 列表上的互斥锁。这个互斥体是在buf0buf.cc:1228中创建的(也可以从信号量部分看到)。所有等待都是为了同一个互斥体，但是在源代码的不同部分。因此，这意味着存在维护 InnoDB 缓冲池的最近最少使用列表的争用。(本例中的等待是在对一个将近 2 GiB 的大表执行并发查询时通过使用innodb_buffer_pool_size = 5M创建的。)
因此，在研究信号量等待时，通常有必要参考源代码。也就是说，文件名很好地暗示了争用发生在代码的哪个部分，例如，buf0buf.cc与缓冲池相关，而buf0flu.cc与缓冲池刷新算法相关。
信号量部分对于查看正在进行的等待很有用，但是在监控一段时间的情况下用处不大。为此，InnoDB 互斥监控器是一个更好的选择。您可以使用SHOW ENGINE INNODB MUTEX语句访问互斥监控器:
mysql> SHOW ENGINE INNODB MUTEX;
+--------+------------------------------+------------+
| Type | Name | Status |
+--------+------------------------------+------------+
| InnoDB | rwlock: dict0dict.cc:2455 | waits=748 |
| InnoDB | rwlock: dict0dict.cc:2455 | waits=171 |
| InnoDB | rwlock: fil0fil.cc:3206 | waits=38 |
| InnoDB | rwlock: sync0sharded_rw.h:72 | waits=1 |
| InnoDB | rwlock: sync0sharded_rw.h:72 | waits=1 |
| InnoDB | rwlock: sync0sharded_rw.h:72 | waits=1 |
| InnoDB | sum rwlock: buf0buf.cc:778 | waits=2436 |
+--------+------------------------------+------------+
7 rows in set (0.0111 sec)
文件名和行号指的是创建互斥体的位置。互斥体监控器并不是 MySQL 中最用户友好的工具，因为每个互斥体可能会出现多次，并且在不解析输出的情况下无法对等待进行求和。但是，默认情况下它是启用的，因此您可以随时使用它。
Note
SHOW ENGINE INNODB MUTEX仅包括至少等待过一次操作系统的互斥体和读写锁信号量。
使用latch InnoDB 度量(它是隐藏的，所以您看不到当前值)来启用和禁用互斥信息的收集。通常没有理由禁用latch指标。
摘要
本章介绍了可用于监控和调查锁的资源。首先考虑性能模式表。有专门的表用于查询当前的元数据和数据锁请求，其中包含关于作为锁目标的对象的信息，该对象是共享锁还是排他锁，以及锁请求是否已被授予。在最低级别，也有允许您调查同步等待的表；但是，默认情况下这些功能是不启用的，并且开销很大。在粒度尺度的另一端，语句表和错误汇总表可用于调查哪些语句遇到了错误以及错误的频率。
其次，sys模式对于调查锁等待问题也很有用，其中innodb_lock_waits视图提供了关于正在进行的 InnoDB 数据锁等待的信息，而schema_table_lock_waits视图提供了关于正在进行的表元数据锁等待的信息。
第三，在最高级别，状态计数器和 InnoDB 指标给出了实例上活动的概述，包括锁的使用和获取锁的失败。如果您想要更多关于 InnoDB 锁的信息，那么锁监控器提供了与性能模式中的数据锁表类似的信息，但是使用的格式不太方便，InnoDB 监控器包括最近发生的死锁的详细信息。InnoDB 监控器还包括关于信号量等待的信息，最后，InnoDB 互斥体监控器提供关于互斥体等待的统计信息。
获取锁使用信息的另一个有用方法是查看事务信息。这将在下一章考虑。
Footnotes [1](#Fn1_source)https://en.wikipedia.org/wiki/Mutual_exclusion
三、监控 InnoDB 事务
在前一章中，你学习了如何在相对较低的层次上找到关于锁的信息。包含更高级别的信息也很重要，因为锁的持续时间一直到事务完成。(用户锁和显式表锁除外，它们可以持续更长时间。)在 MySQL Server 中，事务的意思是 InnoDB，本章重点是监控 InnoDB 事务。
首先将介绍信息模式中的INNODB_TRX视图。在调查正在进行的事务时，这通常是最重要的资源。关于事务的另一个信息源是 InnoDB monitor，您在前一章中也遇到了它。最后，讨论了INNODB_METRICS和sys.metrics视图中的指标。
信息模式 INNODB_TRX
信息模式中的INNODB_TRX视图是关于 InnoDB 事务的最专门的信息源。它包括诸如事务何时开始、修改了多少行以及持有多少锁之类的信息。INNODB_TRX视图也被sys.innodb_lock_waits视图用来提供一些关于锁等待问题所涉及的事务的信息。表 3-1 汇总了表中的栏目。
表 3-1
information_schema.INNODB_TRX视图中的列
列/数据类型
|
描述
|
| --- | --- |
| trx_id``varchar(18) | 事务记录 id。这在引用事务或与 InnoDB 监控器的输出进行比较时非常有用。否则，id 应该被视为纯内部的，没有任何意义。该 id 仅分配给已修改数据或锁定行的事务；仅执行只读SELECT语句的事务将有一个伪 id，如 421124985258256，如果事务开始修改或锁定记录，该 id 将会改变。 |
| trx_state``varchar(13) | 事务的状态。这可以是RUNNING、LOCK WAIT、ROLLING BACK和COMMITTING中的一个。 |
| trx_started``datetime | 使用系统时区启动事务的时间。 |
| trx_requested_lock_id``varchar(105) | 当trx_state为LOCK WAIT时，该列显示事务正在等待的锁的 id。 |
| trx_wait_started``datetime | 当trx_state为LOCK WAIT时，该列使用系统时区显示锁定等待开始的时间。 |
| trx_weight``bigint unsigned | 根据修改的行数和持有的锁数，衡量事务完成了多少工作。这是用于确定在死锁情况下回滚哪个事务的权重。重量越大，做功越多。 |
| trx_mysql_thread_id``bigint unsigned | 执行事务的连接的连接 id(与性能模式threads表中的PROCESSLIST_ID列相同)。 |
| trx_query``varchar(1024) | 事务当前执行的查询。如果事务空闲，则查询为NULL。 |
| trx_operation_state``varchar(64) | 事务执行的当前操作。即使查询正在执行，这也可能是NULL。 |
| trx_tables_in_use``bigint unsigned | 事务使用的表的数量。 |
| trx_tables_locked``bigint unsigned | 事务持有行锁的表的数量。 |
| trx_lock_structs``bigint unsigned | 事务创建的锁结构的数量。 |
| trx_lock_memory_bytes``bigint unsigned | 事务持有的锁使用的内存量(以字节为单位)。 |
| trx_rows_locked``bigint unsigned | 事务持有的记录锁的数量。虽然被称为行锁，但它也包括索引锁。 |
| trx_rows_modified``bigint unsigned | 事务修改的行数。 |
| trx_concurrency_tickets``bigint unsigned | 当innodb_thread_concurrency不为 0 时，在事务必须允许另一个事务执行工作之前，会给该事务分配innodb_concurrency_tickets个可以使用的票证。一张票对应于访问一行。这一栏显示还剩多少票。 |
| trx_isolation_level``varchar(16) | 用于事务的事务隔离级别。 |
| trx_unique_checks``int | 连接是否启用了unique_checks变量。 |
| trx_foreign_key_checks``int | 连接是否启用了foreign_key_checks变量。 |
| trx_last_foreign_key_error``varchar(256) | 事务遇到的最后一个(如果有)外键错误的错误消息。 |
| trx_adaptive_hash_latched``int | 事务是否锁定了自适应哈希索引的一部分。总共有innodb_adaptive_hash_index_parts个零件。该列实际上是一个布尔值。 |
| trx_adaptive_hash_timeout``bigint unsigned | 是否在多个查询中保持对自适应哈希索引的锁定。如果自适应散列索引只有一部分，并且没有争用，那么超时倒计时，当超时达到 0 时，锁被释放。当存在争用或有多个部分时，每次查询后锁总是被释放，超时值为 0。 |
| trx_is_read_only``int | 该事务是否为只读事务。通过显式声明，事务可以是只读的，或者对于启用了autocommit的单语句事务，InnoDB 可以检测到查询将只读取数据。 |
| trx_autocommit_non_locking``int | 当事务是单语句非锁定的SELECT并且autocommit选项被启用时，该列被设置为 1。当这个列和trx_is_read_only都为 1 时，InnoDB 可以优化事务以减少开销。 |
| trx_schedule_weight``bigint unsigned | 竞争感知事务调度(CATS)算法分配给事务的事务权重(参见第 8 章)。该值仅对处于LOCK WAIT状态的事务有意义。此列是在 8.0.20 中添加的。 |
从INNODB_TRX视图获得的信息使得确定哪些事务具有最大的影响成为可能。清单 3-1 展示了启动两个可以被调查的事务的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 53 163 6
-- 2 54 164 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01);
-- Connection 2
Connection 2> SET SESSION autocommit = ON;
Query OK, 0 rows affected (0.0004 sec)
Connection 2> SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01);
Listing 3-1Example transactions
事务将运行 40-50 秒。当它们执行时，您可以查询INNODB_TRX视图，如清单 3-2 所示(确切的数据取决于测试中的 id 以及您何时查询INNODB_TRX视图)。
-- Investigation #1
-- Connection 3
Connection 3> SELECT *
FROM information_schema.INNODB_TRX
WHERE trx_mysql_thread_id IN (53, 54)\G
*************************** 1\. row ***************************
trx_id: 296813
trx_state: RUNNING
trx_started: 2020-06-27 17:46:10
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1023
trx_mysql_thread_id: 53
trx_query: UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01)
trx_operation_state: NULL
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 14
trx_lock_memory_bytes: 1136
trx_rows_locked: 2031
trx_rows_modified: 1009
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
*************************** 2\. row ***************************
trx_id: 283598406543136
trx_state: RUNNING
trx_started: 2020-06-27 17:46:10
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 54
trx_query: SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01)
trx_operation_state: NULL
trx_tables_in_use: 1
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 1
trx_autocommit_non_locking: 1
trx_schedule_weight: NULL
2 rows in set (0.0008 sec)
Listing 3-2Example output of the INNODB_TRX view
第一行显示了修改数据的事务示例。在检索信息时，已经修改了 1009 行，记录锁的数量大约是现在的两倍。您还可以看到事务仍然在主动执行一个查询(一个UPDATE语句)。
第二行是在启用了autocommit的情况下执行的SELECT语句的示例。由于启用了自动提交，事务中只能有一个语句(显式的START TRANSACTION禁用自动提交)。trx_query列显示它是一个没有任何锁定子句的SELECT COUNT(*)查询，因此它是一个只读语句。这意味着 InnoDB 可以跳过一些事情，比如为事务准备锁定和撤销信息，从而减少事务的开销。trx_autocommit_non_locking列被设置为 1 以反映这一点。
您应该担心哪些事务取决于系统上的预期工作负载。如果您有一个 OLAP 工作负载，预计会有相对长时间运行的SELECT查询。对于纯 OLTP 工作负载，任何运行时间超过一秒并修改多行的事务都可能是有问题的迹象。例如，要查找超过 10 秒的事务，可以使用以下查询:
SELECT *
FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 10 SECOND;
您可以选择连接其他表，比如性能模式中的threads和events_statements_current。清单 3-3 中显示了一个这样的例子。
-- Investigation #3
Connection 3> SELECT thd.thread_id, thd.processlist_id,
trx.trx_id, stmt.event_id, trx.trx_started,
TO_SECONDS(NOW()) -
TO_SECONDS(trx.trx_started
) AS age_seconds,
trx.trx_rows_locked, trx.trx_rows_modified,
FORMAT_PICO_TIME(stmt.timer_wait) AS latency,
stmt.rows_examined, stmt.rows_affected,
sys.format_statement(SQL_TEXT) as statement
FROM information_schema.INNODB_TRX trx
INNER JOIN performance_schema.threads thd
ON thd.processlist_id = trx.trx_mysql_thread_id
INNER JOIN performance_schema.events_statements_current stmt
USING (thread_id)
WHERE trx_started < NOW() - INTERVAL 10 SECOND\G
*************************** 1\. row ***************************
thread_id: 163
processlist_id: 53
trx_id: 296813
event_id: 9
trx_started: 2020-06-27 17:46:10
age_seconds: 25
trx_rows_locked: 2214
trx_rows_modified: 1100
latency: 25.24 s
rows_examined: 2201
rows_affected: 0
statement: UPDATE world.city SET Populati ... ion + MOD(ID, 2) + SLEEP(0.01)
*************************** 2\. row ***************************
thread_id: 164
processlist_id: 54
trx_id: 283598406543136
event_id: 8
trx_started: 2020-06-27 17:46:10
age_seconds: 25
trx_rows_locked: 0
trx_rows_modified: 0
latency: 25.14 s
rows_examined: 0
rows_affected: 0
statement: SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01)
2 rows in set (0.0021 sec)
Listing 3-3Querying details of old transactions
您可以连接到这些表，并选择与您的调查相关的列。
与INNODB_TRX视图相关的是 InnoDB 监控器中的事务列表。
InnoDB 监控器
InnoDB monitor 是 InnoDB information 的一种瑞士军刀，也包含有关事务的信息。InnoDB 监控器输出中的TRANSACTIONS部分专用于事务信息。该信息不仅包括事务列表，还包括历史列表长度。清单 3-4 显示了 InnoDB monitor 的一个摘录，其中的事务部分的示例取自INNODB_TRX视图的前一个输出。
-- Investigation #4
Connection 3> SHOW ENGINE INNODB STATUS\G
*************************** 1\. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-06-27 17:46:36 0x5784 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
...
------------
TRANSACTIONS
------------
Trx id counter 296814
Purge done for trx's n:o < 296813 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283598406541472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406540640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406539808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406538976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 296813, ACTIVE 26 sec fetching rows
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1136, 2333 row lock(s), undo log entries 1160
MySQL thread id 53, OS thread handle 23748, query id 56574 localhost ::1 root User sleep
UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01)
...
Listing 3-4Transaction information from the InnoDB monitor
TRANSACTIONS部分的顶部显示了事务 id 计数器的当前值，后面是已经从撤销日志中清除的信息。它显示事务 id 小于 296813 的撤消日志已被清除。该清除越晚，历史列表的长度(在该部分的第三行)就越长。从 InnoDB monitor 输出中读取历史列表长度是获取历史列表长度的传统方法。在下一节中，将展示如何在用于监控目的时以更好的方式获取值。
该部分的其余部分是事务列表。注意，虽然输出是用与在INNODB_TRX中找到的相同的两个活动事务生成的，但是事务列表只包括一个活动事务(用于UPDATE语句的事务)。在 MySQL 5.7 和更高版本中，只读非锁定事务不包括在 InnoDB monitor 事务列表中。因此，如果需要包含所有活动的事务，最好使用INNODB_TRX视图。
如前所述，还有一种方法可以获得历史列表的长度。为此，您需要使用 InnoDB 指标。
INNODB_METRICS 和 sys.metrics
InnoDB monitor 报告对于数据库管理员了解 InnoDB 中正在发生的事情非常有用，但是对于监控来说，它的用处就没有那么大了，因为它需要进行解析，以监控可以使用的方式获取数据。您在本章的前面已经看到了如何从information_schema.INNODB_TRX视图中获得关于事务的信息，但是像历史列表长度这样的度量标准又如何呢？
InnoDB 指标系统包括几个指标，在information_schema.INNODB_METRICS视图中显示关于事务的信息。这些指标都位于transaction子系统中。清单 3-5 显示了事务度量的列表，无论它们是否默认启用，以及解释度量测量什么的简短注释。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 56 166 6
-- Connection 1
Connection 1> SELECT NAME, COUNT, STATUS, COMMENT
FROM information_schema.INNODB_METRICS
WHERE SUBSYSTEM = 'transaction'\G
*************************** 1\. row ***************************
NAME: trx_rw_commits
COUNT: 0
STATUS: disabled
COMMENT: Number of read-write transactions committed
*************************** 2\. row ***************************
NAME: trx_ro_commits
COUNT: 0
STATUS: disabled
COMMENT: Number of read-only transactions committed
*************************** 3\. row ***************************
NAME: trx_nl_ro_commits
COUNT: 0
STATUS: disabled
COMMENT: Number of non-locking auto-commit read-only transactions committed
*************************** 4\. row ***************************
NAME: trx_commits_insert_update
COUNT: 0
STATUS: disabled
COMMENT: Number of transactions committed with inserts and updates
*************************** 5\. row ***************************
NAME: trx_rollbacks
COUNT: 0
STATUS: disabled
COMMENT: Number of transactions rolled back
*************************** 6\. row ***************************
NAME: trx_rollbacks_savepoint
COUNT: 0
STATUS: disabled
COMMENT: Number of transactions rolled back to savepoint
*************************** 7\. row ***************************
NAME: trx_rollback_active
COUNT: 0
STATUS: disabled
COMMENT: Number of resurrected active transactions rolled back
*************************** 8\. row ***************************
NAME: trx_active_transactions
COUNT: 0
STATUS: disabled
COMMENT: Number of active transactions
*************************** 9\. row ***************************
NAME: trx_on_log_no_waits
COUNT: 0
STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 10\. row ***************************
NAME: trx_on_log_waits
COUNT: 0
STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 11\. row ***************************
NAME: trx_on_log_wait_loops
COUNT: 0
STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 12\. row ***************************
NAME: trx_rseg_history_len
COUNT: 9
STATUS: enabled
COMMENT: Length of the TRX_RSEG_HISTORY list
*************************** 13\. row ***************************
NAME: trx_undo_slots_used
COUNT: 0
STATUS: disabled
COMMENT: Number of undo slots used
*************************** 14\. row ***************************
NAME: trx_undo_slots_cached
COUNT: 0
STATUS: disabled
COMMENT: Number of undo slots cached
*************************** 15\. row ***************************
NAME: trx_rseg_current_size
COUNT: 0
STATUS: disabled
COMMENT: Current rollback segment size in pages
15 rows in set (0.0012 sec)
Listing 3-5InnoDB metrics
related to transactions
这些指标中最重要的是trx_rseg_history_len，它是历史列表长度。这也是默认情况下启用的唯一指标。与提交和回滚相关的指标可用于确定您拥有多少读写、只读和非锁定只读事务，以及它们提交和回滚的频率。许多回滚表明存在问题。如果您怀疑重做日志是一个瓶颈，那么可以使用trx_on_log_%指标来衡量在事务提交期间有多少事务在等待重做日志。
Tip
使用innodb_monitor_enable选项启用 InnoDB 指标，使用innodb_monitor_disable禁用它们。这可以动态完成。
查询 InnoDB 指标的另一种方便的方法是使用sys.metrics视图，其中也包括全局状态变量。清单 3-6 展示了一个使用sys.metrics视图获取当前值以及指标是否启用的示例。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 52 125 6
-- Connection 1
Connection 1> SELECT Variable_name AS Name,
Variable_value AS Value,
Enabled
FROM sys.metrics
WHERE Type = 'InnoDB Metrics - transaction';
+---------------------------+-------+---------+
| Name | Value | Enabled |
+---------------------------+-------+---------+
| trx_active_transactions | 0 | NO |
| trx_commits_insert_update | 0 | NO |
| trx_nl_ro_commits | 0 | NO |
| trx_on_log_no_waits | 0 | NO |
| trx_on_log_wait_loops | 0 | NO |
| trx_on_log_waits | 0 | NO |
| trx_ro_commits | 0 | NO |
| trx_rollback_active | 0 | NO |
| trx_rollbacks | 0 | NO |
| trx_rollbacks_savepoint | 0 | NO |
| trx_rseg_current_size | 0 | NO |
| trx_rseg_history_len | 16 | YES |
| trx_rw_commits | 0 | NO |
| trx_undo_slots_cached | 0 | NO |
| trx_undo_slots_used | 0 | NO |
+---------------------------+-------+---------+
15 rows in set (0.0089 sec)
Listing 3-6Using the sys.metrics view
to get the transaction metrics
这表明历史列表长度为 16，这是一个很低的值，因此撤销日志几乎没有开销。其余指标被禁用。
摘要
本章介绍了如何获取关于 InnoDB 事务的信息。详细信息的主要来源是信息模式中的INNODB_TRX视图，其中包括诸如事务启动时间、锁定和修改的行数等细节。您可以选择连接性能模式表，以获得有关事务的更多信息。
您还可以使用 InnoDB monitor 来获取关于锁定事务的信息；但是，一般情况下，最好使用INNODB_TRX视图。如果您正在寻找更高级别的聚合统计数据，您可以使用information_schema.INNODB_METRICS视图或者sys.metrics视图。最常用的指标是显示历史列表长度的trx_rseg_history_len。
迄今为止，关于事务信息的讨论一直是关于所有事务或单个事务的汇总统计数据。如果你想更深入地了解一个事务做了什么工作，你需要使用下一章讨论的性能模式。
