精华内容
下载资源
问答
  • 意向锁

    千次阅读 2018-09-03 11:23:21
    InnoDB 支持多粒度锁(multiple granularity locking),它允许行级锁与表级锁共存,而意向锁就是其中的一种表锁。 意向锁(Intention Locks) 需要强调一下,意向锁是一种不与行级锁冲突表级锁,这一点非常重要。...
    Gtaker
    阅读 280

    详解 MySql InnoDB 中意向锁的作用

    前言

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

    意向锁(Intention Locks)

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

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

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

    意向锁要解决的问题

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

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

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

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

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

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

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

    LOCK TABLES users READ;
    复制代码

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

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

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

    意向锁的兼容互斥性

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

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

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

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

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

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

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

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

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

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

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

    LOCK TABLES users READ;
    复制代码

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

    意向锁的并发性

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

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

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

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

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

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

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

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

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

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

    SELECT * FROM users WHERE id = 5 FOR UPDATE;
    复制代码
    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. 意向锁在保证并发性的前提下,实现了行锁和表锁共存满足事务隔离性的要求。

    参考资料:

    扩展阅读:

    展开全文
  • :对 “某种范围” 的数据上 “某种” 1.“某种范围”:行、表 2.“某种” 2.1 共享Shared Locks(S) 1、兼容性:加了S的记录,允许其他事务再加S,不允许其他事务再加X 2、加锁方式:select…lock...

    目录

    锁:对 “某种范围” 的数据上 “某种锁”
    1.“某种范围”:行、表
    2.“某种锁”
    2.1 共享锁Shared Locks(S锁)
    2.2 排他锁Exclusive Locks(X锁)
    2.3 表锁:意向锁 Intention Locks,意向锁相互兼容
    2.4 行锁:记录锁(Record Locks)
    2.5 行锁:间隙锁(Gap Locks)
    2.6 *行锁:临键锁(Next-Key Locks)
    2.7 行锁:插入意向锁(Insert Intention Locks)
    2.8 锁的兼容性
    2.9 表锁:自增锁(AUTO-INC Locks)
    3.锁的选择
    帮助知识
    1.查看事务、锁的sql

    锁:对 “某种范围” 的数据上 “某种锁”

    1.“某种范围”:行、表 2.“某种锁”

    2.1 共享锁Shared Locks(S锁)

    1、兼容性:加了S锁的记录,允许其他事务再加S锁,不允许其他事务再加X锁

    2、加锁方式:select…lock in share mode

    2.2 排他锁Exclusive Locks(X锁)

    1、兼容性:加了X锁的记录,不允许其他事务再加S锁或者X锁

    2、加锁方式:select…for update

    2.3 表锁:意向锁 Intention Locks,意向锁相互兼容

    1、表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”

    2、意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存,。

    3、例子:事务A修改user表的记录r,会给记录r上一把行级的排他锁(X),同时会给user表上一把意向排他锁(IX),这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

    4、1)意向共享锁(IS锁):事务在请求S锁前,要先获得IS锁
    2)意向排他锁(IX锁):事务在请求X锁前,要先获得IX锁

    q1:为什么意向锁是表级锁呢?
    当我们需要加一个排他锁时,需要根据意向锁去判断表中有没有数据行被锁定(行锁);

    (1)如果意向锁是行锁,则需要遍历每一行数据去确认;

    (2)如果意向锁是表锁,则只需要判断一次即可知道有没数据行被锁定,提升性能。

    q2:意向锁怎么支持表锁和行锁并存?
    (1)首先明确并存的概念是指数据库同时支持表、行锁,而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁,因为表一旦被上了一个表级的写锁,肯定不能再上一个行级的锁。
    (2)如果事务A对某一行上锁,其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以,没有意向锁的时候,让行锁与表锁共存,就会带来很多问题。于是有了意向锁的出现,如q1的答案中,数据库不需要在检查每一行数据是否有锁,而是直接判断一次意向锁是否存在即可,能提升很多性能。

    5、下图表示意向锁和共享锁、排他锁的兼容关系。
    意思是 当事务A对某个数据范围(行或表)上了“某锁”后,另一个事务B是否能在这个数据范围上“某锁”。

    在这里插入图片描述

    意向锁相互兼容,因为IX、IS只是表明申请更低层次级别元素(比如 page、记录)的X、S操作。

    因为上了表级S锁后,不允许其他事务再加X锁,所以表级S锁和X、IX锁不兼容

    上了表级X锁后,会修改数据,所以表级X锁和 IS、IX、S、X(即使是行排他锁,因为表级锁定的行肯定包括行级速订的行,所以表级X和IX、行级X)不兼容。

    注意:上了行级X锁后,行级X锁不会因为有别的事务上了IX而堵塞,一个mysql是允许多个行级X锁同时存在的,只要他们不是针对相同的数据行。

    2.4 行锁:记录锁(Record Locks)

    (1)记录锁, 仅仅锁住索引记录的一行,在单条索引记录上加锁。
    (2)record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。
    所以说当一条sql没有走任何索引时,那么将会在每一条聚合索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的。

    2.5 行锁:间隙锁(Gap Locks)

    (1)区间锁, 仅仅锁住一个索引区间(开区间,不包括双端端点)。
    (2)在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。

    比如在 1、2、3中,间隙锁的可能值有 (∞, 1),(1, 2),(2, ∞),
    (3)间隙锁可用于防止幻读,保证索引间的不会被插入数据

    2.6 *行锁:临键锁(Next-Key Locks)

    (1)record lock + gap lock, 左开右闭区间。
    (2)默认情况下,innodb使用next-key locks来锁定记录。select … for update
    (3)但当查询的索引含有唯一属性的时候,Next-Key Lock 会进行优化,将其降级为Record Lock,即仅锁住索引本身,不是范围。
    (4)Next-Key Lock在不同的场景中会退化:

    在这里插入图片描述

    2.7 行锁:插入意向锁(Insert Intention Locks)

    (1)插入意向锁是一种Gap锁,不是意向锁,在insert操作时产生。
    (2)在多事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。
    (3)假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。

    (4)插入意向锁不会阻止任何锁,对于插入的记录会持有一个记录锁。

    本例子和插入意向锁无关:是Gap锁和排它锁的关系
    例如test表存在若干数据的数据,先开始一个事务A,插入一条n=5的数据;(图中步骤1)
    此时如果开始一个事务B,执行查询 select * from test where n > 4 for update,事务B会申请Gap锁(4, ∞),申请成功后,被事务A的x锁阻塞,直到x锁被释放。(图中步骤2)
    可以看到图中步骤3的信息,在等待事务释放X锁

    在这里插入图片描述
    在这里插入图片描述

    2.8 锁的兼容性

    在这里插入图片描述

    2.9 表锁:自增锁(AUTO-INC Locks)

    AUTO-INC锁是一种特殊的表级锁,发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

    3.锁的选择

    1.如果更新条件没有走索引,例如执行”update test set name=“hello” where name=“world”;” ,此时会进行全表扫描,扫表的时候,要阻止其他任何的更新操作,所以上升为表锁。

    2.如果更新条件为索引字段,但是并非唯一索引(包括主键索引),例如执行“update test set name=“hello” where code=9;” 那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因:

    首先要保证在符合条件的记录上加上排他锁,会锁定当前非唯一索引和对应的主键索引的值;

    还要保证锁定的区间不能插入新的数据。

    如果更新条件为唯一索引,则使用Record Lock(记录锁)。

    帮助知识

    1.查看事务、锁的sql
    select from information_schema.innodb_locks;
    select from information_schema.innodb_lock_waits;
    select * from information_schema.innodb_trx;

    展开全文
  • 一.Innodb中的行级之共享与排它 (按级别分) 1.Innodb中行级的与表锁对比 InnoDB行锁不是直接记录, 而是索引, 这一点MySQL与Oracle不同, 后者是通过在数据块中对相应数据行加锁来实现的 InnoDB这种...

    一.Innodb中的行级锁之共享锁与排它锁 (按级别分)

    1.Innodb中行级锁的与表锁对比

    • InnoDB行锁不是直接锁记录, 而是锁索引, 这一点MySQL与Oracle不同, 后者是通过在数据块中对相应数据行加锁来实现的

    • InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据, InnoDB才使用行级锁, 否则,InnoDB将锁住所有行, 实现的效果相当于是表锁

    • 演示

    create table t01(id int,name char(16));  # 创建表(并且不添加索引)
    insert t01 value(1,"aa"),(2,"bb"),(3,"cc"),(4,"dd");  # 插入记录
    # 开启两个会话窗口, 分别手动开启事务
    # 事务1对 id=2 进行锁行操作, 事务2对 id!=2 的行进行更新
    # 发现阻塞, 一段时间后显示超时 : ERROR 1205 (HY000): Lock wait timeout exceeded;....
    

    image-20210228153157610

    create index index_id on t01(id);  # 为 id 字段创建索引
    desc t01;  # 查看表结构
    # 再次重复上面开启事务的步骤
    

    image-20210228154050025

    2.行锁的实现原理

    行锁锁的是索引, 索引又分为主键索引和非主键索引两种, 所以锁定的方式分为以下三种 :

    • 如果一条 sql 语句操作了主键索引, Mysql 就会锁定这条语句命中的主键索引(或称聚簇索引)
    • 如果一条语句操作了非主键索引(或称辅助索引), MySQL会先锁定该非主键索引, 再锁定相关的主键索引
    • 如果没有索引, InnoDB 会通过隐藏的聚簇索引来对记录加锁; 也就是说 : 如果不通过索引条件检索数据, 那么InnoDB将对表中所有数据加锁, 实际效果跟表级锁一样

    3.实际应用中的问题

    在实际应用中, 要特别注意InnoDB行锁的这一特性, 否则可能导致大量的锁冲突, 从而影响并发性能 :

    • 在不通过索引条件查询的时候, InnoDB 行锁锁定所有行, 效果相当于表锁
    • 当表有多个索引的时候, 不同的事务可以使用不同的索引锁定不同的行, 另外, 不论 是使用主键索引、唯一索引或普通索引, InnoDB 都会使用行锁来对数据加锁
    • 由于 MySQL 的行锁是针对索引加的锁, 不是针对记录加的锁, 所以虽然是访问不同行的记录, 但是如果是使用相同的索引键, 也还是会出现锁冲突的
    • 即便在条件中使用了索引字段, 但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的, 如果 MySQL 认为全表扫 效率更高, 比如对一些很小的表, 它就不会使用索引, 这种情况下 InnoDB 将锁住所有的行, 相当于表锁, 而不是行锁; 因此, 在分析锁冲突时, 别忘了检查 SQL 的执行计划, 以确认是否真正使用了索引

    4.行级锁分为共享锁和排他锁

    与对行处理有关的语句有 : insertupdatedeleteselect, 这四类语句在操作记录时, 都可以为行加上锁, 但需要注意的是 :

    • 对于 insert、update、delete语句, InnoDB会自动给涉及的数据加锁,而且是排他锁 (简称X锁)
    # 手动开启事务1,对 id=1 的记录进行增删改操作, 并且为提交状态
    # 开启事务2,也对 id=1 的记录进行增删改操作
    # 发现阻塞在原地,一段时间后显示超时 : ERROR 1205 (HY000): Lock wait timeout exceeded ....
    

    image-20210228163926773

    • 对于普通的 select 语句, InnoDB不会加任何锁, 需要我们手动自己加, 可以加两种类型的锁 :
    "共享锁(s)" : select * from [表名] where [条件] lock in share mode;
    "排它锁(x)" : select * from [表名] where [条件] for update;
    

    5.共享锁 (Share Lock)

    • 共享锁定义

    共享锁又称为读锁, 简称S锁, 顾名思义, 共享锁就是多个事务对于同一数据可以共享一把锁, 获准共享锁的事务只能读数据, 不能修改数据直到已释放所有共享锁, 所以共享锁可以支持并发读

    如果事务1对数据A加上共享锁后, 则其他事务只能对A再加共享锁或不加锁 (在其他事务里一定不能再加排他锁, 但是在事务1自己里面是可以加的), 反之亦然

    • 共享锁用法
    select * from [表名] where [条件] lock in share mode;
    

    在查询语句后面增加lock in share mode,Mysql会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时, 可以成功申请共享锁, 否则会被阻塞; 其他线程也可以读取使用了共享锁的表, 而且这些线程读取的是同一个版本的数据

    6.排它锁 (Exclusive Lock)

    • 排它锁定义

    排他锁又称为写锁, 简称X锁, 顾名思义, 排他锁就是不能与其他所并存, 如一个事务获取了一个数据行的排他锁, 其他事务就不能再对该行加任何类型的其他他锁 (共享锁和排他锁), 但是获取排他锁的事务是可以对数据就行读取和修改

    • 排它锁用法
    select * from [表名] where [条件] for update;
    

    在查询语句后面增加for update, Mysql会对查询结果中的每行都加排他锁, 当没有其他线程对查询结果集中的任何一行使用排他锁时, 可以成功申请排他锁, 否则会被阻塞

    加过排他锁的数据行在其他事务种是不能修改数据的, 也不能通过for updatelock in share mode锁的方式查询数据, 但可以直接通过select ...from...查询数据, 因为普通select查询没有任何锁机制

    7.共享锁与排它锁实验

    建立了索引且命中的情况下:

    • 事务1对某记录加排它锁, 事务2无法对该记录进行"改"(三种操作)操作(上面已经演示过了)
    • 事务1对某记录加共享锁, 自己可读可写, 其他事务只能读不能写

    image-20210228170050117

    • 当其他事务也加上共享锁时, 这时候所有的事务都只能进行读操作

    image-20210228171920885

    8.意向锁

    • 概念

    意向锁是表级锁, 其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型

    • 作用

    当一个事务在需要获取资源锁定的时候, 如果遇到自己需要的资源已经被排他锁占用的时候, 该事务可以需要锁定行的表上面添加一个合适的意向锁

    如果自己需要一个共享锁, 那么就在表上面添加一个意向共享锁; 而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话, 则先在表上面添加一个意向排他锁

    • Innodb中的两种意向锁
    • 意向共享锁(IS) : 事务打算给数据行共享锁; 事务在给一个数据行加共享锁前必须先取得该表的IS锁
    • 意向排他锁(IX) : 事务打算给数据行加排他锁; 事务在给一个数据行加排他锁前必须先取得该表的IX锁

    ps : 意向锁是InnoDB自动加的,不需要用户干预

    9.哪种情况使用表锁

    绝大部分情况使用行锁, 但在个别特殊事务中, 也可以考虑使用表锁

    • 事务需要更新大部分数据, 表又较大

    若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度

    • 事务涉及多个表, 较复杂, 很可能引起死锁, 造成大量事务回滚

    这种情况也可以考虑一次性锁定事务涉及的表, 从而避免死锁、减少数据库因事务回滚带来的开销当然, 应用中这两种事务不能太多, 否则, 就应该考虑使用Myisam

    10.行锁优化建议

    通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况, 在着手根据状态量来分析改善

    show status like "innodb_row_lock%";  # 查看行锁状态
    
    • 尽可能让所有数据检索都通过索引来完成, 从而避免无索引行锁升级为表锁
    • 合理设计索引, 尽量缩小锁的范围
    • 尽可能减少检索条件, 避免间隙锁
    • 尽量控制事务大小, 减少锁定资源量和时间长度
    • 尽可能低级别事务隔离, 详见下一章节
    展开全文
  • MySQL 行锁 意向锁 间隙锁

    千次阅读 2020-06-29 20:58:17
    MySQL 行锁 意向锁 间隙锁 一、锁的分类 共享锁:反正我就理解成读锁一个意思,事务A对某些数据加了共享锁,允许其他事务同时获取这些数据共享锁,但是不可以在这些数据上加排它锁。 排它锁:理解成写锁吧,事务A对...

    MySQL 行锁 意向锁 间隙锁

    一、锁的分类

    共享锁:反正我就理解成读锁一个意思,事务A对某些数据加了共享锁,允许其他事务同时获取这些数据共享锁,但是不可以在这些数据上加排它锁。

    排它锁:理解成写锁吧,事务A对某些数据加了排它锁,那么其他事务不再允许加共享锁或排它锁。

    理解:这样讲吧~事务A读取了一条记录,事务B也可以读取这条记录,但是他不能做修改(也就是不能做写操作)。事务A修改了一条记录,事务B不能读不能写这条记录。

    读锁:事务A对某些记录加了读锁,那么其他事务也可以加读锁读取这些数据,但是不能对其进行写操作(可以理解为不能修改了)

    写锁:事务A对某些记录加了写锁(一般做一些修改之类的),那么其他事务不可以再对这些数据进行写操作和读操作了

    行锁:也就是以行为单位的锁啦。也分行读锁、行写锁。

    表锁:表级锁呀~一锁就锁一整张表

    意向锁:表级锁。分为意向共享锁和意向排他锁

    **为啥要有意向锁呢?**我基于innodb引擎哈~它的作用,咱们可以通过下面一个例子来分析:

    innodb下有行锁,也有表级锁。

    如果事务A要给一张表加表写锁,肯定是不允许这张表有其他事务B已经在某行数据上加了行写锁的。那么mysql怎么去检索一张表里面的数据有没有行写锁啊,一条一条去看,是不是效率也太低了~所以这里衍生了意向锁。如果事务B对某行数据加了行写锁(属于排他锁哦),那么B也会同样给这张表加一个意向排他锁,等到事务A来加表锁的时候,事务A 会看到这张表上有了意向排它锁,事务A就不再进行加锁操作了。

    下面我们重点讲行锁

    二、行锁

    行锁演示

    表结构:id为主键,idx_name_age_sex(name,age,sex`)组合索引

    DROP TABLE IF EXISTS `tuser`;
    CREATE TABLE `tuser`  (
      `id` int(11) NOT NULL,
      `name` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `age` int(11) NULL DEFAULT NULL,
      `sex` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `idx_name_age_sex`(`name`, `age`, `sex`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    

    数据

    INSERT INTO `tuser` VALUES (1, '张三', 24, '1', '否');
    INSERT INTO `tuser` VALUES (2, '李四', 33, '1', '2');
    INSERT INTO `tuser` VALUES (3, '王五', 26, '1', '33');
    INSERT INTO `tuser` VALUES (4, '苏六', 28, '2', '33');
    INSERT INTO `tuser` VALUES (5, '赵期', 22, '1', '11');
    INSERT INTO `tuser` VALUES (6, '刘芳', 26, '2', '无');
    
    SET FOREIGN_KEY_CHECKS = 1;
    

    session1

    BEGIN;
    
    select * from tuser where name = '张三' lock in share mode;
    

    session2:

    BEGIN;
    
    UPDATE tuser set age =38 where name='张三';
    

    讲解:session1 开启事务,并执行select lock in share mode语句,此时会对name='张三’的记录加行读锁,不提交;此时,session2 开启事务,对name=‘张三’ 的数据执行update操作,结果为堵塞,如下图:
    在这里插入图片描述

    注意:事务提交后,锁就可以理解为释放了哈

    三、间隙锁

    间隙锁:很容易理解,锁住一个间隙。阻止其他事务向间隙中插入数据,或将数据更新为间隙内的值

    这里介绍一个Next-Key,RR隔离级别当前读依靠next-key来解决幻读问题,快照读使用mvcc解决幻读

    Next-key:记录锁+间隙锁

    下面是我总结的各种情况的RR隔离级别下的加锁情况,先简单参考一下,不用背,理解就可以了,不理解的自己做实验试一下。

    在这里插入图片描述

    演示

    表结构和数据看图吧

    在这里插入图片描述

    1、辅助索引

    1)等值查询

    a)命中记录

    session1

    BEGIN;
    
    select * from user where num=10 lock in share mode;
    
    COMMIT;
    

    session2:

    BEGIN;
    
    INSERT INTO `user` VALUES (9,11,22);
    
    COMMIT;
    

    执行结果:阻塞

    在这里插入图片描述

    分析

    在这里插入图片描述

    session1 走的是辅助索引的等值查询,且命中记录,所以会产生记录锁+间隙锁。间隙锁锁的位置就是如上图中的位置,而记录锁 锁的是 辅助索引树上num=10的这行记录 和 主键索引树上 num=10的记录(虽然也就是同一条数据,但是主键索引树和辅助索引树上都要锁住)。

    b)无命中

    session1

    BEGIN;
    
    select * from user where num=13 lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (9,14,22);
    
    INSERT INTO `user` VALUES (9,16,22);
    

    效果截图:(9,14,22)阻塞,(9,16,22)成功

    在这里插入图片描述

    在这里插入图片描述

    分析

    在这里插入图片描述

    首先我们的sql使用的是辅助索引,进行等值查询,但是没有命中记录,所以只会产生间隙锁,而不会产生记录锁。间隙锁的范围如上图所示。

    2)范围查询

    a)命中记录

    session1

    BEGIN;
    
    select * from user where num>6 and num <11  lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (9,6,22);
    

    效果截图

    分析

    在这里插入图片描述

    session1 走的是辅助索引的范围查询,且命中num为10的记录,所以会产生记录锁+间隙锁。间隙锁锁的位置就是如上图中的位置,而记录锁 锁的是 辅助索引树上num=10的这行记录 和 主键索引树上 num=10的记录(虽然也就是同一条数据,但是主键索引树和辅助索引树上都要锁住)。

    b)无命中记录

    session1

    BEGIN;
    
    select * from user where num>7 and num <9  lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (9,6,22);
    

    效果截图
    在这里插入图片描述

    分析
    在这里插入图片描述

    首先我们的sql使用的是辅助索引,进行范围查询,但是没有命中记录,所以只会产生间隙锁,而不会产生记录锁。间隙锁的范围如上图所示。

    2、主键索引

    1)等值查询

    a)命中记录

    session1

    BEGIN;
    
    select * from user where id=8  lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (9,6,22);
    

    效果截图

    在这里插入图片描述

    分析

    这条sql我们使用的是主键索引的等值查询,命中id=8的记录,这里只会产生记录锁,也就是锁住 主键索引树上的id=8的那条记录。不会产生间隙锁。

    b)无命中记录

    session1

    BEGIN;
    
    select * from user where id=17  lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (18,18,22);
    

    效果截图

    在这里插入图片描述

    分析

    在这里插入图片描述

    这条sql我们使用的是主键索引的等值查询,无命中记录,这里只会产生间隙锁。间隙锁范围如上图所示。

    2)范围查询

    a)命中记录

    session1

    BEGIN;
    
    select * from user where id>14 and id <17  lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (18,6,22);
    

    效果截图

    在这里插入图片描述

    分析

    在这里插入图片描述

    这条sql我们使用的是主键索引的范围查询,命中id=15的记录,这里会产生间隙锁和记录锁。间隙锁范围如上图所示。而记录锁 锁的是 主键索引树上num=15的这行记录 。

    b)无命中记录

    session1

    BEGIN;
    
    select * from user where id>16 and id <18  lock in share mode;
    

    session2

    BEGIN;
    
    INSERT INTO `user` VALUES (19,6,22);
    

    效果截图

    在这里插入图片描述

    分析

    在这里插入图片描述

    这条sql我们使用的是主键索引的范围查询,无命中记录,这里只会产生间隙锁。间隙锁范围如上图所示。

    展开全文
  • mysql共享锁 排他锁 意向锁

    千次阅读 2019-04-01 13:34:36
    又称为写(X),当有多个事务时,排他不能与其他并存,一个事务获取了一行数据的排他,其他事务就不能再获取该行的其他,包括共享与排他。 但是获取排他的事务是可以对数据进行读取和...
  • “某种”2.1 共享Shared Locks(S)1、兼容性:加了S的记录,允许其他事务再加S,不允许其他事务再加X2、加锁方式:select…lock in share mode2.2 排他Exclusive Locks(X)1、兼容性:加了X的...
  • MySQL锁的总结 和 一次插入意向锁的死锁还原分析锁总结为什么要加锁?有什么锁?常见的三种锁常见的五种锁模式锁的兼容性如何分析死锁已经发生死锁正在阻塞中,等待锁释放。记一次死锁还原分析死锁总结
  • 为什么主键通常要使用自增? MySQL的 Inoodb 引擎是基于B+树索引...排他又称为写,简称X,顾名思义,排它不能与其它并存,而且只有一个事务能拿到某一数据行的排它,其余事务不能再获取该数据行的所有
  • 2. MySQL锁分类2.1 读锁(共享锁)2.2 写锁(排他锁)2.3 意向锁2.4 MDL锁3. 表锁和行锁4. 锁等待和死锁5. 锁监控6. 总结 1. 什么是锁?为什么要使用锁? 2. MySQL锁分类 2.1 读锁(共享锁) 2.2 写锁(排他锁) 2.3...
  • mysql共享锁、排他锁、意向锁

    千次阅读 2019-07-23 15:21:45
    1.意向排他锁和共享锁与排他锁冲突 (锁住一行的写锁,其他事务不可获取该表任何锁) 2.意向共享锁和共享锁兼容,和排他锁冲突 (锁住一行的读锁,另一事务可获取该表的读锁...3.意向锁之间相互兼容 (行锁之间不冲突)
  • 文章目录1、数据库为什么要有锁...- 间隙锁(Gap Lock)3.7、行 - 临键锁(Next-Key Lock)3.8、行 - 插入意向锁(Insert Intention Lock)3.9、总结-要点4、对应SQL场景5、锁与事务的关系6、死锁和死锁检测处理6.1、死
  • 2. 读写锁和意向锁 排他锁(Exclusive),简写为 X 锁,又称写锁。 共享锁(Shared),简写为 S 锁,又称读锁。 意向共享锁(IS)和意向排他锁(IX):如果事务想要给表中几行数据加上行级共享锁,那么需要先在表...
  • 点击上方关注“Java后端技术栈“前言InnoDB 支持多粒度锁(multiple granularity locking),它允许行级锁与表级锁共存,而意向锁就是其中的一种表锁。意向...
  • 读写锁&意向锁

    2019-05-16 15:21:10
    文章目录并发控制封锁**排他锁(写锁)****共享锁(读锁)**两段锁协议(扩展, 收缩)封锁的粒度意向锁 并发控制 并发操作带来的数据不一致性包括 丢失修改 两个事务 T1, T2 同时对一个数据修改, T2 的提交结果破坏了 ...
  • innodb锁-插入意向锁

    千次阅读 2019-01-13 16:20:12
    Insert Intention Locks译称插入意向锁,首先强调插入意向锁是间隙锁的一种,本文参考官方文档进行学习说明 数据库版本: SELECT VERSION(); ±-----------+ | version() | ±-----------+ | 5.6.34-log | ...
  • 一、意向锁 意向锁的分类: 意向共享锁,事务想要给数据库某些行加共享锁,需要先获取意向共享锁 意向互斥锁,事务想要给数据库某些行加互斥锁,需要先获取意向互斥锁 意向锁是表锁 !!! 为什么需要意向锁? 主要...
  • (意向锁是表锁,T1加S锁,必然获得IS锁),说明表肯定有行级的S锁,因此,T2申请X锁阻塞等待,不需要判断全表,判断效率极大提高(是不是省了很多钱) 4. 间隙锁 小明先飚了一段英语,想来压制我? 但是我并...
  • InnoDB的锁机制浅析(二)—探索InnoDB中的锁(Record锁/Gap锁/Next-key锁/插入意向锁) InnoDB的锁机制浅析(三)—幻读 InnoDB的锁机制浅析(四)—不同SQL的加锁状况 InnoDB的锁机制浅析(五)—死锁场景(Insert死锁) ...
  • mysql innodb行锁、表锁、意向锁

    千次阅读 2019-09-06 11:19:15
    今天讲一下mysql的数据库引擎中的InnoDb下的行锁、表锁、意向锁等 行锁 mysql的行锁是通过索引加载的,即是行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描 行锁类型 共享锁 和 排它锁 ...
  • 共享与排他 共享:本事务加锁后,其他事务可以读,不可以写。 排他:本事务加锁后,其他事务既不可以读,也不可以写。 行级与表级 表级:粒度打,以表为单位上,允许很低的并发度。 行级...
  • 浅析MongoDB中的意向锁

    2018-10-19 09:10:34
    意向锁就是实现资源并发控制管理的经典方式。在讨论它的概念与设计前,我们先举几个MongoDB的经典场景。 mongoDB 默认是行级并发,我们希望多行并发读写互不影响,但是我们又希望对在dropCollection时,不能有任何...
  • Mysql共享锁, 排他锁和意向锁的简单总结共享锁(Share Lock)排他锁(Exclusive Lock)意向锁(Intent Lock)InnoDB的两种表意向锁加锁过程说明各锁之间兼容详情表 共享锁(Share Lock) 共享锁又称读锁, 缩写(S)锁. 共享锁...
  • InnoDB 支持多粒度锁(multiple granularity locking),它允许行级锁与表级锁共存,而意向锁就是其中的一种表锁。 意向锁(Intention Locks) 需要强调一下,意向锁是一种不与行级锁冲突表级锁,这一点非常重要。...
  • InnoDB并发插入,居然使用意向锁? 原创:58沈剑架构师之路2018-08-19 《插入InnoDB自增列,居然是表级别锁?》介绍了InnoDB所使用的七种锁中的一种,自增锁。 今天,将要介绍InnoDB另外三种:共享/排他锁,...
  • InnoDB 存储引擎中使用的多种并发控制策略,按照的粒度划分,可以分成行锁和表锁。 1. 并发控制 并发控制保证数据一致性的常见手段有:(Locking)和数据多版本(Multi Versioning)。乐观和悲观其实都是...
  • MySQL:共享锁 互斥锁 意向锁

    千次阅读 2019-12-16 14:01:43
    MySQL:共享锁 互斥锁 意向锁
  • 【mysql数据库】意向锁与哪些锁冲突

    千次阅读 2019-07-26 19:27:03
    意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。表名加锁的“意图”。 要读写,直接加S锁或者X锁就好啦,为什么还要表名这样一个意图呢? 意向锁有两种: 意向共享锁(IS)表示事务意图在表中的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 13,114
精华内容 5,245
关键字:

意向锁