用实例带你了解 MySQL 全局锁

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL全局锁会申请一个全局的读锁,对整个库加锁。

MySQL全局锁会申请一个全局的读锁,对整个库加锁。


全局锁的一般使用场景是:全局逻辑备份全局锁的实现方式有两种:


//第一种方法
Flush tables with read lock(FTWRL)
//第二种方法
set global readonly=true


当数据库处于全局锁的状态时,其他线程的一下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(建表、索引变更、修改表结构等)和更新类事务的提交语句。


释放全局锁


unlock tables;


来个示例吧~


创建数据库 `test`


CREATE TABLE `test` (
  `name` varchar(32) NOT NULL DEFAULT '',
  `bid` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试';


插入数据


insert into test values('A', 1), ('B',2),('C',3);


查看表数据


> select * from test;
+------+-----+
| name | bid |
+------+-----+
| A    |   1 |
| B    |   2 |
| C    |   3 |
+------+-----+


加锁


flush tables with read lock;


新增数据


insert into test values('D', 4);


执行 insert 操作后,直接返回错误结果:


ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock


执行查询操作,可以正常返回结果:


> select * from test;
#返回结果:
+------+-----+
| name | bid |
+------+-----+
| A | 1 |
| B | 2 |
| C | 3 |
+------+-----+vv


可以看到,当我们加上全局锁的时候,数据及表更新操作都没办法执行,但表查询不受影响。这样会给我们的业务造成很大的影响(无法修改数据),索性 Innodb 引擎的可重复读隔离级别可以让我们不阻塞数据变更的同时导出数据。


官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。


你一定在疑惑,有了这个功能,为什么还需要 FTWRL 呢?一致性读是好,但前提是引擎要支持这个隔离级别比如,对于 MyISAM 这种不支持事务的引擎,如果备份过程中有更新,总是只能取到最新的数据,那么就破坏了备份的一致性。这时,我们就需要使用 FTWRL 命令了。single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎,那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。


用 FTWRL 而不用 set global readonly = true


  • 在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大,我不建议你使用。


  • 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
27天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
28天前
|
存储 弹性计算 关系型数据库
如何通过控制台创建RDS MySQL实例
本文介绍了通过控制台创建RDS MySQL实例的详细步骤,包括准备工作、选择计费方式、地域、实例规格、存储空间等关键配置,并指导用户完成下单与实例查看。
|
5月前
|
SQL AliSQL 关系型数据库
MYSQL的全局锁和表锁
本文介绍了MySQL中的锁机制,包括全局锁、表级锁及其应用场景。全局锁通过`Flush tables with read lock (FTWRL)`实现,主要用于全库逻辑备份,但会阻塞更新和结构变更操作。表级锁分为显式表锁(`lock tables`)和元数据锁(MDL),前者用于控制并发访问,后者自动加锁以确保读写正确性。文章还探讨了如何安全地为小表添加字段,建议通过设置DDL等待时间或使用MariaDB/AliSQL的NOWAIT/WAIT功能避免业务阻塞。这些方法有助于在高并发场景下优化数据库性能与安全性。
117 0
|
2月前
|
存储 关系型数据库 MySQL
【赵渝强老师】MySQL数据库的多实例环境
MySQL多实例是指在一台服务器上运行多个MySQL服务,通过不同端口提供独立的数据服务。各实例共享安装程序,但使用各自的配置文件和数据文件,实现资源高效利用。本文详细介绍了如何通过“mysqld_multi”工具配置和启动多个MySQL实例,并演示了目录创建、初始化、配置文件修改及实例启动等操作步骤。
|
6月前
|
SQL Oracle 关系型数据库
在MySQL Shell里 重启MySQL 8.4实例
在MySQL Shell里 重启MySQL 8.4实例
159 2
|
8月前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
327 25
|
9月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
|
12月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
599 1
|
11月前
|
关系型数据库 MySQL 数据库
【赵渝强老师】启动与关闭MySQL数据库实例
MySQL数据库安装完成后,可以通过命令脚本启动、查看状态、配置开机自启、查看自启列表及关闭数据库。本文提供了详细的操作步骤和示例代码,并附有视频讲解。
145 0
|
12月前
|
存储 关系型数据库 MySQL
MySQL锁,锁的到底是什么?
【10月更文挑战第16天】MySQL 锁锁定的是与数据和资源相关的对象,其目的是为了保证数据的一致性、避免冲突,并在并发环境下合理协调事务或操作的执行。理解锁的对象和意义对于优化数据库性能、处理并发问题至关重要。
303 0

推荐镜像

更多