文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL表锁、行锁、排它锁及共享锁的使用详解

2024-04-02 19:55

关注

前言

事务隔离级别的实现原理:简单来说就是各种锁机制和MVCC多版本并发控制

我们学习知识的时候,需要了解知识点出现的原因,什么情况下能用到这个知识

我们说到事务,就得说到事务的ACID特性,说到隔离性的时候,事务要能够允许并发执行,并发执行为了同时保证数据的安全性,一致性和并发的效率,就需要设置事务的隔离级别

一、事务隔离机制的选择

二、表级锁&行级锁

表级锁:对整张表加锁。开销小(因为不用去找表的某一行的记录进行加锁,要修改这张表,直接申请加这张表的锁),加锁快,不会出现死锁;锁粒度大,发生锁冲突的概率高,并发度低

行级锁:对某行记录加锁。开销大(需要找到表中相应的记录,有搜表搜索引的过程),加锁慢,会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度高

InnoDB存储引擎支持事务处理,表支持行级锁定,并发能力更好

  1. InnoDB行锁是通过给索引上的索引项加锁来实现的,而不是给表的行记录加锁实现的,这就意味者只有通过索引条件检索数据,InnoDB才使用行级锁,否则InnoDB将使用表锁
  2. 由于InnoDB的行锁实现是针对索引字段添加的锁,不是针对行记录加的锁,因此虽然访问的是InnoDB引擎下表的不同行,但如果使用相同的索引字段作为过滤条件,依然会发生锁冲突,只能串行进行,不能并发进行
  3. 即使SQL中使用了索引,但是经过MySQL的优化器后,如果认为全表扫描比使用索引效率高,此时会放弃使用索引,因此也不会
    使用行锁,而是使用表锁,比如对一些很小的表,MySQL就不会去使用索引

三、排它锁(Exclusive)和共享锁(Shared)

读读(SS)之间是可以兼容的,但是读写(SX、SX)之间,写写(XX)之间是互斥的

对事务加X和S锁之间有以下的关系:

显示加锁:select … lock in share mode强制获取共享锁,select … for update获取排它锁

1. 测试不同事务之间排它锁和共享锁的兼容性

我们先查看表的SQL以及内容

查看隔离级别:

首先开启一个事务,给id=7的数据加上排它锁

在用另一个客户端开启事务

我们用另一个事务的服务线程给id=7的数据加上排它锁,阻塞了

我们尝试给id=7的数据加上共享锁,还是阻塞了

总结:不同事务之间对于数据的锁,只有SS锁可以共存,XX、SX、XS都不能共存

2. 测试行锁加在索引项上

其实行锁是加在索引树上的

用表的无索引字段作为过滤条件

事务2现在同样想获取这条记录的排它锁,可想而知地失败了;那现在事务2获取chenwei的记录的排它锁,试试能不能成功

InnoDB是支持行锁的,刚才以主键id为过滤条件时,事务1和事务2获取不同行的锁是可以成功的。然而现在我们发现获取name为chenwei的排它锁也获取不到了,这是为什么?我们解释一下:

InnoDB的行锁是通过给索引项加锁来实现的,而不是给表的行记录加锁实现的

而我们用name作为过滤条件没有用到索引,自然就不会使用行锁,而是使用表锁。这就意味着只有通过索引检索数据,InnoDB才使用行级锁,否则InnoDB都将使用表锁!!!

我们给name字段加上索引

我们发现,给name加上索引后,两个事务可以获取到不同行的排它锁(for update),再一次证明了InnoDB的行锁是加在索引项上的

因为现在name走的是索引, 通过zhangsan在辅助索引树上找到它所在行记录的id是7,然后到主键索引树上,获取对应行记录的排他锁(个人猜测应该是辅助索引树和主键索引树相应的记录都加了锁)

四、串行化隔离级别测试

(所有的事务都使用排它锁或共享锁,不需要用户手动加锁)

设置串行化隔离级别

两个事务可以同时获取共享锁(SS共存)


现在让事务2插入数据

此时由于insert需要加排它锁,但由于事务1已经对整张表添加了共享锁,事务2无法再对表成功加锁(SX不共存)

rollback一下

因为我们给name加上了索引,以上的select相当于给name为zhangsan的数据加上了行共享锁

事务2 update

事务2不能update,因为此时已经被事务1的共享锁锁住了整个表

事务2在辅助索引树上找zhangsan,找到对应的主键值,然后去主键索引树找到相应的记录,但是发现这行记录已经被共享锁锁住了,事务2可以获取共享锁,但是不能获取排他锁

我们用主键索引id试试能不能update

依然阻塞住了,虽然我们where后面的字段现在使用的id而不是name,但是name也是通过辅助索引树找到对应的主键,再到主键索引树上找相应的记录,而主键索引树上的记录加了锁(个人猜想应该是辅助索引树和主键索引树对应的数据都加了锁)

我们update id=8的数据,成功了。因为我们select的时候,只是给id=7的数据加上了行锁,我们操作id=8的数据当然可以成功

有索引,则使用行锁;没有索引,则使用表锁。

表级锁还是行级锁说的是锁的粒度,共享锁和排他锁说的是锁的性质,不管是表锁还是行锁,都有共享锁和排他锁的区分

总结

到此这篇关于MySQL表锁、行锁、排它锁及共享锁使用的文章就介绍到这了,更多相关MySQL表锁、行锁、排它锁和共享锁内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯