mysql事务和锁的实践

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: mysql事务和锁的实践

概念解释:


脏读 : 就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据


不可重复读 : 是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据,并且提交了修改。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。


幻读 :  是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。


mysql 的隔离级别

隔离级别                      脏读      不可重复读       幻读
   未提交读(Read uncommitted)   可能      可能            可能
   已提交读(Read committed)     不可能    可能            可能
   可重复读(Repeatable read)    不可能    不可能          不可能
   可串行化(Serializable )      不可能    不可能          不可能

实验如下:


第一次采用RC隔离级别


A窗口开启事务不提交,更新数据会锁住数据。

mysql> select * from class_teacher;
id class_name teacher_id
1 aaaa 2
2 bbb 3

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update class_teacher set class_name = 'ccc' where id = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

B窗口更新数据会因为A窗口的锁提示失败。

mysql> update class_teacher set class_name = 'ddd' where id = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B窗口插入数据则成功

mysql> insert into class_teacher values (3, 'eee', 4);

Query OK, 1 row affected (0.00 sec)

RC可以避免脏读,可以避免另一个事务对已有数据进行修改。

但无法避免


可重复读:如果A窗口查询了一次数据,B窗口对该数据进行修改并且提交,A窗口再次读取数据的时候,数据已经改变。

幻读:B窗口仍能插入新数据,B窗口插入新数据后,A窗口再次读取数据,数据就会改变


第二次采用RR隔离级别


A窗口开启事务,读取数据。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from class_teacher;
id class_name teacher_id
1 ddd 2
2 bbb 3
3 eee 4
4 fff 5

4 rows in set (0.00 sec)

B窗口开启事务,修改和插入数据,提交。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update class_teacher set class_name = 'ggg' where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into class_teacher values(5, 'kkk', 6);

Query OK, 1 row affected (0.00 sec)

mysql> select * from class_teacher;
id class_name teacher_id
1 ggg 2
2 bbb 3
3 eee 4
4 fff 5
5 kkk 6

5 rows in set (0.00 sec)

mysql> commit;

A窗口再次读取数据,B窗口修改的数据不会影响到A,但新插入的数据影响到了A

mysql> select * from class_teacher;
id class_name teacher_id
1 ddd 2
2 bbb 3
3 eee 4
4 fff 5

4 rows in set (0.00 sec)

RR隔离级别可以避免脏读,可重复读和幻读(注:大部分资料都写着不能解决幻读,mysql内部其实已经解决了)

GAP锁

A窗口事务未提交

mysql> update class_teacher set class_name = 'ass' where teacher_id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

B窗口事务

mysql> insert into class_teacher values(9,'asd',2);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into class_teacher values(10, 'asd', 10000);

Query OK, 1 row affected (0.00 sec)

一条成功,一条失败,mysql内部会将teacher_id=2的数据锁住


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
44
分享
相关文章
MySQL底层概述—9.ACID与事务
本文介绍了数据库事务的ACID特性(原子性、一致性、隔离性、持久性),以及事务控制的演进过程,包括排队、排它锁、读写锁和MVCC(多版本并发控制)。文章详细解释了每个特性的含义及其在MySQL中的实现方式,并探讨了事务隔离级别的类型及其实现机制。重点内容包括:ACID特性(原子性、持久性、隔离性和一致性的定义及其实现方式)、事务控制演进(从简单的全局排队到复杂的MVCC,逐步提升并发性能)、MVCC机制(通过undo log多版本链和Read View实现高效并发控制)、事务隔离级别(析了四种隔离级别(读未提交、读已提交、可重复读、可串行化)的特点及适用场景)、隔离级别与锁的关系。
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
55 25
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
事务是MySQL中一组不可分割的操作集合,确保所有操作要么全部成功,要么全部失败。本文利用SQL演示并总结了事务操作、事务四大特性、并发事务问题、事务隔离级别。
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
MySQL进阶突击系列(04)事务隔离级别、AICD、CAP、BASE原则一直搞不懂? | 看这篇就够了
本文详细介绍了数据库事务的四大特性(AICD原则),包括原子性、隔离性、一致性和持久性,并深入探讨了事务并发问题与隔离级别。同时,文章还讲解了分布式系统中的CAP理论及其不可能三角关系,以及BASE原则在分布式系统设计中的应用。通过具体案例和图解,帮助读者理解事务处理的核心概念和最佳实践,为应对相关技术面试提供了全面的知识准备。
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
184 5
PHP与MySQL动态网站开发:从基础到实践####
本文将深入探讨PHP与MySQL的结合使用,展示如何构建一个动态网站。通过一系列实例和代码片段,我们将逐步了解数据库连接、数据操作、用户输入处理及安全防护等关键技术点。无论您是初学者还是有经验的开发者,都能从中获益匪浅。 ####
PHP与MySQL的高效交互:从基础到实践####
本文深入探讨了PHP与MySQL数据库之间的高效交互技术,涵盖了从基础连接到高级查询优化的全过程。不同于传统的摘要概述,这里我们直接以一段精简代码示例作为引子,展示如何在PHP中实现与MySQL的快速连接与简单查询,随后文章将围绕这一核心,逐步展开详细讲解,旨在为读者提供一个从入门到精通的实战指南。 ```php <?php // 数据库配置信息 $servername = "localhost"; $username = "root"; $password = "password"; $dbname = "test_db"; // 创建连接 $conn = new mysqli($se
77 0