Mysql 共享锁、排他锁 与 事务隔离级别详解

共享锁、排他锁

InnoDB 实现了两种类型的锁机制:共享锁(S)和排他锁(X)。共享锁允许一个事务读数据,不允许修改数据,如果其他事务要再对该行加锁,只能加共享锁;排他锁是修改数据时加的锁,可以读取和修改数据,一旦一个事务对该行数据加锁,其他事务将不能再对该数据加任务锁。

共享锁(S) 排它锁(X)
共享锁(S) 允许 不允许
排它锁(X) 不允许 不允许

x轴:事务A拥有的锁
y轴:事务B申请的锁

:一个事务在某一行数据上加上排它锁后,其他事务不能再在这行数据加任何锁,无法进行 update 与 delete,但是普通的 select 是可以的,因为普通的 select 不加任何锁,当 select .... from ... for update 时才会加上排它锁。

并发事务带来的问题

1.数据丢失(两个事务同时修改一行数据,其中一个事务异常回滚,导致更新失败)

解决办法:未提交读(Read Uncommitted):在事务 B 读取数据时,事务 A 读取数据加了共享锁,修改数据时加了排它锁。这种隔离级别,会导致脏读、不可重复读以及幻读。

微信截图_20200413230833.png

未提交读会导致脏读、不可重复读、幻读的原因:尽管 A 持有排它锁,避免了两个事务同时修改的情况,但是普通的 select 不持有锁,还是可以读到数据的,所以事务 A 多次修改数据,事务 B 中的查询还是可以看到的。

2.脏读(一个事务的执行期间,另一个事务读到了没有提交数据)

解决办法:已提交读(Read Committed):在事务 B 读取数据时增加了共享锁,一旦读取,立即释放锁,事务 A 读取修改数据时增加了行级排他锁,直到事务结束才释放锁。
也就是说,事务 B 在读取数据时,事务 A 只能读取数据,不能修改。当事务 B 读取到数据后,事务 A 才能修改。
这种隔离级别,可以避免脏读,但依然存在不可重复读以及幻读的问题。

微信截图_20200413223229.png

已提交读会导致不可重复读、幻读的原因:事务 B 没有提交就将锁释放,导致事务 A 可以对数据进行任意修改,修改后,事务 B 再次获取共享锁查询,查询到跟之前读取不一致的数据。

3.不可重复读(一个事务的执行期间,另一个事务连续两次读取到的值不一样)

解决办法:可重复读(Repeatable Read):在事务 B 读取数据时增加了共享锁,事务结束,才释放锁,事务 A 读取修改数据时增加了行级排他锁,直到事务结束才释放锁。
也就是说,事务 B 在没有结束事务时,事务 A 只能读取数据,不能修改。当事务 B 结束事务,事务 A 才能修改。
这种隔离级别,可以避免脏读、不可重复读,但依然存在幻读的问题。

微信截图_20200413225430.png

可重复读导致幻读的原因:可重复读是加的行级锁,而幻读是满足查询条件的前提下数据量发生变化,即发生 insert 或 delete操作。

4.幻读(一个事务的执行期间,另一个事务连续两次读取到的数量不同)

解决办法:可序列化(Serializable):在事务 A 读取数据时增加了共享锁,事务结束,才释放锁,事务 B 读取修改数据时增加了表级排他锁,直到事务结束才释放锁。

可序列化解决了脏读、不可重复读、幻读等问题,但隔离级别越来越高的同时,并发性会越来越低。

微信截图_20200413230447.png

小结

可以看出,控制数据库并发带来的问题依赖的是锁粒度的扩大来解决的。

但是隔离级别越大,并发性就越差,如果业务场景允许的情况下,适当减小隔离级别是处理并发的一种手段。


7 声望
3 粉丝
0 条评论
推荐阅读
Liquibase 数据库版本管理工具:3. changeSet 变更集详解
上篇文章中详细了介绍了一下changelog 文件的使用,本篇文章将详细说一下 changeSet 变更集 中的细节,以及通常的使用方式 1.变更集分类 changeSet 分为 6类: add create drop rename sql other 官方文档:[链接...

哒哒队长1阅读 4k评论 1

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

【golang】sync.Mutex互斥锁的实现原理
sync.Mutex 由两个字段 state 和 sema 组成。其中 state 表示当前互斥锁的状态,而 sema 是用于控制锁状态的信号量。

去去10023阅读 6.7k

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山5阅读 1.1k评论 3

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.8k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 656

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.4k

封面图
7 声望
3 粉丝
宣传栏