锁列表
- 共享与列排他锁
- 意向锁
- 记录锁
- 间隙锁
- 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 updateshow 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
------------------注意
- 如果表中没有显示的定义索引，那么InnoDB会自动创建一个聚集索引，并将这个聚集索引用作记录锁。
- 记录锁总是锁聚集索引（也可以说是主键），因为二级索引都关联了主键索引，这样保证所有事务都获得相同的锁。