lht94

V1

2022/10/25阅读:11主题:默认主题

Mysql引擎innodb行锁的三种算法

Mysql中的锁

基于锁的属性分类:共享锁、排他锁。

基于锁的状态分类:意向共享锁、意向排它锁

根据锁的粒度分类:全局锁、页锁、表级锁、行锁(记录锁、间隙锁、和临键锁),实际上的锁就这些,上面两种分类只是站在不同维度上看这些锁

行锁的三种算法

Record Lock(记录锁)

(1)记录锁是一种行级锁, 仅仅锁住索引记录的一行,在单条索引记录上加锁。

(2)record lock锁住的永远是索引,或者说主键,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。

查询语句必须为精准匹配 = ,不能为 >、<、like等,否则会退化成临键锁。

所以说当一条sql没有走任何索引时,那么将会在每一条聚合索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的。 (2)READ COMMITTED级别下仅采用Record Lock.

  • 使用记录锁

    查询

     -- 事务1,查询id为7182的行,不提交
     begin;
     
     select * from w_word where id =7182 for update
     
     commit;

    更新

    -- 事务2,事务1如果不提交,此时执行下面的update语句会阻塞
    begin;

    update w_word set creator_user ='check' where id=7182

    commit;

    如图

Gap Lock(间隙锁)

间隙锁,锁定一个范围,单不包含记录本身。

(1)区间锁是一种行级锁, 仅仅锁住一个索引区间(开区间,不包括双端端点)。

(2)在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。 比如在 1、2、3中,间隙锁的可能值有 (∞, 1),(1, 2),(2, ∞),

(3)间隙锁可用于防止幻读,保证索引间的不会被插入数据

  • 产生的条件
  1. 可重复读的隔离级别
  2. 范围条件而不是相等条件索引数据
  3. 请求共享或排他锁时
  • 间隙锁的危害

    锁定整个范围内所有的索引键值,即使这个键值并不存在,无法插入锁定键值范围内的任何数据

  • 解决的问题

    解决了RR(可重复读)级别下幻读的问题

  • 使用间隙锁解决幻读

    表数据

    锁住间隙(7182,7185)

    中间只有记录7183

    begin;

    select * from w_word where id > 7182 and id < 7185 for update

  • 插入记录7184

    begin;

    INSERT INTO cloud_learning.w_word
    (id, word)
    VALUES(7184'splurge1231');

    commit;

    此时锁住的(7182,7185)之间多了一条数据7184,造成幻读,间隙锁的作用也产线了。

    select * from w_word where id > 7182 and id < 7185 for update
  • 解决幻读

    直接进行7183的查询for update,将会锁住(7182,7185)间隙,7183必须是不存在的记录,否则只会锁住7183这条记录

    select * from w_word where id=7183 for update;

    插入7184,这时候就阻塞住了,不会插入(71827185)间隙

    INSERT INTO cloud_learning.w_word
      (id, word)
      VALUES(7184'splurge1231');

    对于指定查询某一条记录的加锁语句,如果该记录不存在,会产生记录锁和间隙锁,如果记录存在,则只会产生记录锁,如:WHERE id = 7182 FOR UPDATE;

    对于查找某一范围内的查询语句,会产生间隙锁,如:WHERE id BETWEEN 5 AND 7 FOR UPDATE;

  • 普通索引的间隙锁

    在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁,这跟唯一索引不一样; 在普通索引跟唯一索引中,数据间隙的分析,数据行是优先根据普通索引排序,再根据唯一索引排序。

Next-key Lock(临键锁)

Next-key Lock:记录锁+Gap锁(间歇锁),即:既包含索引记录,又包含索引区间,主要是为了解决幻读。

同时锁住记录和间隙,前开后闭区间(a,b]。

  • 表结构

    lock_key建立了普通索引

    该表中 lock_key 列潜在的临键锁有: (-∞, 12], (12, 31], (31, 35], (35, +∞],

    在事务 A 中执行如下命令:

    非唯一索引列 锁住lock_key=31这条记录;

    begin;

    select * from test_lock where lock_key =31 for update

    之后如果在事务 B 中执行以下命令,则该命令会被阻塞:

    begin;

    INSERT into test_lock (id, lock_key) VALUES(3634);

    事务 A 在对 lock_key 为 31 的列进行 for update 操作的同时,也获取了 (31, 35] 这个区间内的临键锁。

  • Next-Key Locking实现应用程序的唯一性检查

    如果需要插入的值是唯一的,那么插入之前需求判断唯一性,此时并发会出现问题,但是如果加上lock in share mode,就不会出现问题,如图:

  • 临间锁的退化

    场景 退化
    唯一索引,=值匹配 记录锁
    唯一索引,=值匹配,但记录不存在 间隙锁
    唯一索引范围匹配(<>) 记录锁+间隙锁

分类:

后端

标签:

后端

作者介绍

lht94
V1