linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL各类SQL语句的加锁机制

时间:2018-06-04  来源:未知  作者:admin666
MySQL把读操作分为两大类:锁定读和非锁定读(即locking read和nonlocking read),所谓非锁定读就是不对表添加事务锁的读操作,如Repeatable Read和Read Committed隔离级别下的select语句(可能脏读也算?)。MySQL的一致性非锁定读是通过MVCC机制实现的。锁定读是指添加事务锁的读操作,例如select for update和select lock in share mode语句。 官网参考:这里   关于MySQL的锁机制和事务隔离级别,参考以下两篇博客: https://www.linuxidc.com/Linux/2018-04/151914.htm
https://www.linuxidc.com/Linux/2018-04/151923.htm   第一部分:概述 锁定读、update和delete,这些操作通常会在扫描到的索引记录上添加record locks,InnoDB不关心这些行是否会被where条件过滤,因为InnoDB不记得具体的where条件,它只知道哪个索引范围被扫描过。 这些锁定添加的锁通常是next-key lock,这种锁既锁定扫描到的索引记录,也锁定索引间的gap。不过gap锁可以被显示的禁用,参考https://www.linuxidc.com/Linux/2018-04/151914.htm的Gap lock部分。   如果在SQL执行时你需要对次级索引记录加X模式的行锁,那么InnoDB也会检索相应的主键索引并加锁。   如果执行的SQL找不到合适的索引,InnoDB不得不去进行全表扫描,那么InnoDB会把表的每一个聚集索引记录都锁住,这可以看作是表级锁,同样参考https://www.linuxidc.com/Linux/2018-04/151923.htm的表锁部分。这种全表锁定会导致其他事务无法插入和更改(同样参考链接中的表锁兼容性部分),因此为SQL创建合适的索引是很有必要的,因为表锁(非意向锁)会导致DML操作阻塞。   对于select...for update和select...lock in share mode这种锁定读来说,开始时InnoDB会锁定所有扫描的索引记录,但是最后会释放那些不符合条件的索引记录上的锁(例如被where语句过滤掉的行)。但是在某些情况下由于结果行与源表的联系丢失,导致这些行锁不会被释放,例如:union操作,被扫描的中间结果行会被插入到一个临时表中以便形成最终的结果集,在这种情况下锁定行与原表之间的联系丢失,那么剩余的扫描行直到整个SQL执行结束才会被释放(不是事务执行结束)。   第二部分:InnoDB中SQL语句的加锁类型 1.在Repeatable Read和Read Committed事物隔离级别下,SELECT ... FROM语句是一种一致性非锁定读。而在SERIALIZABLE隔离级别下是锁定读,会在扫描的索引记录范围内添加Next-key行锁,不过如果扫描的是唯一索引,那么只会添加Record lock。   2.SELECT ... FROM ... LOCK IN SHARE MODE在扫描到的索引记录上添加S模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加S模式的Record lock。   3.SELECT ... FROM ... FOR UPDATE在扫描到的索引记录上添加X模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。   4.UPDATE ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。   5.当UPDATE语句修改的是主键索引时,InnoDB会隐式的将所有的次级索引锁定(二级索引都是用主键做书签的,因此修改主键索引是很耗资源的操作)。在插入二级索引记录或者为插入二级索引做重复性检查扫描时(unique index),update也会把受影响的二级索引锁定。   6.DELETE FROM ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。   7.INSERT语句会在插入的行上添加Record lock,Insert语句不会阻止其他事务在同一个gap上插入行。 虽然Insert语句不使用gap行锁,但是会使用一种叫插入意向锁的gap锁,即Insert Inrention Locks。这种锁的作用是为添加行锁做锁冲突检测,具体示例参考https://www.linuxidc.com/Linux/2018-04/151914.htm
的插入意向锁部分。 此外INSERT语句还涉及到主键的重复性检测,示例说明如下:   CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
--会话A执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--会话B执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--会话C执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--最后会话A在执行:
ROLLBACK;
--最后发现会话B和C形成了死锁。   因为开始时会话A在i=1上添加了X模式的行锁,会话BC在做重复性检测时发现已有i=1,于是在各自请求行上的一个S行锁,当A会话rollback后,BC的S行锁都获取到了,此时B和C都需要把S行锁转化为X行锁,但是都不愿意放弃自己的S锁,而S和X是互斥的,因此形成死锁。这个问题其实和SQL Server的更新锁出现的原因一样,只不过SQL Server通过U锁解决了此问题,即重复性检测使用的是U锁,而U锁只能有一个会话获取。   8.INSERT ... ON DUPLICATE KEY UPDATE,这种插入语句和普通的INSERT语句区别在于,他会在发生重复性键值错误时向索引记录上添加X行锁,如果是主键那添加X模式的record lock行锁,如果是普通的唯一索引那添加X模式的next-key行锁。这姑且算是对7的死锁问题的一种解决办法吧。   9.REPLACE语句可以看做是INSERT ... ON DUPLICATE KEY UPDATE的简写。   10.INSERT INTO T SELECT ... FROM S WHERE ...语句会在T表的每个被插入的行上添加X模式的record lock(无gap锁)。 如果事务隔离级别被设置为READ COMMITTED,或者innodb_locks_unsafe_for_binlog设为1而且事物隔离级别不是SERIALIZABLE,那么这两种情况下InnoDB对S表执行一致性非锁定读。否则InnoDB会对S表上的每个行都添加S模式的next-key lock。   11.CREATE TABLE ... SELECT ...语句的加锁机???与INSERT INTO T SELECT ... FROM S WHERE ...完全一致。 REPLACE INTO t SELECT ... FROM s WHERE ...或者UPDATE t ... WHERE col IN (SELECT ... FROM s ...)这两种SQL语句对s表的行添加S模式的next-key行锁。   12.关于AUTO-INC Locks参考https://www.linuxidc.com/Linux/2018-04/151914.htm
的AUTO-INC Locks部分。   13.如果表上有外键约束,那么任何需要做外键约束检测的DML语句都会在相应的外键上添加S模式的行锁。即便约束失败也会设置这些行锁。   14.LOCK TABLES也会在表上设置表锁,只是这种表锁并非是InnoDB层的表锁,而是MySQL层的表锁。因此如果死锁涉及到这些表锁时,InnoDB的死锁自动检测机制无法检测到这些表锁。而且由于MySQL层对InnoDB层的行锁机制并不清楚,因此此类表锁甚至可以加在正在使用行锁的InnoDB表上。不过这并不会危及到事务的完整性,具体说明详见:https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html   本文永久更新链接地址:https://www.linuxidc.com/Linux/2018-05/152411.htm linux
友情链接
  • Mozilla发布Firefox 67.0.4,修复沙箱逃逸漏洞
  • 蚂蚁金服正式成为CNCF云原生计算基金会黄金会员
  • Firefox 68将采用Microsoft BITS安装更新
  • OpenSSH增加对存储在RAM中的私钥的保护
  • 谷歌想实现自己的curl,为什么?
  • Raspberry Pi 4发布:更快的CPU、更大的内存
  • Firefox的UA将移除CPU架构信息
  • Ubuntu放弃支持32位应用程序实属乌龙,Steam会否重回Ubuntu怀抱
  • Qt 5.13稳定版发布:引入glTF 2.0、改进Wayland以及支持Lottie动
  • 红帽企业Linux 7现已内置Redis 5最新版
  • Slack进入微软内部禁用服务清单,GitHub也在其列?
  • 安全的全新编程语言V发布首个可用版本
  • Windows Terminal已上架,快尝鲜
  • 阿里巴巴微服务开源生态报告No.1
  • 面世两年,Google地球将支持所有基于Chromium的浏览器
  • 推进企业容器化持续创新,Rancher ECIC千人盛典完美收官
  • CentOS 8.0最新构建状态公布,或于数周后发布
  • Debian移植RISC
  • 微软拆分操作系统的计划初现雏形
  • Oracle发布基于VS Code的开发者工具,轻松使用Oracle数据库
  • Ubuntu 19.10停止支持32位的x86架构
  • 微软为Windows Terminal推出全新logo
  • 联想ThinkPad P系列笔记本预装Ubuntu系统
  • 微软发布适用于Win7/8的Microsoft Edge预览版
  • 启智平台发布联邦学习开源数据协作项目OpenI纵横
  • 经过六个多月的延迟,微软终于推出Hyper
  • ZFS On Linux 0.8.1 发布,Python可移植性工作
  • DragonFly BSD 5.6.0 发布,HAMMER2状态良好
  • Linux Kernel 5.2
  • CentOS 8.0 看起来还需要几周的时间
  • 百度网盘Linux版正式发布
  • PCIe 6.0宣布:带宽翻倍 狂飙至256GB/s
  • PHP 7.4 Alpha 发布,FFI扩展,预加载Opcache以获得更好的性能
  • Canonical将在未来的Ubuntu版本中放弃对32位架构的支持
  • Scala 2.13 发布,改进的编译器性能
  • 微软的GitHub收购了Pull Panda,并且使所有订阅完全免费
  • Windows Subsystem for Linux 2 (WSL 2)现在适用于Windows 10用
  • Debian 10 “Buster”的RISC
  • MariaDB宣布发布MariaDB Enterprise Server 10.4
  • DXVK 1.2.2 发布,带来微小的CPU开销优化
  • DragonFlyBSD 5.6 RC1 发布,VM优化,默认为HAMMER2
  • PrimeNG 8.0.0 发布,支持Angular 8,FocusTrap等
  • GIMP 2.10.12 发布,一些有用的改进
  • 清华大学Anaconda 镜像服务即将恢复
  • Debian GNU/Linux 10 “Buster” 操作系统将于2019年7月6日发布
  • 时时彩论坛
  • 五星体育斯诺克
  • 北单比分直播
  • 河北11选5走势图
  • 福建体彩36选7开奖结果
  • 九龙图库下载