MySQL数据库——锁-表级锁(表锁、元数据锁、意向锁)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库——锁-表级锁(表锁、元数据锁、意向锁)

介绍

表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁,主要分为以下三类:

  • 表锁
  • 元数据锁(meta data lock,MDL)
  • 意向锁

表锁

对于表锁,分为两类:

  • 表共享读锁(read lock)
  • 表独占写锁(write lock)

语法

  • 加锁:lock tables 表名... read/write。
  • 释放锁:unlock tables / 客户端断开连接 。

特点

A.读锁

左侧为客户端一,对指定表加了读锁,不会影响右侧客户端二的读,但是会阻塞右侧客户端的写。

测试:

 

B.写锁

左侧为客户端一,对指定表加了写锁,会阻塞右侧客户端的读和写。

测试:


结论:

读锁不会阻塞其他客户端的读,但是会阻塞写。

写锁既会阻塞其他客户端的读,又会阻塞其他客户端的写。

元数据锁

介绍

meta data lock , 元数据锁,简写MDL。

MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。


为了避免DML与DDL冲突,保证读写的正确性。这里的元数据,可以简单理解为就是一张表的表结构。 也就是说,某一张表涉及到未提交的事务时,是不能够修改这张表的表结构的。


在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL读锁(共享);


当对表结构进行变更操作的时候,加MDL写锁(排他)。


常见的SQL操作时,所添加的元数据锁:

image.png 演示

当执行SELECT、INSERT、UPDATE、DELETE等语句时,添加的是元数据共享锁(SHARED_READ /SHARED_WRITE),之间是兼容的。



当执行SELECT语句时,添加的是元数据共享锁(SHARED_READ),会阻塞元数据排他锁(EXCLUSIVE),之间是互斥的。

我们可以通过下面的SQL,来查看数据库中的元数据锁的情况:

select object_type,object_schema,object_name,lock_type,lock_duration from
performance_schema.metadata_locks ;

我们在操作过程中,可以通过上述的SQL语句,来查看元数据锁的加锁情况。

意向锁

介绍

为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查。


假如没有意向锁,客户端一对表加了行锁后,客户端二如何给表加表锁呢,来通过示意图简单分析一下:


首先客户端一,开启一个事务,然后执行DML操作,在执行DML语句时,会对涉及到的行加锁。

当客户端二,想对这张表加表锁时,会检查当前表是否有对应的行锁,如果没有,则添加表锁,此时就会从第一行数据,检查到最后一行数据,效率较低。

有了意向锁之后,

客户端一,在执行DML操作时,会对涉及的行加行锁,同时也会对该表加上意向锁。

而其他客户端,在对这张表加表锁的时候,会根据该表上所加的意向锁来判定是否可以成功加表锁,而不用逐行判断行锁情况了。


分类

  • 意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容,与表锁排他锁(write)互斥。
  • 意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。

一旦事务提交了,意向共享锁、意向排他锁,都会自动释放。

可以通过以下SQL,查看意向锁及行锁的加锁情况:

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

演示

A. 意向共享锁与表读锁是兼容的

B. 意向排他锁与表读锁、写锁都是互斥的

 


END



相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
5月前
|
SQL AliSQL 关系型数据库
MYSQL的全局锁和表锁
本文介绍了MySQL中的锁机制,包括全局锁、表级锁及其应用场景。全局锁通过`Flush tables with read lock (FTWRL)`实现,主要用于全库逻辑备份,但会阻塞更新和结构变更操作。表级锁分为显式表锁(`lock tables`)和元数据锁(MDL),前者用于控制并发访问,后者自动加锁以确保读写正确性。文章还探讨了如何安全地为小表添加字段,建议通过设置DDL等待时间或使用MariaDB/AliSQL的NOWAIT/WAIT功能避免业务阻塞。这些方法有助于在高并发场景下优化数据库性能与安全性。
114 0
|
7月前
|
SQL 存储 关系型数据库
数据库的行级锁与表锁?
表锁: 不会出现死锁,发生锁的冲突几率高,并发性低。 存储引擎在进行SQL数据读写请求前,会对涉及到的表进行加锁。 其中锁分为共享读锁和独占写锁:读锁会阻塞写,写锁会阻塞读和写。 行级锁: 会出现死锁,发生锁的冲突几率低,并发性高。 InnoDB引擎支持行锁,与Oracle不同,MySQL的行锁是通过索引加载的,也就是说,行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。 行级锁注意事项: 行级锁必须有索引才能实现,否则会自动锁全表,那就不是行锁了。 两个事务不能锁同一个索引。 in
|
8月前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
324 25
|
9月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
|
10月前
|
SQL 存储 关系型数据库
数据库的行级锁与表锁?
表锁:存储引擎在SQL数据读写请求前对涉及的表加锁,分共享读锁和独占写锁,读锁阻塞写,写锁阻塞读写,易发锁冲突,并发性低。行级锁:InnoDB支持,通过索引加锁,提高并发性,但可能引起死锁,需注意索引使用,适用于避免不可重复读场景。
155 21
|
12月前
|
监控 数据库 索引
避免锁等待超时对数据库性能的影响
【10月更文挑战第16天】避免锁等待超时对数据库性能的影响需要综合考虑多个方面,通过不断地优化和改进,来提高数据库的并发处理能力和稳定性。
180 1
|
12月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
594 1
|
1月前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
|
16天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
54 3
|
22天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。

推荐镜像

更多