数据库锁的总结

本章的讨论基于InnoDB，事务隔离界别为RR(可重复读)。

1.1 基本锁

1.1.1 共享锁

Shared Locks，S锁，读锁

锁的目标是行

MySQL语法：select…lock in share mode

1.1.2 排他锁

Exclusive Locks，X锁，写锁

锁的目标是行

MySQL语法：select…for update

1.1.3 说明

加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁；
加了X锁的记录，不允许其他事务再加S锁或者X锁。

1.2 意向锁

Intention Locks，以下为MySQL官网文档说明

InnoDB supports multiple granularity locking which permits coexistence of row-level locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table-level locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require later for a row in that table. There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t):
Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.
Intention exclusive (IX): Transaction T intends to set X locks on those rows.

Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t. Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

意向锁分为意向共享锁（IS锁）和意向排他锁（IX锁）。根据官方说明，事务在请求S锁和X锁前，需要先获得对应的IS、IX锁。

意向锁是表级锁，个人理解他的作用是为了防止行锁和表锁的冲突。即对外表明表锁现在处于不可获取状态。

1.3 记录锁

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

存在于包括主键索引在内的唯一索引中，仅针对单条索引记录，并且锁住的是索引而不是记录本身。即使表上没有任何索引，innnodb也会创建一个隐藏的主键索引。

1.4 间隙锁

是Innodb在可重复读的隔离级别下为了解决幻读问题时引入的锁机制，间隙锁不互斥。加锁的规则：

1. 加锁的基本单位是（next-key lock）,他是前开后闭原则
2. 插叙过程中访问的对象会增加锁
3. 索引上的等值查询--给唯一索引加锁的时候，next-key lock升级为行锁
4. 索引上的等值查询--向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

1.5 临键锁

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.

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows。

record lock + gap lock, 左开右闭区间。默认情况下，innodb使用next-key locks来锁定记录。
但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

1.6 案例说明

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL,
  `name` int(32) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

id(primary key)	name (key)	value
1	1	1
5	5	5
10	10	10
15	15	15

---

隐藏的临键锁：

(-infinity, 1]
(1, 5]
(5, 10]
(10, 15]
(15, +infinity]

---

1.6.1 唯一索引等值查询且有结果

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#正常 INSERT INTO `test` VALUES (3, 3, 3);	#正常 INSERT INTO `test` VALUES (8, 8, 8);
commit;

当使用等值查询且存在结果时，只使用记录锁，不产生间隙锁。

---

1.6.2 唯一索引等值查询不存在结果

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE `id` = 7 FOR UPDATE; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 INSERT INTO `test` VALUES (8, 8, 8);	#正常 update test set value = value +1 where id = 10;
commit;

当使用等值查询且不存在结果时，使用临键锁。

id=7不存在记录，即加临键锁 (5, 10]

由于数据是等值查询，并且范围的最后数据id=10不满足要求，退化为间隙锁(5,10)

id = 8 写入失败，写id=10 更新成功

---

1.6.3 唯一索引范围查询

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE id < 13 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(14,14,14);	#阻塞 update test set value = value +1 where id = 15;
commit;

命中的区间为(-infinity, 13)，13命中的临键锁区间为(10, 15]，因为是范围查询不是等值查询，所以不会降级为间隙锁(10, 15)，综合看锁止的区间为(-infinity, 15]；

---

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE id > 13 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(11,11,11);	#正常 update test set value = value +1 where id = 10;
commit;

命中的区间为(13, +infinity)，13命中的临键锁区间为(10, 15]，所以综合看锁止的区间为(10, +infinity)；

---

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE id < 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(9,9,9);	#正常 insert into test values(11,11,11);
commit

命中的区间是（-infinity, 10]，因为查询范围位于临键锁的边界，但只是小于不包含等于，所以未命中边界，不向右遍历一个区间。

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE id <= 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(9,9,9);	#阻塞 insert into test values(11,11,11);	#阻塞 update test set value = value +1 where id = 15;
commit

命中的区间是（-infinity, 10]，因为查询范围位于临键锁的边界且包含等于，所以命中边界，向右遍历一个区间，最终是（-infinity, 15]。

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE id > 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#正常 insert into test values(9,9,9);	#阻塞 insert into test values(11,11,11);	#正常 update test set value = value +1 where id = 10;
commit

命中的区间是（10,+infinity)，因为查询范围位于临键锁的边界但不包含等于，所以未命中边界，最终是（10,+infinity)。

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE id >= 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#正常 insert into test values(9,9,9);	#阻塞 insert into test values(11,11,11);	#正常 update test set value = value +1 where id = 10;
commit

命中的区间是[10,+infinity)，因为查询范围位于临键锁的边界且包含等于，所以命中边界，但加的是行锁，最终是[10,+infinity)。

---

1.6.4 普通索引等值查询存在结果

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name = 5 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(3,3,3);	#阻塞 insert into test values(9,9,9);	#正常 update test set value = value +1 where name = 10;
commit;

普通索引等值查询会直接使用间隙锁，并且会向右遍历一个区间。

name = 5 命中(1, 5]，向右遍一个区间为(5,10]，因为是等值查询且10不满足条件，所以降级为(5,10)，综合来看锁止的空间为(1,10)，所以事务B和C会被阻塞，事务D正常。

---

1.6.5 普通索引等值查询不存在结果

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name = 6 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#正常 insert into test values(3,3,3);	#阻塞 insert into test values(9,9,9);	#正常 update test set value = value +1 where name = 10;
commit;

name = 6 不存在记录，所以命中的区间为(5,10]，因为10 不满足条件，所以降级为(5,10)。事务B和D正常，事务C被阻塞。

---

1.6.6 普通索引 范围查询

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name < 13 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(3,3,3);	#阻塞 insert into test values(14,14,14);	#阻塞 update test set value = value +1 where name = 15;
commit;

name < 13 表示的范围为 (-infinity, 13)，因为13 位于(10,15]区间，并且非等值查询，即使15未命中也不会降级为开区间，综合看锁止的范围为(-infinity, 15]。

---

---

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name > 13 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(11,11,11);	#正常 update test set value = value +1 where name = 10;
commit;

name > 13 表示的范围为(13, +infinity)，13命中的间隙范围为(10,15]，所以综合看，锁定的范围为(10, +infinity)，事务B阻塞，事务C正常。

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name < 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#正常 insert into test values(11,11,11);	#正常 update test set value = value +1 where name = 15;	#阻塞 update test set value = value +1 where name = 10;
commit;

name < 10 表示的范围为(-infinity, 10)，因为是范围查询，命中的临键锁区间是(-infinity, 10]

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name <= 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(11,11,11);	#阻塞 update test set value = value +1 where name = 15;
commit;

name <= 10 表示的范围为(-infinity, 10]，因为10是临键锁区间上界，有等值查询，所以向右遍历一个区间(10, 15]，因为是范围查询所以即使15未命中也不会退化为间隙锁，综合区间是(-infinity, 15]

事务A	事务B	事务C
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name > 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(11,11,11);	#正常 update test set value = value +1 where name = 10;
commit;

锁定的范围是(10,+infinity)

事务A	事务B	事务C	事务D
/* 开启事务 */ BEGIN; /* 查询并加记录锁 */ SELECT * FROM `test` WHERE name >= 10 for update; /* 延迟60秒执行，用于模拟事务执行中*/ SELECT SLEEP(60);
	#阻塞 insert into test values(8,8,8);	#阻塞 insert into test values(11,11,11);	#正常 update test set value = value +1 where name = 5;
commit;

锁定的范围是(5,+infinity)

---

综合上述案例，可以总结一下几点：

• 唯一索引下等值查询优先使用行锁（即记录存在使用行锁）；行锁不满足升级为间隙锁（注意不是临键锁，是左右都开的区间）；
• 唯一索引下范围查询直接使用

	唯一索引	普通索引
等值查询有结果	记录锁	间隙锁，取值的前后相邻两个区间
等值查询无结果	间隙锁，取值所在的区间	临键锁，取值所在的区间
<x	(-infinity, y] x在表中存在时，y = x x在表中不存在时，y为x所在区间的上界	(-infinity, y] x在表中存在时，y = x x在表中不存在时，y为x所在区间的上界
<=x	(-infinity, y] x在表中存在时，y为x所在区间向右一个区间的上界 x在表中不存在时，y为x所在区间的上界	(-infinity, y] x在表中存在时，y为x所在区间向右一个区间的上界 x在表中不存在时，y为x所在区间的上界
>x	(y, +infinity)， x在表中存在时，y=x x在表中不存在时，y为x所在区间的下界	(y, +infinity)， x在表中存在时，y=x x在表中不存在时，y为x所在区间的下界
>=x	x在表中存在时，[x, +infinity) x在表中不存在时，(y, +infinity) y为x所在区间的下界	x在表中存在时，(y, +infinity)，y为x左侧区间的下界 x在表中不存在时，(y, +infinity) ，y为x所在区间的下界

2.insert on duplicate key update死锁分析

insert 的加锁过程官方文档：

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. 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. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

简单说就是insert执行时会给目标行加排他锁，是记录锁；不过在insert执行前，会先加insertion intention gap lock，意向插入锁，是间隙锁。

如果发生了唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得该行的排它锁，那么将会导致死锁。

insert into xxx values(...) on duplicate key update .....  产生死锁的原因分析

事务A	事务B
acquire X lock; duplicate; release X lock; acquire S lock;
	acquire X lock; duplicate; release X lock; acquire S lock;
prepare update; try acqure X lock;	prepare update; try acqure X lock;
waiting S lock from B	waiting S lock from A