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(36, 34);
  

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

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

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

  

• 临间锁的退化

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