《MySQL中级系列教程-第十章-MySQL锁机制详解-下篇》

妖狐艹你老母 2022-10-07 13:47 54阅读 0赞

# 《MySQL中级系列教程-第九章-MySQL锁机制详解-下篇》 #

# 一、意向锁 #

**意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存**。意向锁是由InnoDB在操作数据之前自动加的，不需要用户干预；

*  意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
 *  意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

> 意向锁（IS、IX）之间是可以互相兼容的

我们知道，在MySQL中行锁和表锁是可以并存的（**即使阻塞也是并存**），之所以能够并存是因为意向锁的存在

*  场景举例（假设此时没有意向锁）：假设事务A锁住了整表数据，那么事务B就不能修改表中的任意一行数据，因此在没有意向锁的时候，行锁与表锁共存就会存在问题！

意向锁的作用就是：当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁，就申请一个**意向共享锁**。如果需要的是某行（或者某些行）的排他锁定，则申请一个**意向排他锁**。

## 1.1 意向锁是如何让表锁和行锁并存？ ##

有了意向锁之后，事务A在申请表锁（写锁）之前，数据库会自动的给事务A申请的表加上意向排它锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。

## 1.2 意向锁是表锁还是行锁？ ##

**答：意向锁是表级别锁**

当我们需要加一个排他锁时（不管是表级还是行级），需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

（1）如果意向锁是行锁，则需要遍历每一行数据去确认；

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

> 注意：意向锁不会和行级的S和X锁冲突，只会和表级的S和X锁冲突

## 1.3 S锁、X锁、IX、IS锁之间的关系： ##

<table> 
 <thead> 
  <tr> 
   <th align="left"></th> 
   <th align="left"><code>X</code></th> 
   <th align="left"><code>IX</code></th> 
   <th align="left"><code>S</code></th> 
   <th align="left"><code>IS</code></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>X</code></td> 
   <td align="left">Conflict</td> 
   <td align="left">Conflict</td> 
   <td align="left">Conflict</td> 
   <td align="left">Conflict</td> 
  </tr> 
  <tr> 
   <td align="left"><code>IX</code></td> 
   <td align="left">Conflict</td> 
   <td align="left">Compatible</td> 
   <td align="left">Conflict</td> 
   <td align="left">Compatible</td> 
  </tr> 
  <tr> 
   <td align="left"><code>S</code></td> 
   <td align="left">Conflict</td> 
   <td align="left">Conflict</td> 
   <td align="left">Compatible</td> 
   <td align="left">Compatible</td> 
  </tr> 
  <tr> 
   <td align="left"><code>IS</code></td> 
   <td align="left">Conflict</td> 
   <td align="left">Compatible</td> 
   <td align="left">Compatible</td> 
   <td align="left">Compatible</td> 
  </tr> 
 </tbody> 
</table>

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

意向锁的主要功能就是：解决表锁与之前可能存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗。

# 二、自增锁 #

MySQL的自增锁是针对于自增列增长的一个特殊的**表级别锁**

和自增锁相关的一个参数为（5.1.22版本之后加入）innodb\_autoinc\_lock\_mode：可以设定3个值，0，1，2

show variables like 'innodb_autoinc_lock_mode';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center]

*  0：traditional （每次都会产生表锁）
 *  1：consecutive （会产生一个轻量锁，insert会获得批量的锁，保证连续插入），事务未提交ID永久丢失，**默认值**
 *  2：interleaved （不会锁表，来一个处理一个，并发最高）

> tips：参数只控制InnoDB引擎的设置，所有Myisam均为traditional ，每次均会进行表锁。只有Innodb会视参数不同而产生不通的锁。

创建测试表：

CREATE TABLE `t1`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `age` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT=1;

一般我们在创建表的时候id起始值为1，通过AUTO\_INCREMENT可以设置其值；

-- 在创建表后也可以通过SQL语句修改auto_increment
    alter table t1 auto_increment=20;

自增幅度由以下两个参数进行控制：

-- 自增的步长
    set auto_increment_increment=2;			-- 默认1
    
    -- 起始的自增值
    set auto_increment_offset=2;			-- 默认1

可以通过函数获取最后一个插入的id：

select last_insert_id();

测试：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_31_color_FFFFFF_t_70_g_se_x_16_pic_center]

# 三、锁的算法 #

## 3.1 Record Lock ##

Record Lock：行锁，也叫记录锁，总是会去锁住索引记录，在使用主键或唯一索引精准匹配行时，临键锁将会退化成行锁；

## 3.2 Gap Lock ##

Gap Lock：间隙锁，当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。

间隙锁（Gap Lock）是Innodb在**可重复读**提交下为了解决幻读问题时引入的锁机制

*  创建测试表，添加数据

CREATE TABLE `t1`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `age` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT=1;
    
    insert into t1 values(1,10);
    insert into t1 values(2,20);
    insert into t1 values(3,30);

查询范围数据，触发间隙锁

select * from t1 where id>2 for update;

在另一事务中插入间隙数据触发锁，发现处于等待现象

insert into t1 values(4,40);

*  思考

1、执行如下sql语句（表中并没有id为100的数据）

select * from t1 where id=100 for update;

2、执行如下sql语句

insert into t1 values(100,100);

**思考：会不会造成锁阻塞？**

**注意：间隙锁主要是阻塞插入 insert。相同的间隙锁之间不冲突；**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_36_color_FFFFFF_t_70_g_se_x_16_pic_center]

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了id大于2的任何记录，那么本事务如果再次执行上述语句，就可能会发生幻读（某些情况通过MVCC快照已经解决）；

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。**因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。**

## 3.3 Next-Key Lock ##

Next-Key Lock：临键锁，即`Gap Lock+Record Lock`，当SQL语句按照索引进行数据的检索时，锁定一个范围，并且锁定记录本身；它是InnoDB默认隔离级别的锁算法，其设计的目的是为了解决Phantom Problem(幻读)，临键锁锁住索引的记录+区间（左开右闭）

左开右闭：左边的可以获取到锁，右边的不行

测试表：

CREATE TABLE `t2`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `num` int(11) ,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB ;
    
    INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
    INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
    INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
    INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
    
    -- 创建普通索引
    create index idx_num on t2(num);
    
    -- 创建唯一索引
    create unique index idx_num on t2(num);
    
    -- 删除索引
    drop index idx_num on t2;

*  区间示意图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_34_color_FFFFFF_t_70_g_se_x_16_pic_center]

### 3.3.1 临键锁体验： ###

<table> 
 <thead> 
  <tr> 
   <th align="left">session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td align="left">select * from t2 where id&gt;11 and id&lt;16 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(10,10); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(11,11); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(15,15); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(16,16); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(18,18); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(20,20); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(21,21); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left">rollback;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

临键锁的触发不仅把条件区间（11-16）的数据行锁住了，还把临键的数据行统统锁住了；锁住的区间为：`(10,15]、(15,20]`

**锁住的id范围：10（不含）~20（含）**

### 3.3.2 临键锁触发时机 ###

我们刚刚测试的是以主键索引进行测试，如果采用不同的列（主键索引、普通索引、普通列等）锁住的区间大不相同；

*  临键锁触发时机：
    
    **1）如果是主键索引或者唯一索引，那么临键锁会降级为行级锁（前提是刚好在临键值）**
    
    **2）如果是二级索引则触发临键锁**
    
    **3）如果是普通列，则触发表级锁**

#### 1）普通列 ####

以`num`列触发排他锁测试：

<table> 
 <thead> 
  <tr> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>select * from t2 where num=11 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,3); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,5); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,10); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,15); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,20); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,21); – 阻塞</td> 
  </tr> 
  <tr> 
   <td>rollback;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

> **innodb查询如果没有使用到索引默认触发表级锁；**

#### 2）二级索引列 ####

-- 创建普通索引
    create index idx_num on t2(num);

测试临键锁：

<table> 
 <thead> 
  <tr> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>select * from t2 where num=17 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,15); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,18); – 阻塞</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into t2 values(null,20); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td>rollback;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

num=17这条记录不是会锁定(15,20\]区间吗？为什么15被阻塞了，20反而没被阻塞呢？

这里需要牵扯到另一个问题了，在InnoDB中，**相同的普通索引的叶子节点是以主键的顺序进行排列的**（聚集索引的特性），我们来模拟一下刚刚插入的数据在B+Tree上的变化：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_42_color_FFFFFF_t_70_g_se_x_16_pic_center]

只考虑叶子节点的变化，可以看到在上图在演变的过程中产生了分裂情况（假设每个叶子节点都只存储两个元素），如果普通索引的重复值太多势必会造成大量的分裂情况，减低插入效率，因此索引列不宜选择重复率太大的列；

再看下图数据库表中实际存储的列的样子我们就会明白为什么num=20不阻塞，num=15阻塞了

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_38_color_FFFFFF_t_70_g_se_x_16_pic_center]

<table> 
 <thead> 
  <tr> 
   <th align="left">session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td align="left">select * from t2 where num=15 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,8); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,10); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,11); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,18); – 阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,20); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left">rollback;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

15处于`(10,15]`和`(15,20]`两个临键区间，因此在两个区间内的数据行都被锁住了

至于20没被阻塞和10被阻塞的原因和上面一致

#### 3）主键和唯一索引 ####

创建唯一索引：

-- 删除索引
    drop index idx_num on t2;
    -- 创建唯一索引
    create unique index idx_num on t2(num);

测试临键锁：

<table> 
 <thead> 
  <tr> 
   <th align="left">session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td align="left">select * from t2 where num=15 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,4); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,8); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,11); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,18); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>insert into t2 values(null,20); – 不阻塞</td> 
  </tr> 
  <tr> 
   <td align="left">rollback;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

分析：在session1中首先会num=15加X锁，由于num是唯一键，因此锁定的只有15这个值，而不是(15,20\]这个区间；在session2中插入值18，是可以成功插入的，即锁定由Next-Key Lock算法降级为了Record Lock，从而提高应用的并发性。

#### 4）临键锁总结 ####

**临键锁**，是**记录锁（行锁）与间隙锁的组合**，它的封锁范围，既包含索引记录，又包含索引区间。

临键锁的主要目的，也是为了避免**幻读**(Phantom Read)。**如果把事务的隔离级别降级为RC，临键锁则也会失效。**

# 四、悲观锁，乐观锁 #

## 4.1 悲观锁乐观锁概念 ##

*  **悲观锁**
    
     *  就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改。
     *  整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制
     *  事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁
 *  **乐观锁**
    
     *  顾名思义，就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
     *  但是在更新的时候会去判断在此期间数据有没有被修改
     *  需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性
 *  **悲观锁，乐观锁使用前提**
    
     *  对于读操作远多于写操作的时候，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
     *  如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观  
        锁，它增加了复杂度，也带来了业务额外的风险。

## 4.2 乐观锁的实现方式 ##

### 1）版本号 ###

就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version如果还是开始读取的version就可以更新了如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。

-- 要修改数据之前，先查该数据上一次修改的时间戳
    select version from t_goods where id=1; 
    
    -- 修改数据时，更新时间戳
    update t_goods set goods_name='小苹果', version=version+1 where version=${version};

### 2）时间戳 ###

和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间（timestamp）和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比如果一致则OK，否则就是版本冲突。

-- 要修改数据之前，先查该数据上一次修改的时间戳
    select lock_time from t_goods where id=1;
    
    -- 修改数据时，更新时间戳
    update t_goods set goods_name='小苹果', lock_time=unix_timestamp(CURRENT_TIMESTAMP) where lock_time=${lock_time};

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20221005/fcd067e05b154abea6e7f0a98982950a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_31_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20221005/4dc3e7e7d8be462eb2daa738bf0341cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_36_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20221005/3da664b985994b79981d0adcd2d700de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_34_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20221005/7c3d07c0c23a49b5b446ee47442c9e7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_42_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20221005/10c8b09632934f83961dedadb07a0fa6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_38_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20221005/22ca9e87c3924fe6b4811818ca6d4a94.png