本文均是在 Mysql + InnoDB 下讨论
行锁算法
-
Record Lock (记录锁): 单个记录上的锁
-
Gap Lock (间隙锁): ，锁定一个范围，但不包括记录本身
-
Next-Key Lock (临键锁): Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身
MVCC：快照读 vs 当前读
MySQL InnoDB 存储引擎，实现的是基于多版本的并发控制协议，MVCC (Multi-Version Concurrency Control)，MVCC下的读操作可以分成两类：
- 快照读 (snapshot read)，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
select * from table where ?;
- 当前读(current read)，读取的是记录的最新版本，会加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
锁的是索引
思考一下，以下两个SQL，如果最终查询到的记录一样，加的锁是一样的吗？
# RC/RR 隔离级别下
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> select * from user where id = 49 for update;
+----+------+
| id | name |
+----+------+
| 49 | Tom |
+----+------+
mysql> select * from user where name = 'Tom' for update;
+----+------+
| id | name |
+----+------+
| 49 | Tom |
+----+------+
分析
select * from user where id = 49 for update; # 只会锁住主键索引
select * from user where name = 'Tom' for update; # 除了主键索引，还会锁住二级索引
隔离级别与锁的关系
-
Read Uncommitted：
- 读不加锁，写加排他锁。
-
Read Committed (RC) ：
-
SELECT时为快照读。
-
针对当前读，RC 隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
-
-
Repeatable Read (RR) ：
-
SELECT时为快照读。
-
针对当前读，RR 隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
-
-
Serializable：
- 所有的读操作均为当前读，读加共享锁 (S 锁)，写加排他锁 (X 锁)。
# 查询当前隔离级别
select @@global.tx_isolation,@@tx_isolation;
# 设置隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
加锁分析
以下表作为实例，对各类查询的语句的加锁情况进行分析
CREATE TABLE `book` (
`id` int(11) unsigned NOT NULL,
`isbn` varchar(10) NOT NULL DEFAULT '',
`author` varchar(20) NOT NULL DEFAULT '',
`score` decimal(2,1) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_isbn` (`isbn`),
KEY `idx_author` (`author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> select * from book;
+----+-------+--------+-------+
| id | isbn | author | score |
+----+-------+--------+-------+
| 10 | N0001 | Bob | 3.4 |
| 18 | N0002 | Alice | 7.7 |
| 25 | N0003 | Jim | 5.0 |
| 30 | N0004 | Eric | 9.1 |
| 41 | N0005 | Tom | 2.2 |
| 49 | N0006 | Tom | 8.3 |
| 60 | N0007 | Rose | 8.9 |
+----+-------+--------+-------+
主键索引，查询命中
分析
RC/RR隔离级别：
- 事务1会对id = 10这个索引加排他记录锁，所以：场景1会锁等待，场景2不会锁等待。
主键索引，查询未命中
分析
RC隔离级别：
- 事务1不会加锁，所以：场景1/2/3，都不会锁等待。
RR隔离级别：
-
事务1会对(10,18)这个区间上间隙锁，间隙锁和间隙锁是互不冲突的，所以：场景1，不会锁等待。
-
间隙锁的作用就是为了防止其他事务的插入新行，导致幻读，所以：场景2/3，会产生锁等待。
二级唯一索引，查询命中
分析
RC/RR隔离级别：
- 事务会对二级索引(isbn = 'N0003')和主键索引(id = 25)都会加排他记录锁，所以。场景1/2都会产生锁等待。
二级唯一索引，查询未命中
分析
RC隔离级别：
- 事务1不会加锁，所以：场景1/2，都不会锁等待。
RR隔离级别：
-
事务1会对('N0007'&60, +∞)这个区间上间隙锁，所以：场景1会产生锁等待。
-
这种情况下并不会对主键上间隙锁，所以：场景2不会产生锁等待。
二级非唯一索引，查询命中
分析
RC隔离级别：
- 会对二级索引和主键索引加排他记录锁，所以：场景1/2，都会锁等待，场景3不会锁等待。
RR隔离级别：
- 除了记录以外，还会对二级索引的(Rose60, Tom41)、(Tom41, Tom49)、(Tom49, +∞)三个区间上间隙锁，所以：场景1/2/3都会锁等待。
二级非唯一索引，查询未命中
分析
RC隔离级别：
- 事务1不会加锁，所以：场景1/2，都不会锁等待。
RR隔离级别：
- 事务1会对(Rose61, Tom41)这个区间上间隙锁，间隙锁和间隙锁是互不冲突的，所以：场景1，不会产生锁等待，场景2会产生锁等待。
主键索引，范围查询
分析·
RC隔离级别：
- RC下与等值查询类似，会在ID=10、ID=18、ID=25索引上加排他记录锁，所以，场景1会锁等待，场景2/3不会锁等待。
RR隔离级别：
- 会在多个区间上间隙锁，间隙锁只影响插入，所以，场景1/3会发生锁等待，2不会锁等待。
二级索引，范围查询
分析
RC隔离级别：
- RC下与等值查询类似，会在ID=10、ID=18、ID=25、ID=30 索引上加排他记录锁，所以，场景1会锁等待，场景2/3不会锁等待。
RR隔离级别：
- 会在多个区间上间隙锁，所以，场景1/3会发生锁等待，2不会锁等待。
无索引
分析
前提：
-
在实际的实现中，MySQL 有一些优化(semi-consistent read)，在 MySQL Server 过滤条件，发现不满足后，会调用 unlock_row 方法，把不满足条件的记录释放锁 (违背了 2PL 的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。
-
semi-consistent read开启条件：
-
RC隔离级别
-
RR隔离级别 + 开启 innodb_locks_unsafe_for_binlog
RC隔离级别：
- 由于没有走索引，所以只能全表扫描，即使Mysql做了优化，id=60这条记录也会上锁，所以事务2再做全表扫描时会有锁等待，所以：场景1会锁等待，场景2不会锁等待。
RR隔离级别：
-
如果不开启innodb_locks_unsafe_for_binlog，则会锁所有记录与间隙，所以场景1/2都会锁等待。
-
如果开启innodb_locks_unsafe_for_binlog，则和RC一样，场景1会锁等待，场景2不会锁等待。
