首页 > 数据库 >[转载]详解Mysql innoDB意向锁的作用

[转载]详解Mysql innoDB意向锁的作用

时间:2024-02-27 10:15:56浏览次数:35  
标签:事务 users 互斥 innoDB 意向锁 Mysql 共享 意向

前言

InnoDB 支持多粒度锁(multiple granularity locking),它允许行级锁表级锁共存,而意向锁就是其中的一种表锁

意向锁(Intention Locks)

需要强调一下,意向锁是一种不与行级锁冲突表级锁,这一点非常重要。意向锁分为两种:

  • 意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享锁(S锁)
    1.   -- 事务要获取某些行的 S 锁,必须先获得表的 IS 锁。
    2.   SELECT column FROM table ... LOCK IN SHARE MODE;
    3.   复制代码
  • 意向排他锁(intention exclusive lock, IX):事务有意向对表中的某些行加排他锁(X锁)
    1.   -- 事务要获取某些行的 X 锁,必须先获得表的 IX 锁。
    2.   SELECT column FROM table ... FOR UPDATE;
    3.   复制代码

即:意向锁是有数据引擎自己维护的,用户无法手动操作意向锁,在为数据行加共享 / 排他锁之前,InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁要解决的问题

我们先来看一下百度百科上对意向锁存在意义的描述:

如果另一个任务试图在该表级别上应用共享或排它锁,则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁,而只需检查表上的意向锁。

设想这样一张 users 表: MySql,InnoDB,Repeatable-Read:users(id PK,name)

idname
1 ROADHOG
2 Reinhardt
3 Tracer
4 Genji
5 Hanzo
6 Mccree

事务 A 获取了某一行的排他锁,并未提交:

  1.   SELECT * FROM users WHERE id = 6 FOR UPDATE;
  2.   复制代码

事务 B 想要获取 users 表的表锁:

  1.   LOCK TABLES users READ;
  2.   复制代码

因为共享锁与排他锁互斥,所以事务 B 在视图对 users 表加共享锁的时候,必须保证:

  • 当前没有其他事务持有 users 表的排他锁。
  • 当前没有其他事务持有 users 表中任意一行的排他锁 。

为了检测是否满足第二个条件,事务 B 必须在确保 users表不存在任何排他锁的前提下,去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法,但是有了意向锁之后,情况就不一样了:

意向锁的兼容互斥性

意向锁是怎么解决这个问题的呢?首先,我们需要知道意向锁之间的兼容互斥性:

 意向共享锁(IS)意向排他锁(IX)
意向共享锁(IS) 兼容 兼容
意向排他锁(IX) 兼容 兼容

即意向锁之间是互相兼容的,emmm......那你存在的意义是啥?

虽然意向锁和自家兄弟互相兼容,但是它会与普通的排他 / 共享锁互斥:

 意向共享锁(IS)意向排他锁(IX)
共享锁(S) 兼容 互斥
排他锁(X) 互斥 互斥

注意:这里的排他 / 共享锁指的都是表锁!!!意向锁不会与行级的共享 / 排他锁互斥!!!

现在我们回到刚才 users 表的例子:

事务 A 获取了某一行的排他锁,并未提交:

  1.   SELECT * FROM users WHERE id = 6 FOR UPDATE;
  2.   复制代码

此时 users 表存在两把锁:users 表上的意向排他锁与 id 为 6 的数据行上的排他锁。

事务 B 想要获取 users 表的共享锁:

  1.   LOCK TABLES users READ;
  2.   复制代码

此时事务 B 检测事务 A 持有 users 表的意向排他锁,就可以得知事务 A 必然持有该表中某些数据行的排他锁,那么事务 B 对 users 表的加锁请求就会被排斥(阻塞),而无需去检测表中的每一行数据是否存在排他锁。

意向锁的并发性

这就牵扯到我前面多次强调的一件事情:

意向锁不会与行级的共享 / 排他锁互斥!!!
意向锁不会与行级的共享 / 排他锁互斥!!!
意向锁不会与行级的共享 / 排他锁互斥!!!

重要的话要加粗说三遍,正因为如此,意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性(不然我们直接用普通的表锁就行了)。

最后我们扩展一下上面 users 表的例子来概括一下意向锁的作用(一条数据从被锁定到被释放的过程中,可能存在多种不同锁,但是这里我们只着重表现意向锁):

idname
1 ROADHOG
2 Reinhardt
3 Tracer
4 Genji
5 Hanzo
6 Mccree

事务 A 先获取了某一行的排他锁,并未提交:

  1.   SELECT * FROM users WHERE id = 6 FOR UPDATE;
  2.   复制代码
  1. 事务 A 获取了 users 表上的意向排他锁。
  2. 事务 A 获取了 id 为 6 的数据行上的排他锁。

之后事务 B 想要获取 users 表的共享锁:

  1.   LOCK TABLES users READ;
  2.   复制代码
  1. 事务 B 检测到事务 A 持有 users 表的意向排他锁。
  2. 事务 B 对 users 表的加锁请求被阻塞(排斥)。

最后事务 C 也想获取 users 表中某一行的排他锁:

  1.   SELECT * FROM users WHERE id = 5 FOR UPDATE;
  2.   复制代码
  1. 事务 C 申请 users 表的意向排他锁。
  2. 事务 C 检测到事务 A 持有 users 表的意向排他锁。
  3. 因为意向锁之间并不互斥,所以事务 C 获取到了 users 表的意向排他锁。
  4. 因为id 为 5 的数据行上不存在任何排他锁,最终事务 C 成功获取到了该数据行上的排他锁。

总结

  1. InnoDB 支持多粒度锁,特定场景下,行级锁可以与表级锁共存。
  2. 意向锁之间互不排斥,但除了 IS 与 S 兼容外,意向锁会与 共享锁 / 排他锁 互斥
  3. IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突。
  4. 意向锁在保证并发性的前提下,实现了行锁和表锁共存满足事务隔离性的要求。

标签:事务,users,互斥,innoDB,意向锁,Mysql,共享,意向
From: https://www.cnblogs.com/shijianchuzhenzhi/p/18036262

相关文章

  • [转]MySQL “grant all”与”grant all privileges”授权语句的差别
    原文地址:MySQL“grantall”与”grantallprivileges”的差别|极客笔记在MySQL中,”grantall”与”grantallprivileges”都是用来赋予用户所有权限的语句。然而,在某些情况下,这两种语句的区别是非常明显的。 “grantall”“grantall”语句将授权用户执行操作的所有权......
  • Docker安装Mysql
    使用Dockers部署Nacos集群前置条件:已经安装dockerDockerSwarm集群已经初始化添加标签:dockernodeupdate--label-addmysql=masterworkId创建网络dockernetworkcreate-doverlayswarm_net构建私有镜像mysql.ymlversion:"3.8"services:mysql:......
  • mysql-批量修改表的主键id,修改成联合主键
    1.sql脚本一.通过sql脚本,查出所有表的功能,并编写插入修改的联合主键,sqlselect concat('ALTERtable',TABLE_NAME,'DROPPRIMARYKEY',',','ADDPRIMARYKEY(`id`,`org_id`)'';')from information_schema.TABLESwhereTABLE_SC......
  • mysql 定位问题常用sql
    select*,now()frominformation_schema.INNODB_TRX;--查看当前正在被锁定的事务select*,now()frominformation_schema.INNODB_LOCKS;--查看当前正在被锁定的行select*,now()frominformation_schema.INNODB_LOCK_WAITS;--查看当前等待锁的事务SHOWOPENTABLESW......
  • [MYSQL] MYSQL 8解决 Invalid default value for `created_at`(timestamp)
    1问题描述问题背景执行从MYSQL5.7导出,并在MYSQL8.0.33的数据库中执行DDL建表语句时,报Invaliddefaultvaluefor'create_at'...`create_at`timestampNOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'创建时间/createdtime',...2原因分析表面原因:......
  • mysql为啥不用红黑树
    Mysql没有使用红黑树的原因主要是因为红黑树的层级较多,可能会导致大量的磁盘IO操作。红黑树是一种自平衡的二叉查找树,它的特点是根节点为黑色,叶子节点为黑色的空节点,红色节点下的子节点一定为黑色节点。从根节点到叶子节点所有的路径上存在相同数目的黑色节点。红黑树的平衡性保......
  • MySQL——去重复
    MySQL——去重复把查询结果去除重复记录。注意:原表数据不会被修改,只是查询结果去重。使用的关键字为:distinct。语法结构:selectdistinct字段名from表名;distinct不能以这种形式出现:select字段名,distinct字段名from表名;会报错。distinct只能出现在所有字段的最前方......
  • MySQL备份恢复数据--binary-mode is enabled and mysql is run in non-interactive...
    使用mysqldump;MySQL自带的逻辑备份工具。mysqldump[选项]数据库名[表名]>脚本名mysqldump[选项]--数据库名[选项表名]>脚本名mysqldump[选项]--all-databases[选项]>脚本名备份mysqldump-hlocalhost-uwordpress-pwordpress_20200104>c......
  • mysql access denied for root ... mysqld –skip-grant-tables 命令失效 ... Failed
    <!--密码突然登录不上MySQL了,久了也不晓得是不是密码不正确...只能改密码...一年难得碰一次,感觉每次总有莫名其妙的问题--><!--修改方案只找到一个,就是无密码验证开启mysql服务,然后登录,设置新密码--><!--mysql版本不同有些命令无效,大概分高低两版本--><!--低版命令我......
  • 关于Windows上设置MySQL为外部可访问
    改表法。可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改"mysql"数据库里的"user"表里的"host"项,从"localhost"改称"%"1.mysql-uroot-pdtsdtsmysql>usemysql;mysql>updateusersethost='%&#......