MySQL InnoDB中的锁-记录锁

锁列表

• 共享与列排他锁
• 意向锁
• 记录锁
• 间隙锁
• Next-Key锁
• 插入意向锁
• AUTO-INC锁

这次我们只来讨论和实验意向锁。

记录锁

记录锁是锁定某个具体的索引记录，用来阻止其他事务的增、删、改。

实验

以下实验基于MySQL 8.0.x版本。
建表语句：

CREATE TABLE `sys_user` ( `id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名', 
`name_pinyin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名拼音',
`id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机号', 
PRIMARY KEY (`id`), 
UNIQUE KEY `uni_idx_id_card` (`id_card`) USING BTREE COMMENT '唯一索引-身份证号', 
KEY `idx_phone_name` (`phone`,`name`) USING BTREE COMMENT '普通索引-手机号' ) ENGINE=InnoDB AUTO_INCREMENT=3495 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户'

表结构：

MySQL [employees]> desc sys_user;
+-------------+--------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id              | int             | NO   | PRI | NULL    | auto_increment |
| name         | varchar(10)   | NO   |      | NULL    |                |
| name_pinyin | varchar(255) | NO   |     | NULL    |                |
| id_card       | varchar(255) | NO   | UNI | NULL    |                |
| phone        | varchar(20)   | YES  | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.25 sec)

索引结构:

MySQL [employees]> show indexes from sys_user;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+---------+------------+
| Table    | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment             | Visible | Expression |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+---------+------------+
| sys_user |          0 | PRIMARY         |            1 | id          | A         |        3494 |     NULL |   NULL |      | BTREE      |         |                           | YES     | NULL       |
| sys_user |          0 | uni_idx_id_card |            1 | id_card     | A         |        3494 |     NULL |   NULL |      | BTREE      |         | 唯一索引-身份证号         | YES     | NULL       |
| sys_user |          1 | idx_phone_name  |            1 | phone       | A         |        3493 |     NULL |   NULL | YES  | BTREE      |         | 普通索引-手机号           | YES     | NULL       |
| sys_user |          1 | idx_phone_name  |            2 | name        | A         |        3493 |     NULL |   NULL |      | BTREE      |         | 普通索引-手机号           | YES     | NULL       |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+---------+------------+
4 rows in set (4.29 sec)

记录锁

事务A：先添加一个IX锁

begin;
select * from sys_user where name='张三' for update

事务B：然后执行SQL

begin;
select * from sys_user where name='张三' for update

show engine innodb status输出：

---TRANSACTION 129420645, ACTIVE 36 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5680, OS thread handle 139734815254272, query id 6936008 192.168.1.83 root executing
select * from sys_user where name='张三' for update
------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 7 n bits 328 index PRIMARY of table `employees`.`sys_user` trx id 129420645 lock_mode X waiting
Record lock, heap no 2
------------------

注意

1. 如果表中没有显示的定义索引，那么InnoDB会自动创建一个聚集索引，并将这个聚集索引用作记录锁。
2. 记录锁总是锁聚集索引（也可以说是主键），因为二级索引都关联了主键索引，这样保证所有事务都获得相同的锁。

参考

1. https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks
2. https://www.infoq.cn/article/zau0ewzsdtx9zofr6c8w