MySQL 与InnoDB 下的锁做朋友 (二)共享锁与排他锁

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 与InnoDB 下的锁做朋友 (二)共享锁与排他锁

前言


 

没有前言,我们直接看内容。


正文


 

共享锁(Shared Lock)


共享锁、S锁、读锁   ,都是他的叫法。

而我,喜欢叫他 共享读锁


A shared (S) lock permits the transaction that holds the lock to read 。  

共享锁允许持有该锁的事务读取。


这里的共享是, 读读共享

也就是说, 无论是 行级或是表级 , 如果 对某数据 上了 共享读锁 ,其他事务可以继续 读(也就是允许持有共享读锁), 但是 不能写,也就是 读写互斥

顺便介绍一下如何  加 共享锁(共享读锁) :

上表级共享锁,也就是表级共享读锁:


select  *  from table(表) lock in share mode

上行级共享锁,也就是行级共享读锁:


select  *  from table(表)where id = 10  lock in share mode


在这啰唆一点, 注意了,在InnoDB 下, 不是你想用行锁就用行锁的,行锁的触发条件我们再次回顾下(开篇有提到):


image.png


排他锁(Exclusive Lock)


排他锁 、写锁、X锁  ,都是他的叫法。


而我,喜欢叫他 独占写锁。


An exclusive (X) lock permits the transaction that holds the lock to update or delete。


独占(X)锁允许持有锁的事务更新或删除。


独占 ,这个词。 打过篮球没,以前初中打篮球不懂事,拿着球就不传。同学就说我,你好独啊。


是的,我很独。 就跟这个独占写锁 (排他锁) 一样,很独。


当事务对某数据加上了 独占写锁 (排他锁) ,只有当前事务能够对这数据执行修改或删除操作。


其他事务,不能读,不能写 。 因为 这个锁 很独, 必须等这个很独 的锁 使用完了(释放),其他事务才有机可乘。


所以,独占写锁 (排他锁) 是,读写互斥、写写互斥的。


顺便介绍一下如何  加 排他锁(独占写锁) :


上表级排他锁,也就是表级独占写锁:


select * from table for update


上行级排他锁,也就是行级独占写锁:


select * from table where id =10 for update


在这我再再啰唆一点, 注意了,在InnoDB 下, 不是你想用行锁就用行锁的,行锁的触发条件我们再次回顾下(开篇有提到):


image.png


上面的sql能上行级排他锁,是因为命中了索引, id 是 索引。


也许看到这里,你对 共享锁  & 排他锁还只是云里雾里 ,大致知道个什么读读共享、读写互斥、写写互斥啥的。


所以,我们需要再次 从上帝视角再看一次 这两个锁 ,


红色   事务操作一


蓝色    事务操作二


image.png


那么如果你看到这里,还是对 共享锁  & 排他锁还只是云里雾里。


那我只有动手了!


实战介绍,演示 所谓的读读共享、读写互斥、写写互斥 。


在演示读读共享、读写互斥、写写互斥前, 我必须点明一点!


在这篇文章里面,我介绍了一些上 共享锁(共享读锁)、排他锁(独占写锁)的方式 。


但是 可以看到写的查询sql 都是后面加了东西的 ,  lock in share mode ,for update  .... 等。


所以我想点明的一点是,


 如果是使用 普通的查询 ,是 什么锁都没上的!

就好像平时我们经常写的


select * from table ;

select * from table where age=18;


select语句默认不会加任何锁类型

select语句默认不会加任何锁类型

select语句默认不会加任何锁类型

而排他锁,除了 select .... for update ,InnoDB引擎 默认的修改 、插入、删除(update,insert,delete)都是会给操作的相关数据 加 排他锁的 .


废话不多说,我们上才艺:


准备一些用于测试的数据。


建表:


DROP TABLE IF EXISTS `user`;

CREATE TABLE `user`  (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

 `age` int(11) NULL DEFAULT NULL,

 `sex` tinyint(1) NULL DEFAULT NULL,

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


SET FOREIGN_KEY_CHECKS = 1;


搞点模拟数据:


image.png


(id主键索引)


第一个小实践:


我们不废话,我们直接上共享读锁, 看看是不是能 符合刚才我们的理论 读读共享,读写互斥


1. 我们先给id=3这数据上个 共享读锁:


image.png


2.基于当前状况, 我们再执行一下查询语句,也是使用共享读锁的:3.那么也是基于当前情况,我们再执行一下


image.png


使用排他写锁的查询语句,可以发现 读写互斥了:


image.png


4.验证下,我们查看当前是否存在事务在等待锁:


可以从结果中看出 事务请求id 34847在等待锁:


image.png


我们再查询一下,那些事务在使用锁,


可以从结果看出,34844事务在使用S锁,也就是共享读锁;


而34847事务 在使用 X锁, 也就是排他写锁(但是由于共享读锁先上了,所以读写互斥了),所以造成了34847事务 在等待锁。


image.png5.那么如果我们一直不 COMMIT 共享读锁, 34847事务 会永无止息地等待锁吗?   那么肯定是不可能允许这种一直等待的场景的:

所以mysql会有个等待锁资源超时的机制,这种情况就会直接返回查询失败的结果。image.png


根据第一个小实践,我们得出一个很明显的结论:


当某数据上了 共享读锁 S 时, 只允许其他事务上共享读锁 S, 因为读读共享;


不允许其他事务上 独占写锁 X(除非把这个共享读锁S 释放掉),因为读写互斥。


第二个小实践:


1.我们直接给某行数据上个排他写锁 X (注意我们的事务是没有执行COMMIT的) :


image.png


2. 我们接下来去 通过共享读锁去获取数据,看看会发生什么?


image.png


这就是 独占写锁 X 的 读写互斥、写写互斥 (写写互斥的场景就不展示了).


再验证下,我们看下是不是存在事务在等待锁资源:


image.png


3.那么如果独占写锁一直不释放,其余事务时一直等待吗?


也是一样,会等待超时返回查询失败:


image.png


补充一个小实践:


1. 还是一样,先给某个数据上 独占写锁,不COMMIT:


 image.png

2. 执行普通的查询,select :


image.png


可以看到,普通的select语句能正常获取,为什么? 因为前面我们提到了:


image.png


所以我必须再啰唆一下,所谓的 读读共享,读写互斥,写写互斥 ,都是对于锁资源来说的,如果你都没有锁资源竞争,那肯定不存在什么互斥什么互斥了。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
29天前
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 , Inno DB 引搞 是 MySQL 中 高 性 能 , 高 可靠 的 存 储选项 , 宽泛 应用于要求强 复杂交易处理场景 。
64 15
|
5月前
|
SQL AliSQL 关系型数据库
MYSQL的全局锁和表锁
本文介绍了MySQL中的锁机制,包括全局锁、表级锁及其应用场景。全局锁通过`Flush tables with read lock (FTWRL)`实现,主要用于全库逻辑备份,但会阻塞更新和结构变更操作。表级锁分为显式表锁(`lock tables`)和元数据锁(MDL),前者用于控制并发访问,后者自动加锁以确保读写正确性。文章还探讨了如何安全地为小表添加字段,建议通过设置DDL等待时间或使用MariaDB/AliSQL的NOWAIT/WAIT功能避免业务阻塞。这些方法有助于在高并发场景下优化数据库性能与安全性。
118 0
|
6月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
156 1
|
10月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1689 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
6月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
111 0
|
8月前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
327 25
|
9月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
|
11月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
344 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
10月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
238 7
|
10月前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
558 7

推荐镜像

更多