本文基于MySQL8.0.19，下面通过一个例子讲解 MySQL record lock(记录锁) 的加锁方式
现在有个表t
CREATE TABLE t(id INT UNSIGNED PRIMARY KEY ,NAME VARCHAR(100));
INSERT INTO t VALUES(1,'a'),(4,'b'),(7,'c'),(10,'d'),(20,'e'),(30,'f');
数据如下：
mysql> SELECT * FROM t;
+----+------+
| id | NAME |
+----+------+
| 1 | a |
| 4 | b |
| 7 | c |
| 10 | d |
| 20 | e |
| 30 | f |
+----+------+
6 rows in set (0.00 sec)
SESSION1:
SET @@session.transaction_isolation = 'read-committed';
SHOW VARIABLES LIKE '%iso%';
BEGIN;
DELETE FROM t WHERE id<9;
SESSION2:
SET @@session.transaction_isolation = 'read-committed';
SHOW VARIABLES LIKE '%iso%';
BEGIN;
DELETE FROM t WHERE id=10; ---不被阻塞
------------------------------------我是分割线--------------------------
把上面SESSION1，SESSION2执行顺序交换，会发现SESSION2会被阻塞
SESSION1:
SET @@session.transaction_isolation = 'read-committed';
SHOW VARIABLES LIKE '%iso%';
BEGIN;
DELETE FROM t WHERE id=10;
SESSION2:
SET @@session.transaction_isolation = 'read-committed';
SHOW VARIABLES LIKE '%iso%';
BEGIN;
DELETE FROM t WHERE id<9; ---被阻塞
如果你是ORACLE DBA你一定会非常困惑，因为在Oracle里面是不会被阻塞的，而在MySQL中居然被阻塞了，是不是很奇怪
为什么DELETE FROM t WHERE id<9会被阻塞呢？这是因为id列是主键，这条SQL会直接扫描id列的主键索引(默认升序扫描)
where id<9 会从索引叶子块从左往右扫描 意思就是会扫描 1，4，7，10，到10就停止，因为10>9
因为 SESSION1 已经持有了id=10的记录锁，SESSION2 要申请 id=1 id=4 id=7 id=10 的记录锁 ，两个SESSION都要申请id=10的记录锁，所以SESSION2会被阻塞，通过查询数据字典也可以验证这个理论：
mysql> SELECT object_schema,object_name,lock_type,lock_mode,lock_status,lock_data FROM performance_schema.data_locks;
+---------------+-------------+-----------+---------------+-------------+-----------+
| object_schema | object_name | lock_type | lock_mode | lock_status | lock_data |
+---------------+-------------+-----------+---------------+-------------+-----------+
| test | t | TABLE | IX | GRANTED | NULL |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 7 |
| test | t | RECORD | X,REC_NOT_GAP | WAITING | 10 |
| test | t | TABLE | IX | GRANTED | NULL |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+---------------+-------------+-----------+---------------+-------------+-----------+
7 rows in set (0.00 sec)
知道了MySQL加record lock的内部原理之后，我们就可以通过一些特殊手段来避免锁争用
DELETE FROM t WHERE id<9 的执行计划如下：
mysql> EXPLAIN DELETE FROM t WHERE id<9;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | DELETE | t | NULL | range | PRIMARY | PRIMARY | 4 | const | 3 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
默认会从索引的最左边扫描，也就是扫描 1 4 7 10 ，因为要扫描到10会被阻塞，那我反向扫描呢？
BEGIN;
DELETE FROM t WHERE id<9 ORDER BY id DESC; ---成功了，没被阻塞
因为反向扫描只需要扫描7，4，1，不需要扫描10，也就是说不会对10加锁
DELETE FROM t WHERE id<9 ORDER BY id DESC 的执行计划如下：
mysql> EXPLAIN DELETE FROM t WHERE id<9 ORDER BY id DESC;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------------------------+
| 1 | DELETE | t | NULL | range | PRIMARY | PRIMARY | 4 | const | 3 | 100.00 | Using where; Backward index scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
查看锁数据字典：
mysql> SELECT object_schema,object_name,lock_type,lock_mode,lock_status,lock_data FROM performance_schema.data_locks;
+---------------+-------------+-----------+---------------+-------------+-----------+
| object_schema | object_name | lock_type | lock_mode | lock_status | lock_data |
+---------------+-------------+-----------+---------------+-------------+-----------+
| test | t | TABLE | IX | GRANTED | NULL |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 7 |
| test | t | TABLE | IX | GRANTED | NULL |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+---------------+-------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)
总结：MySQL 加 record lock 是在扫描索引的时候申请锁资源，在索引key上获取锁资源之后再会根据where条件释放掉不满足条件的key上面的锁
最后，如果是RR模式，SESSION2 不会被阻塞，因为SESSION2 id=10加的是GAP锁，SESSION1 id=10加的是REC_NOT_GAP锁，GAP锁与REC_NOT_GAP锁可以兼容
mysql> SELECT object_schema,object_name,lock_type,lock_mode,lock_status,lock_data FROM performance_schema.data_locks;
+---------------+-------------+-----------+---------------+-------------+-----------+
| object_schema | object_name | lock_type | lock_mode | lock_status | lock_data |
+---------------+-------------+-----------+---------------+-------------+-----------+
| test | t | TABLE | IX | GRANTED | NULL |
| test | t | RECORD | X | GRANTED | 1 |
| test | t | RECORD | X | GRANTED | 4 |
| test | t | RECORD | X | GRANTED | 7 |
| test | t | RECORD | X,GAP | GRANTED | 10 |
| test | t | TABLE | IX | GRANTED | NULL |
| test | t | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+---------------+-------------+-----------+---------------+-------------+-----------+
7 rows in set (0.00 sec)