两文说透MySQL里的各种锁(上篇)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 两文说透MySQL里的各种锁(上篇)

0.前言


MySQL按照加锁的范围,分为全局锁、表级锁、行级锁。


本文作为上篇,主要介绍MySQL的全局锁 和 表级锁。


重要的实战总结为,如何安全地变更一个表的表结构。

5.jpg

                                                        搞个图做封面


1. 全局锁


定义:

全局锁就是对整个数据库实例加锁。


全局锁语法:

Flush tables with read lock (FTWRL)


当你使用这个命令后,整个库处于只读状态,之后其他线程的数据更新语句(DML)、数据定义语句(DDL)都会被阻塞。


场景:不支持事务的引擎(如MyISAM),做全库的逻辑备份。


不过我们一般使用innodb,这个锁不太会接触,就不展开详细介绍了。


2. 表级锁


表级锁其实有两种,一种叫表锁,一种叫原数据锁(meta data lock, MDL)


2.1 表锁


表锁的语法是:

锁表:lock tables … read/write

释放锁:unlock tables 主动释放锁

表锁也能够在客户端断开的时候自动释放。


值得注意的是,lock tables 除了会限制别的线程的读写外,当前线程接下来的操作也会被限制。


同样的,对于innoDB这种支持行锁的引擎,我们一般也不会去使用用表锁,因此,这部分内容也只需要简单了解下即可。


2.2 MDL锁


原数据锁meta data lock 也是一种表级锁,在 MySQL 5.5 版本中引入。


当我们对一个表做CRUD操作的时候,会加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

6.jpg

                                              MDL读写锁的互斥


  • 读锁之间不互斥,所以我们可以有多个线程同时对一张表增删改查。
  • 读锁和写锁之间、写锁和写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,只要有一个线程要对一个表加字段,那天其他线程必须等这个线程完成表结构变更以后才能执行。


看到这里,不得不给大家举例一个日常会踩的坑。


对于大表DDL,大家一般比较谨慎,而对于小表,就会比较随意。但是小表DDL也能造成数据库崩溃。

7.jpg

                                             表结构变更阻塞实例


Session A第一个启动,开启事务,select后没有立刻commit,模仿一个长事务,此时,session A对表tt的MDL读锁还没有释放。

Session B之后执行一个select,由于MDL读锁之间不互斥,因此执行成功。

Session C之后想对表tt 执行一个alter语句,需要获得MDL写锁,但是由于Session A持有了MDL读锁,读写锁互斥,因此Session C被阻塞。

Session D这时候也想执行一个select,由于Session C被阻塞,所以Session D也会被阻塞。


所以这个时候,后续的线程就都无法读写了。


如果接下来这个表上有频繁的查询,而且客户端有重试机制,那么超时后会再起一个新的session来查询,很快数据库的线程就溢出了。

这里有几个小问题需要稍微再解释一下:


1)从Session A的情况我们得知,事务中 的MDL锁,在语句开始时申请,但是并不是在语句结束后马上释放的,而是在整个事务提交后才会释放。

2)SessionC(DDL操作)被前面的SessionA和B(查询操作,获取MDL 读锁)所阻塞,

这里实际上并没有成功获取MDL写锁,为什么Session D的读操作会被sessionC所阻塞呢?这里的原因是,MySQL Server端,对于Session C和Session D会有一个队列来决定谁先执行。


看到这里,我相信肯定有对MySQL比较熟悉的朋友会问了,MySQL 5.6不是号称支持Online DDL吗?怎么这里又会有各种阻塞呢?


首先,我们先明确下什么叫做Online DDL。

Online DDL的过程中,对于锁的获取分为五步(具体online DDL过程比较复杂,本文不展开说明):

1)拿到MDL写锁

2)降级成MDL读锁

3)真正做DDL

4)升级成MDL写锁

5)释放MDL锁


1、2、4、5如果没有锁冲突,执行时间都是非常短的。绝大部分时间是第三步占用了,而这个期间,表可用正常读写,所以被称为Online DDL。


而我们上文的例子,实际上是在第一步就阻塞了。


3. So,如何做一次安全的表结构变更


其实,无论大表小表的表结构变更,都应用引起我们重视。

总结了上文,大家应该都知道了最关键的三点:

避免长事务!

避免长事务!

避免长事务!


尤其是在执行表结构变更前,可以在information_schema库的innodb_trx表中,查看当前执行的事务。如果有长事务正在执行,要延迟等待执行变更,或者手动先kill长事务。


另外,尽量保证表结构变更在数据库流量低峰期操作,比如夜间,这样能更好地避免出现风险。


所以,如何做一次安全的表结构变更?

1)避免长事务

2)在流量低峰进行

就是这么简单。


下一期,我们就好好聊聊 行锁,不见不散~


参考:

丁奇《MySQL 实战45讲》

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
4月前
|
SQL AliSQL 关系型数据库
MYSQL的全局锁和表锁
本文介绍了MySQL中的锁机制,包括全局锁、表级锁及其应用场景。全局锁通过`Flush tables with read lock (FTWRL)`实现,主要用于全库逻辑备份,但会阻塞更新和结构变更操作。表级锁分为显式表锁(`lock tables`)和元数据锁(MDL),前者用于控制并发访问,后者自动加锁以确保读写正确性。文章还探讨了如何安全地为小表添加字段,建议通过设置DDL等待时间或使用MariaDB/AliSQL的NOWAIT/WAIT功能避免业务阻塞。这些方法有助于在高并发场景下优化数据库性能与安全性。
101 0
|
11月前
|
SQL 关系型数据库 MySQL
MySQL 锁
MySQL里常见的几种锁
170 3
|
7月前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
301 25
|
8月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
|
11月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
576 1
|
11月前
|
关系型数据库 MySQL Java
MySQL数据锁:Record Lock,Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎,深入解析三种行锁机制:记录锁(Record Lock)、间隙锁(Gap Lock)和临键锁(Next-key Lock)。记录锁锁定索引记录,确保事务唯一修改;间隙锁锁定索引间的间隙,防止新记录插入;临键锁结合两者,锁定范围并记录自身,有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
1071 2
|
11月前
|
存储 关系型数据库 MySQL
MySQL数据库锁:共享锁和独占锁
本文详细介绍了`InnoDB`存储引擎中的两种行级别锁:共享锁(S锁)与排他锁(X锁)。通过具体示例展示了这两种锁的工作机制及其在`InnoDB`与`MyISAM`引擎中的表现差异。文章还提供了锁的兼容性矩阵,帮助读者更好地理解锁之间的互斥关系。最后总结了两种锁的特点及适用场景。适合希望深入了解`MySQL`并发控制机制的读者阅读。
413 1
|
12月前
|
监控 关系型数据库 MySQL
MySQL锁机制与解决死锁问题
MySQL锁机制与解决死锁问题
560 5
|
11月前
|
存储 关系型数据库 MySQL
MySQL锁,锁的到底是什么?
【10月更文挑战第16天】MySQL 锁锁定的是与数据和资源相关的对象,其目的是为了保证数据的一致性、避免冲突,并在并发环境下合理协调事务或操作的执行。理解锁的对象和意义对于优化数据库性能、处理并发问题至关重要。
292 0
|
11月前
|
关系型数据库 MySQL 数据库
mysql锁详解
通过理解并合理运用MySQL中的锁机制,开发者可以有效管理数据库并发访问,平衡性能与数据一致性需求。更多关于MySQL锁的深入探讨和最佳实践,请参考专业的数据库管理资源[[深入MySQL锁机制详解
209 0

推荐镜像

更多