正文

第25天sql进阶-查询优化-performance_schema系列实战二：锁问题排查（全局读锁）（sql小虚竹)

author  author  2023-01-26  358

关键词：

文章目录

零、前言
一、什么是全局锁
二、什么时候适合加全局锁
三、实战演练

3.1 数据准备（如果已有数据可跳过此操作）
3.2 开启第一个会话，执行全局读锁
3.3 开启第二个会话,修改表数据
3.4 开启第三个会话，进行排查
3.5 分析
3.6 释放全局读锁

四、总结
五、参考

零、前言

今天是学习 SQL 打卡的第 25 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区中对应的【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-查询优化- performance_schema系列实战二：锁问题排查（全局读锁）

一、什么是全局锁

全局锁是对整个数据库来说的，当对数据库加了全局读锁，任何请求都不能对数据库进行加写锁操作了；当对数据库加了全局写锁，后面对数据库的加读锁和写锁操作会被阻塞。

二、什么时候适合加全局锁

全局锁适合在进行主从备份数据、或者导入导出数据的时候才会对全局进行加锁。

三、实战演练

在MySQL 5.7之前的版本，要排查谁持有全局读锁通常在数据库层面是很难直接查询到有用数据（innodb_locks表也只能记录innodb层的锁信息，而全局读锁是server层的锁，所以也无法查询到）。

从MySQL 5.7开始提供表performance_schema.metadata_locks表记录一些Server层的锁信息（包括全局读锁和MDL锁等），下面我们通过一个示例来演示然后使用performance_schema来找出谁持有全局读锁。

3.1 数据准备（如果已有数据可跳过此操作）

使用sysbench准备初始化数据
创建测试数据库sysbenchdemo

create database sysbenchdemo;

【第25天】SQL进阶-查询优化-

准备测试数据：

sysbench /usr/share/sysbench/oltp_insert.lua \\
--mysql-host=localhost \\
--mysql-port=3306 \\
--mysql-socket=/tmp/mysql.sock \\
--mysql-user=root \\
--mysql-password=xiaoxuzhu \\
--mysql-db=sysbenchdemo \\
--db-driver=mysql \\
--tables=8 \\
--table-size=100000 \\
--time=180 prepare

【第25天】SQL进阶-查询优化-

3.2 开启第一个会话，执行全局读锁

登录mysql数据库

use sysbenchdemo;

【第25天】SQL进阶-查询优化-

执行加锁语句

flush table with read lock;

【第25天】SQL进阶-查询优化-

查询以下加锁线程的process id，以便后续排查过程好对应

select connection_id();

【第25天】SQL进阶-查询优化-

3.3 开启第二个会话,修改表数据

登录mysql数据库

use sysbenchdemo;

【第25天】SQL进阶-查询优化-

查询sbtest1表的第一条数据

select * from sbtest1 limit 1;

【第25天】SQL进阶-查询优化-

查询当前线程的process id，以便后续排查过程好对应

select connection_id();

【第25天】SQL进阶-查询优化-

修改sbtest1表的第一条数据

update sbtest1 set pad=xxx where id=1;

【第25天】SQL进阶-查询优化-

会发现，修改不了，一直处于堵塞。

3.4 开启第三个会话，进行排查

我们还可以通过performance_schema.metadata_locks表来排查谁持有全局读锁，全局读锁通常在该表记录着同一个会话的OBJECT_TYPE为global和commit、LOCK_TYPE都为SHARED的两把显式锁

select * from performance_schema.metadata_locks where OWNER_THREAD_ID!=sys.ps_thread_id(connection_id());

【第25天】SQL进阶-查询优化-

其中OWNER_THREAD_ID: # 持有锁的内部线程ID

查看process id为 12372，12427 各自对应的内部线程ID是多少

select sys.ps_thread_id(12372);

【第25天】SQL进阶-查询优化-

process id=12372的线程对应的内部线程ID正好为12536，说明就是process id=12372的线程持有了全局读锁

select sys.ps_thread_id(12427);

【第25天】SQL进阶-查询优化-

proces id=12427的线程对应的内部线程正好是12591，说明在等待全局读锁的就是process id=12427的线程

3.5 分析

结合上面的分析，再通过 show processlist找到对应行记录信息，就可大概推测出这是哪个的业务语句。

【第25天】SQL进阶-查询优化-

3.6 释放全局读锁

在产生阻塞的会话内释放全局读锁。

unlock tables;

【第25天】SQL进阶-查询优化-

查看第二个会话，发现执行了修改语句。释放全局读锁成功。

【第25天】SQL进阶-查询优化-

四、总结

通过本文学习，学会了什么是全局锁以及全局锁的适用场景，通过实战演练排查全局读锁问题，从理论到实战的介绍，可以加深对全局锁的理解。

五、参考

应用示例荟萃 | performance_schema全方位介绍（上）

我是虚竹哥，我们明天见~

第25天sql进阶-查询优化-performance_schema系列实战二：锁问题排查（全局读锁）（sql小虚竹)

回城传送–》《32天SQL筑基》文章目录零、前言一、什么是全局锁二、什么时候适合加全局锁三、实战演练3.1数据准备（如果已有数据可跳过此操作）3.2开启第一个会话，执行全局读锁3.3开启第二个会话,修改表数据3.4开启第三个... 查看详情

第15天sql进阶-查询优化-慢查询日志（sql小虚竹)

...SQL筑基》文章目录零、前言一、练习题目二、SQL思路：SQL进阶-查询优化-慢查询日志初始化数据解法慢查询日志是什么如何开启慢查询日志第一种方式windows实战slow_query_log一直为off，没生效验证linux实战验证第二种方式实操验证... 查看详情

第17天sql进阶-查询优化-showstatus（sql小虚竹)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化-SHOWSTATUS初始化数据解法SHOWSTATUS是什么实战经验：常用的mysql状态查询1、QPS(每秒处理的请求数量)计算思路：Questions和Queries的区别2、TPS(每秒处理的事务数量... 查看详情

第18天sql进阶-查询优化-showprofile（sql小虚竹)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化-SHOWPROFILE初始化数据解法SHOWPROFILE是什么开启/关闭PROFILE分析InnoDB数据表初始化数据性能分析分析MyISAM数据表初始化数据性能分析三、扩展SQL执行过程中可能... 查看详情

第18天sql进阶-查询优化-showprofile（sql小虚竹)

第17天sql进阶-查询优化-showstatus（sql小虚竹)

第16天sql进阶-查询优化一定要学expalin（sql小虚竹)

...言一、练习题目二、SQL思路SQL进阶-查询优化一定要学EXPALIN初始化数据解法EXPALIN是什么EXPALIN的用法输出列介绍重要的列特别说明id... 查看详情

第16天sql进阶-查询优化一定要学expalin（sql小虚竹)

回城传送–》《32天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化一定要学EXPALIN初始化数据解法EXPALIN是什么EXPALIN的用法输出列介绍重要的列特别说明idselect_typetypesystemconsteq_refreffulltextref_or_nullindex_merge 查看详情

第16天sql进阶-查询优化一定要学expalin（sql小虚竹)

第17天sql进阶-查询优化-showstatus（sql小虚竹)(代码片段)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化-SHOWSTATUS初始化数据解法SHOWSTATUS是什么实战经验：常用的mysql状态查询1、QPS(每秒处理的请求数量)计算思路：Questions和Queries的区别2、TPS(每秒处理的... 查看详情

第18天sql进阶-查询优化-showprofile（sql小虚竹)(代码片段)

第16天sql进阶-查询优化一定要学expalin（sql小虚竹)(代码片段)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化一定要学EXPALIN初始化数据解法EXPALIN是什么EXPALIN的用法输出列介绍重要的列特别说明idselect_typetypefulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL总结... 查看详情

第21天sql进阶-查询优化-performance_schema系列三：事件记录（sql小虚竹)(代码片段)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化-performance_schema系列三：事件记录等待事件表events_waits_current表实战试试：events_waits_history表events_waits_history_long表语句事件表events_statements_current表实战... 查看详情

第20天sql进阶-查询优化-performance_schema系列二：常用配置详解（sql小虚竹)(代码片段)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化-performance_schema系列二：常用配置详解启动时配置启动选项配置systemvariables运行时设置performance_timers表setup_timers表setup_consumers表setup_instruments表setup_actors表... 查看详情

第20天sql进阶-查询优化-performance_schema系列二：常用配置详解（sql小虚竹)(代码片段)

第19天sql进阶-查询优化-performance_schema系列一：了解performance_schema（sql小虚竹)

...天SQL筑基》文章目录零、前言一、练习题目二、SQL思路SQL进阶-查询优化-performance_schema初始化数据解法performance_schema是什么performance_schema特点performance_schema设计目标MySQL是否支持performance_schema开启/关闭performance_schema了解performance_ 查看详情