MySQL多版本并发控制与锁机制

港控/mmm° 2022-05-08 09:44 345阅读 0赞

MySql InnoDB 存储引擎通过多版本并发控制(Multiversion Concurrency Control, 即MVCC)和锁(Lock)机制来实现各种事务隔离级别. 下面我们来一起学习下MySql InnoDB的多版本并发控制和锁机制.

### 多版本并发控制(MVCC) ###

首先我们来看下MySql中每行数据的格式:

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

InnoDB引擎会为每行数据添加三个隐藏字段, 分别是:

*  DB\_TRX\_ID
    
    6字节, 表示最近一次对本记录进行写操作的事务ID(增删改).
 *  DB\_ROLL\_PTR
    
    7字节, 指向undo log中旧版本数据的指针, 即回滚指针.
 *  DB\_ROW\_ID
    
    6字节, 单调递增的行id, 当表没有主键和唯一的非空索引时, 使用该字段生成聚集索引. 这个字段与MVCC无关.

当事务对某条记录进行写操作时, 会将旧版本的数据copy到undo log中, 然后再操作该条记录 (一个事务中的多个写操作只会copy一次), 如下图所示:

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

当事务对某条记录进行读操作时, 会生成一个读视图(Read View), 里面存储了对当前事务不可见的其他活跃事务Id, 当前事务只能顺着回滚指针读取到小于最小活跃事务Id的记录.

class ReadView { 
        private:
            /** The read should not see any transaction with trx id >= this value. In other words, this is the "high water mark". */
            trx_id_t    m_low_limit_id;
    
            /** The read should see all trx ids which are strictly smaller (<) than this value. In other words, this is the low water mark". */
            trx_id_t    m_up_limit_id;
    
            /** trx id of creating transaction, set to TRX_ID_MAX for free views. */
            trx_id_t    m_creator_trx_id;
    
            /** Set of RW transactions that was active when this snapshot was taken */
            ids_t       m_ids;
    
            /** The view does not need to see the undo logs for transactions whose transaction number is strictly smaller (<) than this value: they can be removed in purge if not needed by other views */
            trx_id_t    m_low_limit_no;
    
            /** AC-NL-RO transaction view that has been "closed". */
            bool        m_closed;
    
            typedef UT_LIST_NODE_T(ReadView) node_t;
    
            /** List of read views in trx_sys */
            byte        pad1[64 - sizeof(node_t)];
            node_t      m_view_list;
    };

当然, 如果把MVCC和锁结合起来, 当事务对某条记录进行写操作时, 会加上排他锁, 此时undo log中就只会有一个版本的旧数据, 其他事务直接读undo log中旧版本的数据. 当写事务提交后, undo日志中旧版本的数据就会被`Purge`线程清除.

**undo log的作用**

*  实现MVCC
 *  回滚未提交的事务

**什么是redo log?**

提到undo log就要说下redo log, redo log用来保证事务的持久性.

数据库事务提交后，并不会立即将更新后的数据刷到磁盘上, 而是先缓存在内存中(Buffer Pool), 再定期的刷到磁盘上. 如果这个时候数据库崩溃, 就会导致这部分修改后的数据丢失, 为了保证事务的持久性, 就引出了redo log.

当用户开启一个事务时, redo log缓冲区(redo log buffer)会缓存修改后的数据, 定期的写入到磁盘的redo log文件中, 并且保证在持久化数据文件前，之前的redo log已经写到磁盘. 这样当数据库崩溃时, 如果内存(Buffer Pool)中的数据没有持久化到磁盘上, 只需重启数据库, 就能从redo log文件中恢复数据.

**快照读与非快照读**

undo log里的旧版本数据也叫快照(snapshot), 所以从undo log里读数据也叫快照读. InnoDB中普通的查询都是快照读, 如:

select * from t where id>2;

非快照读是指显示加锁的select, 例如:

//共享锁
    select * from t where id>2 lock in share mode;
    //排他锁 
    select * from t where id>2 for update;

### InnoDB的七种锁 ###

InnoDB共有七种类型的锁：

*  共享/排他锁(Shared and Exclusive Locks)
 *  意向锁(Intention Locks)
 *  记录锁(Record Locks)
 *  间隙锁(Gap Locks)
 *  临键锁(Next-key Locks)
 *  插入意向锁(Insert Intention Locks)
 *  自增锁(Auto-inc Locks)

其中意向锁和自增锁是表锁, 其余都是行锁. 记录锁、间隙锁和临键锁是排他锁. 插入意向锁是间隙锁的一种. 下面是七种锁的详细介绍:

**共享/排他锁**

InnoDB实现了两种标准的行锁, 分别是共享锁和排他锁.

*  事务拿到某一行记录的共享锁，才可以读取这一行.
 *  事务拿到某一行记录的排它锁，才可以修改或者删除这一行.

共享锁和排他锁的兼容关系如下:

**意向锁**

InnoDB支持多重粒度锁, 允许行锁和表锁共存. 意向锁是表锁, 包括意向共享锁(intention shared lock)和意向排他锁(intention exclusive lock).

意向锁仅仅表明意向, 并不会锁住数据. 当事务对一行记录加共享锁之前必须先添加意向共享锁, 同样, 在加排他锁之前要先添加意向排他锁. 他们的兼容关系如下:

<table> 
 <thead> 
  <tr> 
   <th>是否兼容</th> 
   <th>共享锁</th> 
   <th>排他锁</th> 
   <th>意向共享锁</th> 
   <th>意向排他锁</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>共享锁</td> 
   <td>√</td> 
   <td>×</td> 
   <td>√</td> 
   <td>×</td> 
  </tr> 
  <tr> 
   <td>排他锁</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
  </tr> 
  <tr> 
   <td>意向共享锁</td> 
   <td>√</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>意向排他锁</td> 
   <td>×</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
 </tbody> 
</table>

**记录锁**

记录锁封锁索引记录, 例如:

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

它会在c1=10的索引记录上加锁，以阻止其他事务插入, 更新, 删除这一行.

注意, 普通的查询是快照读, 不加锁:

SELECT c1 FROM t WHERE c1 = 10

**间隙锁**

间隙锁封锁索引记录之间的间隔, 或者第一条索引记录之前的范围, 或者最后一条索引记录之后的范围. 例如一个索引包含10, 20:

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE

上面这条语句封锁了c1在10和20之间的这个范围, 插入一个c1=15的数据就会失败.

**临键锁**

临键锁是记录锁和间隙锁的组合, 临键锁会封锁索引记录和该索引记录之前的间隙. 例如有一个id索引包含10, 11, 13 和20, 临键锁可能封锁的区间如下:

(negative infinity, 10]	--封锁索引10时 
    (10, 11]		--封锁索引11时
    (11, 13]		--封锁索引13时
    (13, 20]		--封锁索引20时
    (20, positive infinity)		--封锁索引20时

当使用临建锁封锁20时, 因为20是最后一个索引值, 所以会封锁两个区间: (13, 20\] 和 (20, positive infinity).

在REPEATABLE READ隔离级别下, InnoDB使用临键锁解决幻读的问题(MySQL 5.7版本, 可见官网), 如下所示

SELECT * FROM t WHERE id > 12 FOR UPDATE;

上面的这条语句会锁定(11, 13\], (13, 20\], (20, positive infinity) 三个区间, 这样其他事务就不能在这个范围内插入行了.

**插入意向锁**

插入意向锁是间隙锁的一种, 是专门针对insert操作的. 在进行insert操作时, 会先在插入间隙加上插入意向锁, 然后对具体的插入行加上排他锁.

插入意向锁仅仅表示一种意向, 当多个事务在同一索引间隔内插入数据时, 如果插入的位置不冲突就不会阻塞彼此. 例如有一索引间隔\[10,15\]:

先执行事务A, 未提交:

insert into t values(11);

再执行事务B:

insert into t values(12);

因为两个事务插入的位置不冲突, 所以事务B并不会被阻塞.

**自增锁**

自增锁是表锁, 针对AUTO\_INCREMENT类型的列. 例如主键ID是AUTO\_INCREMENT类型的, 如果一个事务正在往表中插入记录, 其他所有事务的插入必须等待, 以便第一个事务插入的行拥有连续的主键值.

### InnoDB 多版本并发控制与锁机制的实例 ###

在MySQL默认的隔离级别(REPEATABLE READ)下:

下面实例用到的表结构:

CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `flag` int(11) DEFAULT NULL,
      `num` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `t_flag` (`flag`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**SELECT**

(1)普通select

普通的select都是快照读, 不加锁, 如:

select * from t where id>2;

(2)加锁select

*  select … lock in share mode  
    加共享锁. 在唯一索引(PRIMARY KEY和UNIQUE KEY) 上使用唯一的查询条件时, 加共享记录锁, 如:
    
    select \* from t where id=1 lock in share mode;
    
    其他情况下加共享临键锁. 如:
    
    select \* from t where id >1 lock in share mode;  
    select \* from t where flag =1 lock in share mode;
 *  select … for update  
    加排他锁. 在唯一索引(PRIMARY KEY和UNIQUE KEY) 上使用唯一的查询条件时, 加排他记录锁. 其他情况下加排他临键锁.

**UPDATE/DELETE**

和加排他锁的select类似, 在唯一索引(PRIMARY KEY和UNIQUE KEY) 上使用唯一的查询条件时, 加排他记录锁. 其他情况下加排他临键锁.

**INSERT**

在插入索引间隔内加插入意向锁, 对具体的插入行加排他记录锁. 例如有一索引间隔\[10,15\], 用户将要插入两行记录11和12. InnoDB首先在\[10,15\]的索引间隙内加插入意向锁, 然后再对11和12这两行记录加排他记录锁.

**注意:**

InnoDB存储引擎的行锁是通过索引实现的, 当语句没有用到索引时, InnoDB会放弃使用行锁而改用表锁.

上面这句话其实是有点问题的, 当语句没有用到索引时, InnoDB并没有把行锁升级为表锁, 仍然使用的是行锁, 只是把表中的所有行都锁定了.

如下面这条UPDATE语句会给整张表的所有记录都锁定:

update t set num = 5 where num =3;

### 扩展 ###

[InnoDB的四种事务隔离级别是如何实现的?][InnoDB]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70_pic_center]: /images/20220508/0a04e99c4cc240c8a0980648c0443c0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220508/ff48119159604f19a3d3a058357ba98c.png
[InnoDB]: https://tyshawnlee.blog.csdn.net/article/details/83061230