MySQL 锁（InnoDB Locking）

MySQL 的锁.png

一、属性锁：Shared and Exclusive Locks

1.1 简介

shared locks 是共享锁，简称 S 锁，exclusive locks 是排它锁，简称 X 锁，它们既可以是表级锁，也可以是行级锁，在 MySQL 的 InnoDB 引擎中是行级锁，可以加在一行或者多行上，那么何时在一行上加锁，何时在多行上加锁，这需要根据索引情况而定

shared locks 允许持有某行 S 锁的事务读取（select）该行，exclusive locks 允许持有某行 X 锁的事务更新（update）和删除（delete）该行

InnoDB 支持通过特定的语句进行显式加锁：

• 显式加 X 锁：select ... for update
• 显式加 S 锁：select ... lock in share mode

1.2 S 锁和 X 锁的兼容性

共享锁和排它锁的兼容性列表如下表，该表表示：

• 如果事务 T1 获得了行 r 的 S 锁，另一个事务 T2 可以获取行 r 的 S 锁，但是不能获取行 r 的 X 锁。即 S 锁可以被多个事务共享，所以称为共享锁
• 如果事务 T1 获得了行 r 的 X 锁，另一个事务 T2 既不能获取行 r 的 S 锁，也不能获取行 r 的 X 锁，必须等待 T1 释放 X 锁，故称为排他锁

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

1.3 S 锁和 X 锁的兼容性示例

1.3.1 创建测试表

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `xid` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_xid`(`xid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test`(xid, name) VALUES (1, '1');
INSERT INTO `test`(xid, name) VALUES (5, '5');
INSERT INTO `test`(xid, name) VALUES (9, '9');

1.3.2 S 锁兼容性测试

分别打开两个会话（会话 A 和会话 B），在会话 A 中开启一个事务并执行：

select * from test where xid = 1 lock in share mode;

在会话 B 中开启一个事务并执行：

select * from test where xid = 1 lock in share mode;
select * from test where xid = 1 for update;

效果如下：

1.3.3 X 锁兼容性测试

分别打开两个会话（会话 A 和会话 B），在会话 A 中开启一个事务并执行：

select * from test where xid = 1 for update;

在会话 B 中开启一个事务并执行：

select * from test where xid = 1 lock in share mode;
select * from test where xid = 1 for update;

效果如下：

二、状态锁：Intention Locks

Intention Locks 称为意向锁，它是表级锁，顾名思义，它是用来锁定表的，与行级锁相对应

如果事务 T1 获取了一个表的 intention exclusive 锁（简称 IX 锁），相当于表级别的排它锁，那么事务 T2 就不能再获取表上的 S 和 X 锁了；

如果事务 T1 获取了一个表的 intention shared 锁（简称 IS 锁），那么事务 T2 可以获取表的 S 锁，但不能获取表的 X 锁，它与共享锁和排它锁的关系如下：

（1）一个事务获取一张表中某行的 S 锁之前，必须获取表的 IS 锁或者更强的锁（比如 IX）；

（2）一个事务获取一张表中某行的 X 锁之前，必须获取表的 IX 锁；

表级锁的兼容性如下：

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

当前事务 T1 想要修改某张表的一些行，那么首先要获取该表的 IX 锁，然后在要修改的行上加上 X 锁，另一个事务 T2 也准备要修改该表的一些行，因为表中除了被当前事务 T1 加锁的行，其他行是可以修改的，所以 T2 可以获取该表的 IX 锁，然后在其他行添加 X 锁，但是如果要修改当前事务 T1 加锁的行就需要等待了

三、算法锁

InnoDB 有三种锁行算法：

1. Record Lock：单个行记录上的锁
2. Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP 锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
3. Next-Key Lock：Record Lock 和 Gap Lock 的结合。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题

3.1 for update 简介

for update 可以显式地为表中满足条件的行加 X 锁。当一个事务的操作未完成时候，其他事务可以读取该行，但不能更新或删除该行

使用场景：高并发并且对于数据的准确性很有要求的场景。例如涉及到金钱、库存等。一般这些操作都是很长一串并且是开启事务的。如果库存刚开始读的时候是 1，而立马另一个进程进行了 update 将库存更新为 0 了，而事务还没有结束，会将错的数据一直执行下去，就会有问题。所以需要 for upate 进行数据加锁防止高并发时候数据出错

InnoDB 行锁是通过给索引项加锁来实现的，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁

3.2 for update 中算法锁的使用

场景一：明确指定索引，并且有此记录，加 Next-Key Lock

select * from test where xid = 5 for update;

插入操作

此时锁住的范围除了 ② 之外，还会锁住下一个范围，即 ③，这就是所谓的 Next-Key Lock。因此不能在另一个会话中插入 xid 在 1~9 范围内的数据。xid = 1 的数据不能插入，但 xid = 9 的数据可以插入，这可能与 B-Tree 索引的顺序插入有关。我们可以看下图描述的聚簇索引（自增 id）和 xid 的索引结构图：

个人理解（仅供参考）：xid = 1 的数据不能插入，因为主键索引 id 是自增的，因此在 id=2 这条记录之前，是不允许插入一条 xid=5 的记录，这样就破坏了主键索引 id 的有序性；xid = 9 的数据可以插入可能是因为 MySQL允许在 (id = 3, xid = 9) 的后面插入一条 (id = 4, xid = 9) 的数据，并不破坏主键索引的有序性

INSERT INTO `test`(xid, name) VALUES (1, '1');
INSERT INTO `test`(xid, name) VALUES (4, '4');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (9, '9');
INSERT INTO `test`(xid, name) VALUES (15, '15');

读取、更新和删除操作

事务 A 执行 select * from test where xid = 5 for update; 将 xid = 5 这一行锁定后，事务 B 可以执行 select，但不能执行 update 和 delete

select * from test where xid = 5;
update test set name = '5-1' where xid = 5;
delete from test where xid = 5;

事务 B 对其他行的操作（select、update 和 delete）则不受影响

场景二：使用范围条件而不是相等条件检索数据时，InnoDB 会给满足条件的索引行加锁，对于索引值在条件范围但不存在的行记录加 Gap Lock

select * from test where xid > 7 for update;

插入操作

此时针对索引 xid 使用的范围查找，会锁住 xid = 9 的行以及 ③、 ④ 。因此除了可以插入 xid = 3 的数据外，其余插入语句均不能执行

INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (8, '8');
INSERT INTO `test`(xid, name) VALUES (15, '15');

读取、更新和删除操作

事务 A 执行 select * from test where xid > 7 for update; 将 xid = 9 这一行锁定后，事务 B 可以执行 select，但不能执行 update 和 delete

select * from test where xid = 9;
update test set name = '9-1' where xid = 9;
delete from test where xid = 9;

事务 B 对其他不满足 xid > 7 的行的操作（select、update 和 delete）则不受影响

场景三：明确指定索引，若查无此记录，加 Gap Lock

select * from test where xid = 7 for update;

此时加锁区间是 ③ ，因此除了 xid = 6 和 xid = 8 无法插入外，其余均可执行

INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (8, '8');
INSERT INTO `test`(xid, name) VALUES (15, '15');

读取、更新和删除操作

Gap Lock 主要是为了防止其他事务在锁定范围内插入数据，不影响其他事务操作其他行数据

场景四：当查询的索引含有唯一属性（主键或唯一索引）的时候，Next-Key Lock 会进行优化，将其降级为 Record Lock，即仅锁住索引本身，不是范围

将 xid 改为 unique index：

ALTER TABLE `db_zll`.`test` 
DROP INDEX `idx_xid`,
ADD UNIQUE INDEX `idx_xid`(`xid`) USING BTREE;

select * from test where xid = 5 for update;

事务 A 执行 select * from test where xid = 5 for update; 只锁住 xid = 5 这一行，因此事务 B 对 xid = 5 的行只能 select，事务 B 对其他行的操作不受影响

INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (5, '5');
INSERT INTO `test`(xid, name) VALUES (7, '7');

行锁失效场景：

• 未指定主键/索引，并且有此记录，表级锁
• 无主键/索引，表级锁
• 主键/索引不明确，表级锁，例如 where xid like 、where xid <> 等操作

3.3 for update 超时回滚

超时时间的参数：innodb_lock_wait_timeout ，默认是50秒
超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF

默认情况下，InnoDB 存储引擎不会回滚超时引发的异常，除死锁外。当参数 innodb_rollback_on_timeout 设置成 ON 时，则可以回滚

3.4 for update 注意点

1. for update 仅适用于 InnoDB，并且必须开启事务，在 begin 与 commit 之间才生效

2. 当开启一个事务进行 for update 的时候，另一个事务也有 for update 的时候会一直等待，直到第一个事务结束吗？

   答：会的。除非第一个事务 commit 或者 rollback 或者断开连接，第二个事务会立马拿到锁进行后面操作。不过也可以设置锁等待超时参数innodb_lock_wait_timeout 来解决

3. 如果没查到记录会加锁吗？

   答：会的。有主键/索引产生间隙锁，无主键/索引产生表锁表级锁

4. for update 和 for update nowait 区别（前者阻塞其他事务，后者拒绝其他事务）

   for update 锁住表或者锁住行，只允许当前事务进行操作（读写），其他事务被阻塞，直到当前事务提交或者回滚，被阻塞的事务自动执行 for update nowait 锁住表或者锁住行，只允许当前事务进行操作（读写），其他事务被拒绝，事务占据的 statement 连接也会被断开