Mysql隔离级别，锁的原理

最新推荐文章于 2024-10-08 21:32:03 发布
最新推荐文章于 2024-10-08 21:32:03 发布
阅读量155 收藏
点赞数
文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_31630335/article/details/115289960
版权

Mysql隔离级别，锁的原理

抛开剂量谈毒性，就是耍流氓。

抛开存储引擎和隔离级别谈锁，也是耍流氓。

所以，先介绍一下存储引擎和隔离级别。

1.存储引擎

MyISAM：每个表有三个文件，.frm文件用于存储表结构，.myd文件用于存储数据，.myi文件用于存储索引

不支持事务，不支持外键，不支持聚集索引，不支持行锁

Innodb：每个表有两个文件，.frm文件用于存储表结构，.idb文件用于存储表的索引和数据

InnoDB为什么推荐使用自增ID作为主键？

    答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树的频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

2.隔离级别

2.1.脏读

脏读就是指一个事务访问到了另一个事务还未提交的数据

将隔离级别设置为read uncommitted(读未提交)

A：启动事务，查询数据，此时数据为初始状态

B：启动事务，更新数据，但不提交

A：再次读取数据，发现数据已经被修改了，这就是所谓的“脏读”

 

2.2.不可重复读

不可重复读是指在一个事务内两次读到的数据是不一样的（侧重修改数据）

将隔离级别设置为read committed(已提交读)

A：启动事务，查询数据，此时数据为初始状态

B：启动事务，更新数据，提交事务

A：再次读取数据，发现数据已发生变化，说明B提交的修改被事务中的A读到了，这就是所谓的“不可重复读”

2.3.幻读

幻读是指在一个事务内两次读取到的数据数量不一致（侧重插入数据）

A：启动事务，查询数据，此时数据为初始状态

B：启动事务，插入数据，提交事务

A：再次查询、更新数据，发现数据量不一致，这就是所谓的“幻读”

第二次查询，数据量没有变化，更新的时候，数据量发生变化，是因为MVCC解决了部分幻读的问题

2.4.MVCC (多版本并发控制) 

MVCC作用于RC和RR隔离级别，用于解决数据库的并发读写操作

MVCC解决了不可重复读和部分幻读的问题

MVCC的实现原理：

2.4.1

  • 6字节的事务ID（DB_TRX_ID）
  • 7字节的回滚指针（DB_ROLL_PTR）

事务100插入初始数据

 

事务200更改数据

当事务200更改该行的值时，会进行如下操作：

  • 用排他锁锁定该行
  • 记录redo log
  • 把该行修改前的值copy到undo log
  • 修改当前行的值，填写事务编号，使回滚指针指向undo log中的修改前的行
  • 当事务正常提交时Innbod只需要更改事务状态为COMMIT即可，不需做其他额外的工作
  • 而Rollback则稍微复杂点，需要根据当前回滚指针从undo log中找出事务修改前的版本，并恢复
  • Innodb中存在purge线程，它会查询那些比现在最老的活动事务还早的undo log，并删除它们，从而保证undo log文件不至于无限增长。

2.4.2

2.4.3

为了判断版本链中的哪个版本是当前事务可见的，提出了ReadView的概念，它包含4个主要内容

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表；

min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值；

max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值；

creator_trx_id：表示生成该ReadView的事务的事务id。

A•如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，该版本可以被访问。
B•如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，该版本不可以被访问
C•如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
D•如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断

RC隔离级别下

每次查询都新建一个read view

事务100第一次查询， m_ids{100，200}，版本链{90}，90符合A，可见

事务100第二次查询，m_ids{100，200}，版本链{90，200}，200符合D而且在列表中，不可见，然后看90

事务100第三次查询，m_ids{100}，max_trx_id=201，版本链{90，200}，200符合D而且不在列表中，可见

事务100第四次查询，m_ids{100}，max_trx_id=301，版本链{90，200，300}，300符合D而且不在列表中，可见

事务100第五次查询，m_ids{100}，版本链{90，200，300，100}，100符合C，可见

RR隔离级别下

一个事务中每次查询使用一个read view：m_ids{100，200}，max_trx_id=201

事务100第一次查询，版本链{90}，90符合A，可见

事务100第二次查询，版本链{90，200}，200符合D而且在列表中，不可见，然后看90

事务100第三次查询，版本链{90，200}，200符合D而且在列表中，不可见，然后看90

事务100第四次查询，版本链{90，200，300}，300符合B，不可见，然后看200

事务100第五次查询，版本链{90，200，300，100}，100符合C，可见

3.锁

共享锁、排他锁、意向共享锁、意向排他锁、记录锁、间隙锁、临键锁、插入意向锁、自增锁

3.1.共享锁（S）

共享锁也称读锁，行级别的锁，当前事务加共享锁之后，其他事务可以加共享锁，但不能加排他锁

加锁方式：select colname from tablename where ... lock in share mode;

                  insert into tablename select colname from tablename2 (对tablename2自动加共享锁)

A事务对数据加共享锁之后，B事务也可以对同一数据加共享锁，但加排他锁超时失败

3.2.排他锁（X）

排他锁也称写锁，行级别的锁，当前事务加排他锁之后，其他事务不能加共享锁和排他锁

加锁方式：select colname from tablename where ... for update;

                 insert ... delete... update... 

3.3.意向共享锁（IS）

表级别的锁，加共享锁之前，先要获取意向共享锁

3.4.意向排他锁（IX）

表级别的锁，加排他锁之前，先要获取意向排他锁

3.5.记录锁（Record Lock）

锁精确加在某一行上，基于主键和唯一索引

  select * from test where id=5 for update；//该句只锁id=5这一行

3.6.间隙锁（Gap Lock）

只在RR隔离级别下生效

锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前（负无穷，first-key）或最后一个索引之后(last-key，正无穷）的无限空间。

下表中的间隙锁(-∞,3),(3,5)......(13,+∞)

3.7.临键锁（Next-Key Lock）

基于非唯一索引的，每个非唯一索引上都有一把临键锁

只在RR隔离级别下生效

记录锁与间隙锁组合起来用就叫做临键锁

下表中存在的临键锁：(-∞,3],(3,5],......(13,+∞)

3.8.插入意向锁（Insert Intention）

插入意向锁是一种Gap锁，不是意向锁

插入的时候会获取当前间隙的gap锁，和插入行的X锁

假如有一个索引间隙4-7，不同的事务分别插入5和6，每个事务都会产生一个（4，7）插入意向锁，所以插入意向锁和插入意向锁是兼容的

插入意向锁和间隙锁是不兼容的

4.案例分析

id是主键，name是普通索引

4.1.唯一键+存在记录

select * from test where id = 5 for update;

只锁id=5的这一行

4.2.唯一键+不存在记录

select * from test where id = 18 for update;

RR隔离级别：获得间隙锁（16，20），id为17，18，19的不能插入，id为16，20的可以修改

RC隔离级别：只锁猪id=18的行锁

4.3.普通索引+存在记录

select * from test where num = 7 for update;

RR隔离级别：获得临键锁(5,7]和区间锁(7,9),num为6，8的不能插入，num为5，9的可以修改

RC隔离级别：获得num=7的行锁

4.4.普通索引+不存在记录

select * from test where num = 12 for update;

RR隔离级别：获得间隙锁（10，13）

RC隔离级别：获得num=12的行锁

5.死锁

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

案例1

循环对同一资源加锁

解决办法：两个事务的业务顺序一致

案例2

S锁X锁

解决办法：把S锁改成X锁

案例3

Gap锁

解决办法：大事务化小事务，或者降低隔离级别

6.锁状态查询

https://www.sohu.com/a/253338562_495675

http://www.bubuko.com/infodetail-3344103.html

 

 

参考：https://tech.meituan.com/2014/08/20/innodb-lock.html

          https://blog.csdn.net/sofia1217/article/details/50778906

          

 

 

庚子尽，辛丑祥
关注
Mysql事务隔离级别原理实例解析
09-08
MySQL事务隔离级别是数据库管理系统中确保事务之间正确交互的重要机制，它主要涉及到四个级别：读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）。...
Innodb中事务隔离级别和锁的关系
吨吨吨
03-14 234
InnoDB中事务隔离级别和锁的关系 事务的四大特征 原子性A 一致性C 隔离性I 持久性D 并发事务带来的问题 脏读。 丢失修改 不可重复读 幻读 事务隔离级别 读未提交（Read uncommitted）RU 读提交（Read committed）RC 可重复读（Repeatable read）RR 可串行化（Serializable） 两段锁 加锁阶段：在该阶段可以进行加锁操作...
Mysql
huanglei_hacker的博客
11-18 116
mysql acid特性详细分析： https://www.cnblogs.com/kismetv/p/10331633.html 你们先上线上怎么分库分表的？ 库：取模 表：整除后在取模 32*32 =1024张表 4台服务器 每台8个库 线上预估能存50个亿的数据 目前存了20亿。。 如果想提高写并发 可以扩容机器，，将每台机器只放一个库即可。。。 mysql主从复制原理： 1、主库和从库各有一个io线程来通信做主从同步 2、主库io线程从..
MySql（八）：事务实践
weixin_40074110的博客
05-26 351
事务： 事务是不可能分隔的逻辑工作单元，需要保证原子性、隔离性、一致性、持久性的一个或者多个数据库操作称为一个事务。 事务具备四个属性(ACID)： 例：小明向小强转账10元。 原子性(Atomicity): 事物是一个完整的操作，各个操作不可分，要么都执行，要么都不执行。所以我们把要么全部做，要么全部不做的规则称之为原子性。 转账是一个不可分割操作，要么成功，要么失败，不可能存在中间状态(小明转出了，小强没有转入)。 隔离性(Isolation): 对数据修改的并非事...
RC隔离级别下的GAP间隙锁
Thxxxxxx
05-11 3087
delete from t1 where id = 10; RC RR id主键 结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。 id唯一索引 此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所...
mysql的mvcc和readview
cindyxue_17的博客
02-22 1872
隔离级别标准定义： read uncommited 一个事务读取到了另一个事务未提交的结果（可能发生回滚），叫脏读，这会带来脏读、幻读、不可重复读问题 read commited 一个事务a读取到了另一个事务b最新提交的结果，使得事务a两次读取的结果不同，其避免了脏读，会出现不可重复读，幻读，通过版本链（mvcc）和readview实现。 repeatable read（mysql的默认隔离级别） 一个事务第一次读过某条记录后，另一个事务修改提交了该条记录后，事务a读取到的还是第一次的值，这就是可重复读
关于Mysql隔离级别、锁与MVCC介绍
09-09
MySQL数据库在处理并发事务时，采用了多种机制来确保数据的一致性和隔离性，其中关键的概念包括隔离级别、锁以及多版本并发控制（MVCC）。理解这些机制有助于优化数据库性能和解决并发冲突。 首先，隔离级别是...
MySQL事务及Spring隔离级别实现原理详解
09-08
在MySQL中，可以通过`SHOW VARIABLES LIKE 'tx_isolation'`或`SHOW VARIABLES LIKE 'transaction_isolation'`查看当前的隔离级别，并使用`SET TRANSACTION ISOLATION LEVEL`命令来改变它。在Spring框架中，`@...
mysql原理之隔离级别1
08-08
MySQL 数据库中的事务隔离级别是确保数据库在高并发环境下数据一致性的重要机制。SQL 标准定义了四个隔离级别，它们分别是 Read Uncommitted、Read Committed、Repeatable Read 和 Serializable，每种级别都针对不同...
Innodb事务中的锁实现（转）
a1290123825的博客
01-30 293
目录 一次封锁or两段锁？ MySQL中锁的种类 MVCC在MySql的InnoDB中的实现 1、RR级别的事务隔离级别真的无法解决幻读吗？答案是否 2、Mysql如何做到RR事务隔离级别下不会出现幻读（Next-Key锁=GAP锁+行锁） Serializable 一次封锁or两段锁？ 因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方...
鲁班到家上门安装维修系统源码开发之结构功能解析
weixin_43744973的博客
10-08 909
鲁班到家上门安装维修系统的源码开发是一个复杂但富有成效的过程。通过详细的需求分析、技术选型、功能模块设计以及前后端分离开发，该系统不仅提升了服务效率，还通过数字化管理优化了用户体验。未来，该系统有望在智能家居售后服务领域发挥更加重要的作用，引领行业向更加高效、专业、透明的方向发展。
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1396
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
ATAM需求说明-系统架构师（七十六）
ke1ying的博客
10-08 791
1体系结构权衡分析法ATAM(Architecture Trade Off Analyzer Method)是一种常见的结构权衡分析法，该框架主要关注系统的（），针对性能、安全性、可用性和可修改性，在系统开发前进行分析、评价和这种。A共享数据库的集成方式通常将应用程序的数据存储在一个共享数据库中，通过制定统一的数据库模式来处理不同应用集成需求。场景和需求的收集，架构视图和场景的实现，属性模型的构造和分析，架构评价和折中。7企业信息资源集成管理的前提是对企业（）的集成，其核心是对企业（）的集成。
World of Warcraft [CLASSIC][80][Grandel] Call to Arms: Victory in Wintergrasp
qq 117791303
10-02 348
Wintergrasp 冬拥湖 120 VS 120
同城O2O系统源码与跑腿配送平台的架构设计与开发方案详解
meihusdk的博客
10-08 406
同城O2O系统与跑腿配送平台的开发，既是技术的挑战，也是商业机会的探索。通过合理的架构设计与清晰的开发方案，开发者可以构建出高效、稳定且易于扩展的平台，为用户提供更为便捷的服务。
思迅商云开启货号修改语句
xushugang007的博客
10-03 240
insert into t_sys_system (sys_var_id,sys_var_name,sys_var_value,is_changed,sys_ver_flag) values('itemnoisedit','货号、供应商、客户编码修改','1','否','1')use hbposv10 --分店数据库名改成： hbposv10_branch。--开启 货号、供应商、客户编码修改。
一个真实可用的登录界面！
Q_w7742的博客
10-05 4096
至此，所有的代码运行之后应该可以实现输入账号密码，与数据库比对，重定向到欢迎界面这样的过程，可以将这个代码放在任何一个需要登录的网页中（自夸一下），当然还有很多很多的内容需要补充，留给你们自己发挥~这里如果php.ini设置不好可能会出现问题，找不到mysqli这个拓展，需要手动处理一下，当然这里不赘述。并且加密的过程被我注释掉了，防止新手搞不明白密码。首先需要一个静态网页，在前面已经讲了很多有关的代码，这里也是常规的，但是这里我们需要post到一个php页面，请注意。连接成功后，输入下面的代码，
2024最新 Navicat Premium 17 简体中文版安装图文详细教程
qq_25156781的博客
10-07 370
Navicat 17 引入了一系列新特性，旨在提升用户体验和工作效率。以下是一些值得关注的新功能：‌模型工作区的全面重新设计‌：包含了增强的图表设计、更强大的同步工具、数据字典支持等多项功能。这有助于在一个工作区中创建多个模型，使在单个图表中说明不同的模型对象成为可能，从而简化了复杂系统的浏览和理解‌。‌数据剖析‌：数据查看器现在集成了数据分析工具，为数据提供了一个可视化且全面的视图，增强了数据的完整性‌。‌数据字典‌：为各种服务器平台中的数据库内的每个元素提供文档和描述。
gbase8s数据库实现黑白名单的几种方案
最新发布
wangyx01的博客
10-08 154
实际效果为 162 与163 机器的 root 用户可以连接数据库，其他ip 或者非root用户不能连接，从 root切换成 wangyx用户时不需要输入密码。1）在部分环境中由于DNS反向解析策略很有可能将实际IP解析成bogon,需要额外关注下。2）若 sysdbopen 未知异常会导致数据库无法连接，最简单的处理方案为。2、使用数据库 TRUSTED CONTEXT 机制。更多语法以及配置细节可参考gbase8s语法手册。4 某些高版本中支持在参数中直接配置黑白名单。3 利用数据库现有机制实现。
mysql隔离级别实现原理
03-11
MySQL的隔离级别实现原理是通过锁机制来实现的。MySQL支持四种隔离级别：读未提交、读已提交、可重复读和串行化。在读未提交隔离级别下，一个事务可以读取另一个事务未提交的数据，可能会导致脏读问题。在读已提交...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值