Mysql InnoDB加锁分析

作者: youngYmca | 来源:发表于2020-04-13 23:12 被阅读0次

在文章的开始，简单思考一个小问题：假如有一个SQL语句delete from T where id = 1，这条SQL在InnoDB中执行的时候数据库如何加锁的？

数据库的锁

要回答上面的问题，首先我们要知道数据库的锁到底是什么。首先InnoDB数据库的锁级别有表级锁，页级锁，行级锁。

X锁与S锁

对于行级锁来说有两种：排它锁X、共享锁S。这两种锁的兼容性如下：

锁	X	S
X	不兼容	不兼容
S	不兼容	兼容

可以看到假如一个事务获取了某行数据的X锁，则其它事务就不能获取该行数据的X、S锁了，必须等待前面一个事务释放X锁才可以获取自己想要的锁。但是如果一个事务获取了某行数据的S锁，则其它事务是可以获取该数据的S锁的，但是不能获取X锁。也就是说S锁只与S锁兼容。X锁与任何锁都不兼容。

IX锁与IS锁

数据库为了实现不同粒度的锁的共存，引入了意向锁的概念。意向锁是表级锁，解释为事务在当前表的记录上所需要的锁（S、X）。相应的意向锁就有意向排它锁、意向共享锁。数据库结构如下：

数据库结构.png

假如有一个事务需要对记录1进行加X锁，则它需要先对页、表、数据库层面加上IX锁。如果遇到不兼容则需要等待其它事务释放锁。意向锁的兼容性如下：

锁	X	S	IX	IS
X	不兼容	不兼容	不兼容	不兼容
S	不兼容	兼容	不兼容	兼容
IX	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

可以看出意向锁和意向锁之间是兼容的，这也就解释了为何InnoDB不会有表级别的死锁。S锁除了可以与S锁兼容以外也可以和IS锁兼容。

锁的算法

锁的算法分为三种：Record锁、Gap锁、Next-Key锁。

Record锁：记录锁，即对单个记录上进行加锁。
Gap锁：间隙锁，对记录与记录之间的间隙进行加锁，但是不锁定记录。
Next-Key锁：即锁定一个范围，也锁定记录。

MVCC

多版本控制（Multi Version Concurrency Control），将每一条写操作都记录到undo log中，方便事务读取历史版本的数据。这样的话只有写和写之间才会互不兼容，提升了并发效率。

当前读 & 快照读

快照读.png

数据库不同的写事务执行完成后会生成不同的快照，而快照读就是基于这些快照去读取，而并非是数据库记录的实际数值。

当前读就是每次都获取数据库的最新实际值。不考虑快照。

Undo log

在MVCC中，数据库为每一张表都额外添加了几个隐藏字段：

DB_ROW_ID：数据记录的唯一标识，随着数据的插入而递增。可以简单的把它理解为功能类似主键ID。假如创建的表没有添加主键，则数据库会用该字段创建聚簇索引。
DB_TRX_ID：最新写操作修改该记录的事务ID。也就是说哪个写事务最后提交，这字段就更新成这个事务的ID。
DB_ROLL_PTR：回滚指针，指向undo log。

undo log是存放数据记录历史版本的地方。每次insert/update/delete操作都会生成对应的undo log。不同的是insert的undo log会在当前事务提交后就可以被清除掉。而update的会一直存在，除非记录被delete。delete的undo log会在当前没有快照在使用的时候允许被清除。

所以一张数据库表的结构如下：

undo log.png

由上图可以看到一条数据库真实数据记录是有一个指针指向undo log，然后整体是一个链表结构。也就是说历史版本都可以追溯到。

Read View

read view主要是来辅助事务进行可见性判断的。作用是判断当前事务可以看到哪些快照。内部重要的参数如下：

low_limit_no：不需要undo log的事务编号，也就是说所有小于改编号的事务对应的undo log都可以被清除。
low_limit_id：不应该看到undo log的最小事务ID，也就是说小于该ID的事务undo log均可见。初始化时为当前还未分配的最小事务ID。
up_limit_id：所有小于该ID的事务undo log均可见。初始化的时候为trx_ids中最小事务ID，如果trx_ids不存在，则up_limit_id = low_limit_id。
trx_ids：当前read view创建的时候，其它处于未提交状态的事务ID列表，这些事务产生的undo log对当前事务是不可见的。
creator_trx_id：当前事务ID。

struct read_view_t{
    ulint       type;   /*!< VIEW_NORMAL, VIEW_HIGH_GRANULARITY */
    undo_no_t   undo_no;/*!< 0 or if type is
                VIEW_HIGH_GRANULARITY
                transaction undo_no when this high-granularity
                consistent read view was created */
    trx_id_t    low_limit_no;
                /*!< 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    low_limit_id;
                /*!< The read should not see any transaction
                with trx id >= this value. In other words,
                this is the "high water mark". */
    trx_id_t    up_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". */
    ulint       n_trx_ids;
                /*!< Number of cells in the trx_ids array */
    trx_id_t*   trx_ids;/*!< Additional trx ids which the read should
                not see: typically, these are the read-write
                active transactions at the time when the read
                is serialized, except the reading transaction
                itself; the trx ids in this array are in a
                descending order. These trx_ids should be
                between the "low" and "high" water marks,
                that is, up_limit_id and low_limit_id. */
    trx_id_t    creator_trx_id;
                /*!< trx id of creating transaction, or
                0 used in purge */
    UT_LIST_NODE_T(read_view_t) view_list;
                /*!< List of read views in trx_sys */
};

整体read view的算法可以参考mysql源码，这里就不详细介绍了。

数据库的隔离级别

上面简单介绍了一下锁和MVCC机制，下面我们根据不同的数据库隔离级别来分析下文章开头的那个问题。为了举例说明，这边简单创建一张表初始化内容如下：

CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT, // 主键
  `age` int(11) NOT NULL, // 二级索引，允许重复
  `name` varchar(50) NOT NULL, // 无索引
  PRIMARY KEY (`id`),
  KEY `idxAge` (`age`)
) ENGINE=InnoDB;

insert into test_lock(id, age, name) values(1, 20, 'A');
insert into test_lock(id, age, name) values(2, 20, 'B');
insert into test_lock(id, age, name) values(3, 18, 'C');
insert into test_lock(id, age, name) values(4, 22, 'A');
insert into test_lock(id, age, name) values(5, 17, 'B');

读未提交 - READ UNCOMMITTED

数据库最低隔离级别，顾名思义当前事务可以读取到其它事务未提交的更改数据。
在这个当前隔离级别下数据库不会采用快照读，所有的读操作都是直接读取最新的数据记录。

条件是主键

为了方便演示把问题的SQL改写成 delete from test_lock where id = 1，下同

RU_PK.png

可以看出当执行一条写操作的时候，会锁住主键索引的一条记录（X锁）。

条件是普通索引非唯一索引

为了方便演示把问题的SQL改写成 delete from test_lock where age = 20，下同

RU_INDEX.png

可以看到由于是普通索引，所以会先给查询到的符合条件的辅助索引记录上X锁，然后根据辅助索引找到对应的主键索引，再给主键索引符合条件的记录加上X锁。

条件无索引

为了方便演示把问题的SQL改写成 delete from test_lock where name = 'A'，下同

RU_NONE.png

遇到这种情况数据库会把全表索引记录加锁，可以看到这样开销是很大的。但是mysql对这种操作进行了优化，先锁住全表，再过滤出符合条件的记录，将不符合条件的记录提前释放掉X锁。只保留符合条件的记录锁。

读已提交 - READ COMMITTED

当前事务不可读取到其它事务未提交的数据。也就是说假如当前有事务T1正在修改数据A，然后再有一个事务T2读取A则只能读取到A被修改以前的数据。但是如果事务T1提交后，则T2再次读取就可以读到T1提交后的数据了。

对于此种数据库隔离级别，数据库加锁方式是跟RU是一模一样的。那么怎么区分RU和RC呢，它们最大的区别就是RU读取的是最新记录，而RC采用的就是快照读。也就是说RC采用了MVCC技术。