MySQL-InnoDB行锁

2020-07-09 本文已影响0人 ging_efcf

InnoDB的锁类型

InnoDB存储引擎支持行锁，锁类型有两种：

共享锁（S锁）
排他锁（X锁）

S和S不互斥，其他均互斥。

除了这两种锁以外，innodb还支持一种锁，叫做意向锁。

那么什么是意向锁？为什么需要意向锁呢？

考虑这种情况：
SessionA：已经持有表t某一行的X锁，需要对行进行更新操作
SessionB：想申请表t的表锁写锁

在没有意向锁之前，SessionA已经持有了行X锁以后，如果SessionB也成功，意味着SessionB可以对这个表中的所有数据进行更新操作，与SessionA的行锁是冲突的。

所以数据库在同意SessionB的申请之前，会做以下的判断：

检查是否有其他的表级写锁存在
判断表中每一行是否有行级X锁存在

步骤2的判断本身效率不高。所以产生了意向锁，在申请行级锁之前，先申请意向锁，成功之后才能申请行锁。（简单理解为多保存一个变量，便于申请表锁的时候进行快速判断）。

意向锁分为两种：

意向共享锁（IS锁）
意向排他锁（iX锁）

IS锁和IX锁之间彼此都不互斥，IS和IX只和表级别的读写锁互斥。其中：

IX和表X和表S互斥
IS和表X互斥

一致性锁定读和一致性非锁定读

一致性非锁定读

当隔离类型设置为读已提交和可重复读时，我们写的普通的select语句，不会申请锁，也不会被阻塞，会从MVCC选择一个视图读取数据。
两种隔离状态的区别在于：

读已提交永远拿到的是最新的视图
可重复读永远使用事务刚建立时拿到的视图

一致性锁定读

当写这两种select语句时，会进行加锁操作。

select ... lock in share mode
- 这个语句表示加入一个S锁，其他事务也可以加S锁
- 使用场景：不同表之间的相同数据保持一致性时使用（存疑，暂时放着）
select ... for update
- 这个语句表示加入一个X锁，其他事务不能读也不能写
- 使用场景：先读，根据读结果进行修改的操作时可以使用

行锁的三种算法（判断锁定的范围）

InnoDB存储引擎有三种行锁的锁定算法：

record lock：锁定单行
gap lock：锁定范围，不包含记录本身
next-key lock：gap lock + record lock 既锁定范围又锁定记录本身

当select操作需要加锁时，会按照上面的next-key lock进行加锁。

当查询条件中有索引，并且是唯一索引时，可以只锁定单条记录，由next-key lock降级为 record lock。
当查询条件中有索引，并且索引是辅助索引时，不仅锁住记录本身，还会锁定记录前一个索引键值范围（不包括区间头部的值），除此之外，还会锁定记录值到下一个索引键值的范围（不包括区间尾端的值）；同时还会在主键索引的对应行上加record lock

实验验证

输入下列建表语句：

create table z(a int,b int,primary key(a),key(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

在sessionA中，输入下面查询语句

select * from z where b=3 for update;

在sessionB中，输入下面查询语句

select * from z where b=1 for update;
select * from z where b=3 for update;
insert into z (a,b) values(4,2);
select * from z where b=6 for update;

其中，第一条和第四条可以正常返回，第二条第三条语句会阻塞，因为SessionA一直没有commit，所以这两条语句阻塞一段时间后会报锁等待超时异常。

mysql> select * from z where b=1 for update;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 3 |    1 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from z where b=6 for update;
+---+------+
| a | b    |
+---+------+
| 7 |    6 |
+---+------+
1 row in set (0.00 sec)

mysql> select * from z where b=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into z (a,b) values(4,2);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结果分析
查询条件为b=3，所以会使用b列的辅助索引来查询，找到记录3后

使用record lock锁定主键索引a=5的记录
使用next-key lock锁定辅助索引（1,3）3 （3,6）这个范围的记录

在SessionA提交事务之前，这个范围内的记录都加的是X排他锁，所以第二条和第三条记录都需要阻塞等待。