Home Posts Tags

Contents

MySQL InnoDB Locks

frankma included in mysql
   5919 words   12 minutes 
/images/mysql-innodb-locks.png

并发控制

并发的任务对同一个资源进行操作，不加措施就会导致前后数据不一致。所以，要进行并发控制。

并发控制保证数据一致性的手段：

  • 锁 Locking
  • 数据多版本 Multi Version

锁

1、普通锁

  • 操作数据前，锁住，实施互斥，不允许其他的并发任务操作
  • 操作完成后，释放锁，让其他任务执行。

产生的问题：简单的锁过于粗暴，读任务也无法并行，所有操作本质是串行的。为了提高并发，于是出现了S和X锁。如下

2、共享锁 和 排它锁

  • 共享锁(shared locks 记为读锁 或 S锁)，读数据加S锁
  • 排它锁(exclusive locks 记为写锁 或 X锁)，修改数据加X锁

S和X的互斥性：

  • S锁之间不互斥，即：读读可以并行
  • X锁与S/X互斥，即：写读，写写 不可以并行

产生的问题：写数据时候，不能读数据。这会降低并发度。为了进一步提高并发，于是出现了MVCC(Multi-Version Concurrency Control)。如下

数据多版本

核心：

  1. 写数据前，先clone一份，并加版本号区分
  2. 写数据操作新clone的数据，直至提交
  3. 读任务 继续读取旧版本的数据，不加锁不阻塞

实现：

首先要介绍redo、undo、回滚段：

1、redo日志：事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID。为了提高性能，将修改行为先写到redo日志里，再定期flush到磁盘上，思路就是：随机写优化成顺序写。

一句话，redo日志用于保障，已提交事务的ACID特性。

2、undo日志：事务未提交时，会将修改前的旧版本数据 存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。

一句话，undo日志用于保障，未提交事务不会对数据库的ACID特性 产生影响。

3、rollback segment：回滚段是存储undo日志的地方。

所以，现在就好理解MVCC的snapshot read，读的旧版数据是存储在rollback segment里的。

总结

为了提高并发，出现的技术思路 实现路线如下：

  • 普通锁，本质是串行执行
  • 读写锁，实现读读并发
  • 数据多版本，实现读写并发

扩展: 快照读在RC与RR下的差异

前提：

  • InnoDB基于MVCC，普通的select是snapshot read，不加锁。
  • RC：read commit。数据库的一种事务隔离级别。保证了事务A commit前，也可以读取到事务B commit的数据。可能出现幻读
  • RR：repeated read。数据库的一种事务隔离级别。保证了事务A commit前，读取不到事务B commit的数据，实现了事务的完全隔离性。也解决了幻读问题，保证事务中两次的read出相同数据集。

先说下结论：

  • 在自己的事务中，snapshot read总能读到cud的记录。这很好理解：自己操作的自己当然可以读到
  • RC/RR下，快照读产生的结果 和 上面说的RC/RR 保证的行为一致。再说一遍：RC保证事务A可以读到事务B commit的数据；RR保证事务A读取不到事务B commit的数据。

看下例子就清楚了：

1
2
3
4
5
6
7
8
9

-- t表里有数据{1,2,3}

A1: start transaction;
         B1: start transaction;
A2: select * from t;
         B2: insert into t values (4);
A3: select * from t;
         B3: commit;
A4: select * from t;

RC下：A2、A3都是{1,2,3}。A4是{1,2,3,4}

RR下：A2、A3、A4都是{1,2,3}

InnoDB的七种锁

自增锁

Auto-Inc Locks

InnoDB里每个有自增长的表都有一个 自增长计数器(auto-increment counter)。在MySQL<=5.7，这个counter存在于内存中，所以每次需要初始化通过SELECT MAX(ai_col) FROM table_name FOR UPDATE;。

插入操作会根据上面的counter值+1再赋给自增列。这个实现方式操作AUTO-INC Locking。它是一种特殊的表锁机制，注意：为了提高性能，该锁的释放是在 插入SQL执行完后立即释放，而不是在事务完成后释放。。但是对于并发插入还会存在性能问题，所以innodb提供了一种轻量级互斥量的自增长实现机制。

innodb_autoinc_lock_mode提供了配置自增长采用的机制。详解官方文档

这里只说下默认的innodb_autoinc_lock_mode配置：

  • 对于简单的insert，采用互斥量进行counter累加操作
  • 对于插入前不知道插入行数的语句：如insert…select/LOAD DATA。还是使用的AUTO-INC Locking表锁的方式。

总结：对于平时开发简单的insert插入，即使在事务中，也是使用的mutex来累加counter的。所以并发性好，平时不用考虑。

共享/排他锁

前面说的并发控制的第二种锁，为了进一步提高并发。所以有了行级锁。

innodb实现了标准的行级锁：共享(S)/排它(X)锁

  • S锁：事务 拿到 S锁，才能 读 这一行
  • X锁：事务 拿到 X锁，才能 修改/删除 这一行

它们之间的互斥表如下：

SX
S兼容互斥
X互斥互斥

简单总结上表：

  • 多个事务都可拿到S锁，即 读读可以并行
  • 只有一事务可拿到X锁，即 写写 / 读写 不能并行

注意：

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

意向锁

上面的共享/排它锁都是行锁，此外，innodb提供多粒度锁定。通俗来说就是，在表级提供一种粗粒度的控制，允许事务在获取表锁后，还能获取到行锁。

1、理解：

所以innodb推出了一种表锁 意向锁：可以理解未来时刻，事务要加某些行加S/X锁，先提前声明一个表级的意向(IS/IX)。

理解意义前提：事物在申请一行的行锁时，会自动先开始申请表的意向锁

即：

  • 事务要获得某些行的S锁，必须先获得表的IS锁
  • 事务要获得某些行的X锁，必须先获得表的IX锁

所以现在粗浅理解 它的意义：在表锁需要和行锁 相互约束时，通过表级的意向锁提供一种快速的 判别方式。即：为了表明某个事物 正在锁定某行 或 将要锁定某行

理解意义举例：

1
2
3
4
5
6
7
8
9

事物A加了行的读锁，事物B想要表的写锁。
如果B申请成功。就可以对A锁的行修改，这显然不满足ACID的。所以事物B要阻塞。

但是数据库如何判断这个冲突呢？假设分为2步：
step1：判断表 是否 已经有表锁
step2：判断表里的 每一行 是否 已经有行锁

这种效率很低 需要遍历整个表。所以有了意向锁，这样后，判断只要1步：
step1：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞

2、特点：

  • 意向锁是个 表级锁
  • 分为意向排它锁IX/意向共享锁IS，即 事务有意向对某些行加X/S锁
  • 数据库在获取行的S/X锁是，会先获取表的IS/IX锁

3、IS/IX互斥表

ISIX
IS兼容兼容
IX兼容兼容

看出意向锁是比较弱的锁，意向锁之间可以并行。

4、IS/IX 与 表级的S/X锁 互斥表 参考

SX
IS兼容互斥
IX互斥互斥

5、测试意向锁通过locking reads

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 前提：
select ... lock in share mode; -- 对读取的行加S锁，从而要设置IS锁；
select ... for update; -- 对读取的行加X锁，从而要设置IX锁；

-- session A：
begin;
select * from user where id = 1 lock in share mode;


-- session B：
LOCK TABLES user WRITE; -- 此时就会阻塞 直到session A的IS锁释放。

记录锁

介绍record lock、gap lock、next-key lock前先说下innodb的索引

1. 索引类型

innodb有两类索引：

  • 聚集索引 clustered index
  • 普通索引 secondary index

注意：innodb的每一个表都会有聚集索引。

  • 如果表定义了PK，则PK就是 聚集索引
  • 如果表没定义PK，则第一个非空unique列是聚集索引
  • 否则，innodb会创建一个隐藏的row-id作为聚集索引

2. 索引对应到B+树的位置

首先 背景前提：索引B+树中，非叶子节点 存储key，叶子节点 存储value

  • 聚集索引：叶子节点 存储 行记录(row)
  • 普通索引：叶子节点 存储 PK的值

理解：行数据存储在聚集索引上，普通索引通过找到聚集索引 进而找到row

所以，在select...where name='xxx'时候，会先通过name的普通索引查到PK，在通过聚集索引查到对应的row。

形象展示 聚合索引 和 普通索引：

https://raw.githubusercontent.com/Fedomn/misc-blog-assets/master/mysql-innodb-index.png

注意树中 索引顺序，它也是 树结构 在分组/排序/比较 SQL时 快于哈希索引的原因。延伸innodb索引为什么是B+树

注意：我们对二级索引加锁时，也会同时作用在聚集索引上。 如下图：

聚集索引+S聚集索引+X
二级索引+S兼容互斥
二级索引+X互斥互斥

3. 介绍记录锁 record locks

前面说索引的目的是：记录锁、间隙锁、临键锁 它们都是行锁的三种算法，基于索引实现。

先说record locks：它会在 索引记录上加锁，从而阻止其他事物 对索引上的row 进行修改删除。

For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

这里有个容易忽视的点：insert也是和update、delete一样尝试对索引加X锁。

间隙锁

间隙锁 gap locks：它会锁定 一段范围内的索引记录。它锁住的是一个区间。

例如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

有表：user(id PK, name KEY, age)
有数据：
id	age	name
1	1	1
3	3	3
5	5	5
10	10	10


select * from user where name between 3 and 5 for update;
会演变成 
select * from user where name>=3 and name<=5 for update;

进而变成许多个临键锁，在拼接成一个双开范围的锁：(1,10)。其中3/5加的记录锁。

注意：间隙锁，我们单独测试不了，它在innodb里存在于临键锁里的。

目的：为了防止不可重复读，前提隔离级别>=RR。

不论唯一还是非唯一索引，都会有gap锁。

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.

A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap.

临键锁

临键锁 next-key locks：它是 记录锁 和 间隙锁 的组合。即 会锁住记录和范围。

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

其实，在每个数据行上的索引 都会存在一个潜在的 临键锁。如一个索引有10、11、13、20。则潜在的临键锁区间为：(-∞, 10)、(10, 11)、(11, 13)、(13, 20)、(20, +∞), 目的也是防止幻读，前提>=RR。同时10/11/13/20上还有记录锁存在。

例如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

有表：user(id PK, name KEY, age)
有数据：
id	age	name
1	1	1
3	3	3
5	5	5
10	10	10

事务A：
update user set age = 555 where name = '5'; 
或
select * from user where name = '5' lock in share mode;
或
select * from user where name = '5' for update;

事务B：
INSERT INTO `user` (`age`, `name`) VALUES (7, '7');

分析：事务A在name=5上加了记录锁。同时又加了(3, 10)的间隙锁，最后形成了(3, 10)临键锁。所以，这时候事务B的insert name='7’ 就会阻塞住了。

这里又存在一个问题：(3, 10)的间隙锁目的是什么？因为我们的sql where并不是一个范围，所以也不会有幻读问题。只要加name='5’的记录锁 就可以了。

分析上面的原因：临键锁 只能在 非唯一的二级索引上，你想嘛，如果不锁，就会在insert name=5，就会导致了不可重复读了。但是也会造成我们insert name=7阻塞住。这是由于MySQL 间隙锁的最小单位是gap，所以也就会产生一些副作用。

插入意向锁

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

对数据的修改和删除，必须加互斥锁X锁，保证数据一致性。对于数据的插入，是否还需要这么强的锁，为了提高并发，所以有了插入意向锁。

插入意向锁，是间隙锁的一种，实施在索引上，专门针对insert。它与gap-lock和next-gap-lock冲突。

特点：

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

表同上 如 ： INSERT INTO user (age, name) VALUES (6, '6'); 和 INSERT INTO user (age, name) VALUES (7, '7'); 同一个的gap，但是事务A和B插入时不会阻塞。目的，为了提高并发。

总结

InnoDB 的锁，与索引类型，事务的隔离级别相关。InnoDB 到底是行锁还是表锁取决于你的 SQL 语句。如果查询没有命中索引，也将退化为表锁。InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。所以如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

常见问题

MySQL锁不存在的行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mysql版本5.7

id  age name
10	10	10
13	NULL	111

session A:
begin;
select * from user where id = 12 for update;

session B:
begin;
// insert会阻塞，不存在的记录会存在next-key locks
INSERT INTO `user` (`id`, `age`, `name`) VALUES (11, 100, '7'); 
INSERT INTO `user` (`id`, `age`, `name`) VALUES (12, 100, '7'); 
// select不会阻塞
select * from user where id = 12 for update;

讨论 与 详解—实际中会出现deadlock—解决select+insert ignore

通过对不存在的行，MySQL锁处理的逻辑，进一步深入理解：next-gap locks

上面介绍了：next-gap lock = gap lock + record lock。next-gap lock 和 gap lock的引入是为了帮助我们解决幻读问题，也就是说它们存在于RR隔离级别下。

回到上面的demo，select * from user where id = 12 for update;由于 id=12这条记录不存在，因此会加上(10, 13)的gap lock。因此在此gap内插入数据会被阻塞住。同时select * from user where id = 12 for update;不会被阻塞。

看出gap lock有些特殊，它的互斥条件是：向这个gap内插入记录这个动作。 与行锁与行锁互斥，这种冲突概念有些不同。

从这个角度，也可以分析上面statckoverflow上deadlock的原因：两个事物都获得了gap lock，两个又同时会尝试向gap里insert，所以都被对方的gap lock给锁住，从而死锁。

用动态的视角去看加锁

修改一个unique key会加什么锁

update u2 set age = 11 where id = 1; 这是会给id和age都加上X锁。

行锁组成

  1. 行锁类型
1
2

LOCK_S：共享锁
LOCK_X: 排他锁
  1. GAP类型å
1
2
3
4

LOCK_GAP：只锁间隙
LOCK_REC_NO_GAP:只锁记录
LOCK_ORDINARY: 锁记录和记录之前的间隙
LOCK_INSERT_INTENTION: 插入意向锁，用于insert时检查锁冲突

每个行锁由锁类型和GAP类型组成 例如：

  • LOCK_X | LOCK_ORDINARY 表示对记录和记录之前的间隙加排他锁
  • LOCK_S | LOCK_GAP 表示只对记录前的间隙加共享锁

锁的兼容性： 值得注意的是，持有GAP的锁（LOCK_GAP和LOCK_ORDINARY)与其他非LOCK_INSERT_INTENTION的锁都是兼容的，也就是说，GAP锁就是为了防止插入的。

普通insert与update阻塞关系

关键的一句话：常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；

所以：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

// session1: not block
INSERT INTO `shedlock` (`name`, `lock_until`, `locked_at`, `locked_by`)
VALUES ('ad', '2019-12-17 14:14:13', '2019-12-17 13:14:13', 'fedomn');

// session2: will block
update shedlock set lock_until = "2019-12-17 14:14:13", locked_at = '2019-12-17 13:14:13' where name = 'ad' and lock_until <= '2019-12-17 13:14:13'; 

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 601523:6891:3:2
lock_trx_id: 601523
  lock_mode: X
  lock_type: RECORD
 lock_table: `shedlock`
 lock_index: PRIMARY
 lock_space: 6891
  lock_page: 3
   lock_rec: 2
  lock_data: 'ad'
*************************** 2. row ***************************
    lock_id: 601522:6891:3:2
lock_trx_id: 601522
  lock_mode: X
  lock_type: RECORD
 lock_table: `shedlock`
 lock_index: PRIMARY
 lock_space: 6891
  lock_page: 3
   lock_rec: 2
  lock_data: 'ad'
2 rows in set, 1 warning (0.01 sec)

Reference

Updated on 2020-03-12
 mysql
Back | Home