Mysql之锁与事务知识要点小结

Mysql之锁与事务

平时的业务中,顶多也就是写写简单的sql,连事务都用的少,对锁这一块的了解就更加欠缺了,之前一个大神分享了下mysql的事务隔离级别,感觉挺有意思的,正好发现一个很棒的博文,然后也收集了一些相关知识,正好来学习下,mysql中锁与事务的神秘面纱,主要内容包括

  1. 共享锁和排它锁的区别以及适合范围

  2. mysql的表锁和行锁的区别

  3. 怎么判断一个sql是否执行了锁,执行的是表锁还是行锁

  4. 事务是什么,怎么用

  5. 事务的特性ACID

  6. 事务的隔离级别 (RU, RC, RR, SER)

  7. 如何查看mysql使用的隔离级别

I. 锁

在学习多线程时,我们也经常会遇到锁这个东西,那个时候谈的比较多的是乐观锁和悲观锁,那这两种锁和DB中常说的共享锁和独占锁有什么区别呢?先给出我们已知的乐观锁和悲观锁定义

  • 乐观锁:多线程中的CAS就是一种乐观锁,实际上不加锁,先尝试去执行,如果失败则重试(或者根据失败策略进行处理)

  • 悲观锁:上锁,一次只能有一个线程访问,其他的都只能等待

1. 共享锁和排它锁

a. 共享锁

突出在共享这个关键词上,顾名思义,表示这个锁可以多人共享,一般又可以称为读锁(S锁)

在DB中,读锁表示所有的读取数据的小伙伴都不会被锁阻塞,可以放心大胆的获取数据,专业一点的说法就是同一时刻,允许多个连接并发的读取同一资源

b. 排它锁

排它,表示当某个人持有这个锁之后,其他的人再来竞争锁就会失败,只能等待锁释放, 又称为写锁(X锁)

在DB中,写锁表示同一时刻,只能有一个小伙伴操作,其他的不管是读还是写,都得排队,专业说法是写锁会阻塞其他的读锁或写锁请求,确保同一时刻只能有一个连接可以写入资源,并防止其他连接读取或者写资源

c. gapLock 和 next key lock

  • next key lock 主要是范围匹配的场景下,会锁某一个范围区间

  • gapLock 主要用来锁边界

如下面的case(说明,columnA是非唯一索引,RR隔离级别)

  • where columnA between 10 and 30, next key lock 确保不会在10, 30 之内插入新的数据行

  • where columnA = 10, gap lock 确保不会再次插入一个columnA=10的行

2. 表锁和行锁

对于DB的操作,通常会出现两种情况,一个是锁表,一个锁行

  • 表锁:表示整个表被某一个连接占用了写锁,导致其他连接的读锁或者写锁都会阻塞;影响整个表的读写

  • 行锁:表示表中某些行被某个连接占用了写锁,但是其他行,依然可以被其他的连接请求读锁、写锁;仅影响被锁的那些行数据

那么一个问题就来了,什么sql会导致行锁,什么会导致写锁?甚至我们如何判断一个sql是否会请求锁,请求的是读锁还是写锁呢?

3. 如何使用锁

上面一节抛出了问题,那么现在就是来看下如何使用和分析锁了,首先我们是我们最常见的几个sql

  • select

  • update

  • delete

  • insert

其中很容易得出的结论是 update, delete, insert 三个涉及到写锁;而且这种操作绝大部分的场景是操作具体的某些行(想想为什么?),所以更常见的是行锁

select读操作则有点特殊

a. select分析

MVCC(multiple-version-concurrency-control)是个行级锁的变种,它在普通读情况下避免了加锁操作,因此开销更低。即下面这个没有读锁也没有写锁

快照读,不加锁

1
select * from table ...

当前读,select 语句可以指定读锁和写锁,如下

说明,insert, update, delete 也是当前读,理由如下:

1.update和delete操作流程分解:

  • 首先通过where条件查询到第一个满足的记录,并加锁

  • 对这条记录进行更新,再读取下一条记录

  • 对记录更新,继续读下一条直到完毕

2.insert操作流程分解:

  • unique key 冲突检测,会有一个当前读

  • 无冲突时,插入

b. sql实例分析

在分析上面的sql之前,需要明确几个前提:

  • id是否为主键(id是否有索引)

  • 系统的隔离级别(隔离级别是什么东西可以先看下下文介绍)

分别说明:

case1: 主键+RC级别

  • sql1不加锁,MySQL是使用多版本并发控制的,读不加锁

  • sql2加写锁(即X锁),只锁 id=10这一行

case2: 唯一索引+rc级别

  • sql2加写锁,如下图的case,就两把锁,一个对应于id unique索引上的id = 10的记录,另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录

case3: id非唯一索引+RC

  • sql2加写锁,如下图的case,会有四个写锁

case4: 无索引+RC

  • sql2分析:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上写锁(X锁)。

  • 但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省

case5: 主键+RR

加锁同case1

case6: 唯一索引+RR

加锁同case2

case7: 非唯一索引+RR

RR级别不允许出现幻读,简单来说,在加锁的过程中,不允许在新增or修改满足条件的记录

即下图中,除了图三中类似的x锁之外,还会新增一个gap锁,这个gap锁主要确保那几个位置上不能插入新的记录

case8: 无索引+RR

  • 在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作

case9: Serializable级别

  • sql2: Serializable隔离级别。对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致

  • SQL1: 在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC


II. 事务

事务可谓是db中非常重要的一个知识点了,接下来我们的目标就是弄懂什么是事务,怎么使用事务,以及事务与锁之间的关联是怎样的

说明:本文的分析主要是以mysql的innordb存储引擎为标准

1. 定义

事务就是一组原子性的sql,或者说一个独立的工作单元。

事务就是说,要么mysql引擎会全部执行这一组sql语句,要么全部都不执行(比如其中一条语句失败的话)。

2. ACID特性

a. A:atomiciy 原子性

一个事务必须保证其中的操作要么全部执行,要么全部回滚,不可能存在只执行了一部分这种情况出现。

b. C:consistency一致性

数据必须保证从一种一致性的状态转换为另一种一致性状态。

c. I:isolation 隔离性

在一个事务未执行完毕时,通常会保证其他Session 无法看到这个事务的执行结果

d. D:durability 持久性

事务一旦commit,则数据就会保存下来,即使提交完之后系统崩溃,数据也不会丢失

3. 隔离级别

前面在分析锁的sql时,就提到了隔离级别,通常有四种: RU, RC, RR, Serializable

在说明这个之前,先了解几个概念

a. 基本概念

  • 脏读:读取到一个事务未提交的数据,因为这个事务最终无法保证一定执行成功,那么读取到的数据就无法保证一定准确

  • 不可重复读:简单来说就是在一个事务中读取的数据可能产生变化,同样的sql,在一个事务中执行多次,可能得到不同的结果

  • 幻读:会话T1事务中执行一次查询,然后会话T2新插入一行记录,这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索,但是此时却看到了事务T2刚才插入的新行

  • 加锁读:select * from table ... 的执行是否加了读锁 (这个可以参考上面的sql加锁分析)

b. RU: Read Uncommited 未提交读

事务中的修改,即使没有提交,对其他会话也是可见的,即表示可能出现脏读,一般数据库都不采用这种方案

c. RC: Read Commited 提交读

这个隔离级别保证了一个事务如果没有完全成功(commit执行完),事务中的操作对其他会话是不可见的,避免了脏读的可能

但是可能出现不可重复度的情况,举例说明:

  • 会话T1, 执行查询 select * from where id=1,第一次返回一个结果

  • 会话T2, 执行修改 update table set updated=xxx where id=1 并提交

  • 会话T1,再次执行查询 select * from where id=1,这次返回的结果中update字段就和前面的不一样了

实际的生产环境中,这个级别用的比较多,特意查了下公司的db隔离级别就是这个

一个RC级别的演示过程:

  • 会话1,开启事务,查询

  • 会话2,开启事务,更新DB,提交事务

  • 会话1,再次查询,提交事务

  • 从下面的实际演示结果可以知道,会话1,同一个sql,两次执行的结果不同

相关的sql代码如下:

d. RR: Repeatable Read 可重复度

一个事务中多次执行统一读SQL,返回结果一样。 这个隔离级别解决了脏读的问题,幻读问题

实例演示解决脏读的过程(将上面的过程同样来一次)

  • 发现不管会话1同一个sql,返回的结果都是相同的

e. Serializable 可串行化

最强的隔离级别,通过给事务中每次读取的行加锁,写加写锁,保证不产生幻读问题,但是会导致大量超时以及锁争用问题。

f. 常用命令

  • 查看当前会话隔离级别: select @@tx_isolation

  • 查看系统当前隔离级别: select @@global.tx_isolation

  • 设置当前会话隔离级别: set session transaction isolation level read committed;

  • 设置系统当前隔离级别: set global transaction isolation level read committed;

  • 命令行,

    • 开始事务: start transactioin;

    • 提交: commit;

4. 使用姿势

前面演示事务隔离级别的时候,给出的实例就演示了事务的使用姿势,一般作为三步骤:

  • 开始事务 start transaction;

  • 执行你的业务sql

  • 提交事务 commit;

我们现在演示以下一个事务中,读锁、写锁对另一个事务的影响

a. 读锁的影响

我们采用mysql默认的RR级别进行测试,userId为主键

实际执行演示:

b. 写锁的影响

实际执行演示:

c. 小结

  • 读锁,会阻塞其他请求写锁的sql执行

  • 写锁,会阻塞其他读锁和写锁的sql执行

  • 事务只有在提交之后,才会释放锁

  • 额外注意,上面事务在提交之后才会释放锁,因此如果两个事务循环依赖锁时,可能发生死锁


III. 小结

锁和事务可谓是db中非常重要的知识点了,在我们实际的编码过程中(一般针对mysql, innordb存储引擎,rr隔离级别),做出下面的一些总结

1. sql分析

  • select * from table where xxx; (读快照,一般不加锁)

  • select * from table where xxx lock in share mode; (读锁,会阻塞其他的写锁请求,但其他的读锁请求没有影响)

  • select * from table where xxx for update; (写锁,会阻塞其他的读写请求)

  • update tableName set xxx (写锁)

  • insert (写锁)

  • delete (写锁)

2. 事务

简单来讲,事务就是一组sql,要么全部执行成功,要么全部失败

四个特性: A(原子性)C(一致性)I(隔离性)D (持久性)

四种隔离级别:(mysql 默认采用的是RR级别)

隔离级别脏读不可重复读幻读加锁读
read uncommited可能可能可能
read commited不可能可能可能
repeatable read不可能不可能不可能
serializable不可能不可能不可能

使用姿势:

1
2
3
4
5
start transaction;

-- xxx 具体的sql

commit;

IV. 其他

参考

  • 深入理解Mysql——锁、事务与并发控制

  • MySQL 加锁处理分析

个人博客: 一灰灰Blog

基于hexo + github pages搭建的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

声明

尽信书则不如,已上内容,纯属一家之言,因本人能力一般,见识有限,如发现bug或者有更好的建议,随时欢迎批评指正

  • 微博地址: 小灰灰Blog

  • QQ: 一灰灰/3302797840

扫描关注

版权声明:本文为liuyueyi25原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/liuyueyi25/article/details/103369731

智能推荐

laravel框架的课堂知识点概总

1. MVC 1.1 概念理解 MVC全名是Model View Controller,是模型(model)-视图(view)-控制器(controller)的缩写,一种软件设计典范,用一种业务逻辑、数据、界面显示分离的方法组织代码,将业务逻辑聚集到一个部件里面,在改进和个性化定制界面及用户交互的同时,不需要重新编写业务逻辑 MVC 是一种使用 MVC(Model View Controller ...

Unity人物角色动画系统学习总结

使用动画系统控制人物行走、转向、翻墙、滑行、拾取木头 混合树用来混合多个动画 MatchTarget用来匹配翻墙贴合墙上的某一点,人物以此为支点翻墙跳跃 IK动画类似于MatchTarget,控制两只手上的两个点来指定手的旋转和位置,使得拾取木头时更逼真 创建AnimatorController: 首先创建一个混合树,然后双击 可以看到该混合树有五种状态机,分别是Idle、WalkForward、...

Composer 安装 ThinkPHP6 问题

Composer 安装 ThinkPHP6 问题 先说说问题 一.运行环境要求 二.配置 参考: ThinkPHP6.0完全开发手册 先说说问题 执行ThinkPHP6的安装命令 遇到问题汇总如下: 看提示是要更新版本,执行命令更新。 更新之后,再次安装ThinkPHP,之后遇到如下问题。 尝试了很多方法,依然不能解决。其中包括使用https://packagist.phpcomposer.com...

Spring Boot 整合JDBC

今天主要讲解一下SpringBoot如何整合JDBC,没啥理论好说的,直接上代码,看项目整体结构 看一下对应的pom.xml 定义User.java 定义数据源配置,这里使用druid,所以需要写一个配置类 上面指定druid的属性配置,和用户登录的账号信息以及对应的过滤规则: 下面定义数据访问接口和对应的实现: 数据访问层很简单,直接注入JdbcTemplate模板即可,下面再看对应的servi...

html鼠标悬停显示样式

1.显示小手:     在style中添加cursor:pointer 实现鼠标悬停变成小手样式     实例:         其他参数: cursor语法: cursor : auto | crosshair | default | hand | move | help | wait | tex...

猜你喜欢

Yupoo(又拍网)的系统架构

Yupoo!(又拍网) 是目前国内最大的图片服务提供商,整个网站构建于大量的开源软件之上。以下为其使用到的开源软件信息: 操作系统:CentOS、MacOSX、Ubuntu 服务器:Apache、Nginx、Squid 数据库:MySQLmochiweb、MySQLdb 服务器监控:Cacti、Nagios、 开发语言:PHP、Python、Erlang、Java、Lua 分布式计算:Hadoop...

创建一个Servlet项目流程(入门)

版本 IDEA 2020.2 JDK1.8 apache-tomcat-9.0.36 项目流程 一、IDEA中新建JaveEE项目 项目起名,选择项目存放地址,点击finish创建成功 进入项目后,右键选择项目,选择add Framework Support 选择Web Application,点击OK 此时项目文件夹 在WEB-INF下创建两个目录classes和lib 按ctrl+alt+sh...

Docker部署SpringCloud ELK+RabbitMQ日志

Docker部署SpringCloud ELK+RabbitMQ日志  Im_Coder 原文:https://www.jianshu.com/p/f773f23096a9 一、效果图 image.png 二、ELK是什么? ELK由ElasticSearch、Logstash和Kiabana三个开源工具组成。 其中Elasticsearch是个开源分布式搜索引擎,它的特点有:分布式,索...

[译]高性能缓存库Caffeine介绍及实践

概览 本文我们将介绍Caffeine-一个Java高性能缓存库。缓存和Map之间的一个根本区别是缓存会将储存的元素逐出。逐出策略决定了在什么时间应该删除哪些对象,逐出策略直接影响缓存的命中率,这是缓存库的关键特征。Caffeine使用Window TinyLfu逐出策略,该策略提供了接近最佳的命中率。 添加依赖 首先在pom.xml文件中添加Caffeine相关依赖: 您可以在Maven Cent...

Django开发微信公众号

一、微信公众号的准备: 1. 注册 访问地址:https://mp.weixin.qq.com/  按照提示注册即可  注意:本文样例使用个人公众号,由于个人公众号没有接口权限,自定义菜单无法进行开发,同学们不要注册错!  2. 配置 需要准备好自己的服务器地址,可以使用腾讯云阿里云京东云等等皆可,没有域名用IP也可以。  在公众平台官网的开发-基本设置页面...