innodb的锁讲解

原创

shixiazhoujun 2019-02-23 11:23:38 博主文章分类：MySQL ©著作权

文章标签 MySQL. innodb lock 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者shixiazhoujun的原创作品，请联系作者获取转载授权，否则将追究法律责任

innodb的锁，我们可以从几个维度来分析，分为级别，类型
- 级别
  - 行级锁
  - 表级锁
- 类型
  - 共享锁(S)，也称为写锁, 级别:行级锁
  - 意向共享锁(IS)，也称为意向写锁级别:表级锁
  - 排他锁(X)，也称为读锁属于行级锁级别: 行级锁
  - 意向排他锁(IX)，也称为意向读锁级别: 表级锁
- 行锁的算法
  - Record Locks
  - Gap Locks
  - Next-Key Locks
  - Insert intention Locks
  - AUTO-INC Locks
  - Predicate Locks for Spatial Indexes
锁的兼容性请看下图
我们就先从类型讲起
- 共享锁
  - 允许持有锁的事务读取行
  - 假如有一个a事务获取了x行的共享锁，这时候b事务也来请求x行的锁，那么会进行一下处理
    - 如果b请求的是x行的共享锁，那么会立刻授予，这时候a事务和b事务都拥有x行的共享锁
    - 如果b请求的是x的排他锁，那么不会立刻授予，因为共享锁和排他锁是不兼容的
- 排他锁
  - 允许持有锁的事务更新或删除行。
  - 假如有一个a事务获取了x行的排他锁，这时候b事务也来请求x行的锁，这时候不管b事务请求的锁是共享锁还是排他锁，都不能立即授予，只能等到a事务释放了在x行的排他锁才能授予b事务，因为排他锁与任何的锁都不兼容
- 意向锁的讲述
  - innodb支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同行存在，为了支持在不同粒度上进行操作，innodb存储引擎支持一个额外的锁方式，称之为意向锁，意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁，也就是如果一个事务需要针对记录R来加排他锁，那么就需要对记录R所在的数据库，表，页进行加锁，最后对记录R加排他锁，如果有任何一个部分导致等待，那么该操作需要粗粒度锁的完成。
  - 意向锁是表级锁，设计目的主要是为了在下一个事务中揭示下一行将被请求的锁类型，由于innodb存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求。
  - 上面的可能不好理解，下面我说一个实际的例子
    - 我们有一个student表
    - mysql> show create table student;
    - +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    - | Table | Create Table |
    - +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    - | student | CREATE TABLE student (
    - id int(11) NOT NULL AUTO_INCREMENT,
    - student_num int(11) NOT NULL DEFAULT '0' COMMENT '学号',
    - name varchar(32) NOT NULL DEFAULT '' COMMENT '学生姓名',
    - PRIMARY KEY (id),
    - UNIQUE KEY uqidx_student_num (student_num)
    - ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
    - +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    - 1 row in set (0.00 sec)
    - mysql> select * from student;
    - +----+-------------+----------+
    - | id | student_num | name |
    - +----+-------------+----------+
    - | 1 | 1 | zhangsan |
    - | 2 | 2 | lisi |
    - | 3 | 3 | wangwu |
    - | 4 | 4 | zhaoliu |
    - | 5 | 5 | liqi |
    - +----+-------------+----------+
    - 5 rows in set (0.00 sec)
    - session1
      - mysql> start transaction;
      - Query OK, 0 rows affected (0.00 sec)
      - mysql> select * from student where student_num=4 for update;
      - +----+-------------+---------+
      - | id | student_num | name |
      - +----+-------------+---------+
      - | 4 | 4 | zhaoliu |
      - +----+-------------+---------+
      - 1 row in set (0.00 sec)
    - session2
      - mysql> LOCK TABLE student write;
    - 这时候我们发现session2一直在等待，因为session2想获取student整个表的写锁，如果session2申请成功了，它是可以修改student表的任意一行的，那么大家会说session1已经获取了student_num=4的排他锁呢，如果session2申请成功了，那么student_num=4的记录也会被session2修改了，所以说这时候session2肯定是会阻塞的，那么数据库是根据什么方法来判断使得session2被阻塞呢，无非就下面两种方法
      1. 判断这个表是否被其他事务用表锁给锁住
      2. 判断这个表的每一行是否有行锁
    - 根据前面的讲述，假如我们要针对某一条记录加排他锁的话，那么会在记录对应的表里面先加一个共享排他锁，然后再到记录上面加一个排他锁，这时候我们可以发现session1可以针对student表加了一个共享排他锁了，那么这时候session2发现student上面已经有其他事务加上共享排他锁了，因此会阻塞。如果没有意向锁的话，那session2就要走方法2了，那么就需要判断每一行，那么需要遍历整个表，这种效率非常差，特别碰到表数据量大的时候。
- 意向共享锁
  - 事务想要获取一个表中某几行的共享锁
- 意向排他锁
  - 事务想要获取一个表中某几行的排他锁
- 请注意：对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的 Select 语句，InnoDB 不会加任何锁，可能有人会有疑问，什么是一般的select，什么是特殊的select呢，一般的select就是select column from table where x=1 特殊的select就是select column from table where x=1 lock in share mode或者select column from table where x=1 for udpate ,前面加共享锁，后者加排他锁
行锁
- Record Locks
  - 记录锁是索引记录上的锁，例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;防止任何其他事务插入，更新或删除t.c1的值为10的行
  - 记录锁始终锁定索引记录，如果一个表没有定义任何的索引，像这种情况，innodb会创建一个隐藏的聚簇索引并且使用此索引进行记录锁定
  - 需要注意的是:
    - innodb的记录锁是针对索引加锁，不是针对物理记录加锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，将出现锁冲突
- Gap Locks
  - 间隙锁是锁定索引记录之间的间隙，或锁定第一个索引记录之前或最后一个索引记录之后的间隙上。。例如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;由于范围内所有现有值之间的间隔都被锁定，因此可以防止其他事务向列t.c1中插入值15，无论该列中是否已有任何此类值。
  - 对于使用唯一索引锁定行的语句，不需要使用间隙锁(这不包括搜索条件仅包括多列唯一索引的一些列的情况; 在这种情况下，确实会出现间隙锁定），例如，如果id列是一个唯一索引，那么下面的语句只会在id=100的那行上面加一个索引记录锁,而不会关心别的会话（session）是否在前面的间隙中插入数据。
  - SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
  - 如果id没有索引或者没有唯一索引，则该语句将锁定前述的间隙
  - 间隙锁的主要作用，就是和记录锁组成Next-key锁，解决幻读问题
  - 间隙锁在不同的隔离级别下，有着不同的作用范围，能发挥间隙锁作用的，是’REPEATTABLE READ’隔离级别，在这个级别下使用带有间隙锁的Next-Key锁，解决了幻行的问题。这个涉及到了事务隔离级别和一致读的相关信息，后面我也会更新对应的文章
- Next-Key Locks
  - next-key锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合,也就是相当于Record Locks+Gap Locks
  - InnoDB以这种形式实现行级锁，当它查找或扫描表索引的时候，它会在遇到的索引记录上设置共享或排它锁。因此，行级锁实际上是索引记录锁。next-key锁同样会影响索引记录之前的间隙。就是说，next-key 锁就是一个索引记录锁加上索引记录前间隙的间隙锁。如果一个会话拥有记录 R 的索引上面的一个共享锁或独占锁，另一个会话不能在索引顺序中的R之前的间隙中插入新的索引记录。
  - 下面我们来看一个表
  - mysql> create table goods(
  - ```
  -> id int not null auto_increment primary key,
```
- ```
-> title varchar(32) not null default '' comment  '商品名称',
```
  - ```
  -> classify tinyint not null default 0  comment  '商品类型',
```
- ```
-> index `idx_classify` (`classify`)
```
  - ```
  -> )engine=innodb  charset=utf8;
```
- mysql> insert into goods (title,classify) values ('商品1',1),('商品2',3),('商品3',5),('商品4',8),('商品5',10),('商品6',1),('商品7',3),('商品8',5),('商品9',8),('商品10',10);
- mysql> select * from goods;
- +----+----------+----------+
- | id | title | classify |
- +----+----------+----------+
- | 1 | 商品1 | 1 |
- | 2 | 商品2 | 3 |
- | 3 | 商品3 | 5 |
- | 4 | 商品4 | 8 |
- | 5 | 商品5 | 10 |
- | 6 | 商品6 | 1 |
- | 7 | 商品7 | 3 |
- | 8 | 商品8 | 5 |
- | 9 | 商品9 | 8 |
- | 10 | 商品10 | 10 |
- +----+----------+----------+
- 10 rows in set (0.00 sec)
- mysql> select distinct(classify) from goods;
- +----------+
- | classify |
- +----------+
- | 1 |
- | 3 |
- | 5 |
- | 8 |
- | 10 |
- +----------+
- 5 rows in set (0.02 sec)
- #在REPEATTABLE READ隔离级别下，执行查询时，因为Next-Key锁存在，写Next-Key的锁定范围如下
  - (-∞,1) 锁定索引项1和1之前的间隙，因为1之前没有其他索引项，所以负无穷
  - (1,3) 锁定1和3之前的间隙，不包括1，包括3
  - (3,5) 同上
  - (5,8) 同上
  - (8,10) 同上
  - (10,∞) 锁定索引项10和10之后的间隙，因为10之后没有其他索引项，所以为正无穷
- 下面我们就用一个demo来验证一下
  - Session1
    - mysql> start transaction;
    - Query OK, 0 rows affected (0.00 sec)
    - mysql> select * from goods where classify=3 for update;
    - +----+---------+----------+
    - | id | title | classify |
    - +----+---------+----------+
    - | 2 | 商品2 | 3 |
    - | 7 | 商品7 | 3 |
    - +----+---------+----------+
    - 2 rows in set (0.00 sec)
    - #对于辅助索引，其加的是Next-Key锁，锁定的范围是(1,3),特别需要注意的是，innodb存储引擎还会对辅助索引下一个键值加上gab lock，即还有一个辅助索引范围(3,5)的锁
  - Session2
    - mysql> start transaction;
    - Query OK, 0 rows affected (0.00 sec) mysql> insert into goods (title,classify) select '商品11',4;
    - #这时候可以看到session2给阻塞了，因为session1的Next-Key的锁定范围是(1,3),(3,5)，正好包含了4
    - mysql> insert into goods (title,classify) select '商品11',6;
    - Query OK, 1 row affected (0.00 sec)
    - Records: 1 Duplicates: 0 Warnings: 0
    - 插入classify=6的就立刻成功了，因为6不在(1,3),(3,5)的范围内
- Insert intention Locks
  - Insert intention 锁是插入行之前由INSERT操作设置的一种间隙锁。。此锁表示要插入的意图是多个事务插入到相同的索引间隙时, 如果它们没有插入到间隙中的同一位置, 则不必等待对方。。假设存在值为4和7的索引记录。两个事务分别尝试插入5和6，分别用插入意向锁锁住4和7之间的间隙，然后再取得插入行的排它锁，但是相互不会阻塞，因为这些行是不冲突的。
- AUTO-INC Locks
  - AUTO-INC锁是由插入到带有 AUTO_INCREMENT 列的表中的事务所采取的特殊表级锁。。一个最简单的例子，如果一个事务正在向表中插入值，则任何其他事务必须等待对该表执行自己的插入，以便第一个事务插入的行接收连续的主键值。
- Predicate Locks for Spatial Indexes
  - innodb支持空间索引，如果使用Next-Key来支持空间索引，则不能满足要求，这是因为普通的索引都是键值类型，意味着索引存在一个方向，这个方向是单向的，要不升序要不降序，这个方向的存在，使得数据库存储引擎可以利用索引进行常规的范围查询
  - 但是在空间数据类型上面，这个单向的有序变得失去了作用，因为空间数据是多维多向的，是以区域或空间为范围的，没有确定的方向顺序，所以单向的Next-Key满足不了要求
  - 空间索引是建立爱MBR上的，innodb为索引项的MBR增加了一个谓词锁，实现空间索引上的并发控制
参考资料
- 链接：
  - https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
- 书籍：
  - 高性能MySQ
  - MySQL技术内幕 innoDB存储引擎
  - 数据库事务处理的艺术事务管理与并发控制