唯一约束锁分析
MySQL：8.0.20 MySQL Community Server - GPL
隔离级别：RC
8版中本有performance.schema.data_locks 表，方便分析锁问题
创建测试数据：
mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `od` int DEFAULT NULL, `name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `od` (`od`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> insert into t1(id,od,name) values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'),(100,100,'fff');
1、insert 唯一键不冲突
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(id,od,name) values(50,50,'def'); Query OK, 1 row affected (0.00 sec)
performance.schema.data_locks 表 查询结果如下
| 库名 | 表名 | INDEX_NAME | lock_type | lock_mode | LOCK_STATUS | LOCK_DATA |
| ceshi | t1 | NULL | TABLE | IX | GRANTED | NULL |
2、insert into 主键冲突
mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1(id,od,name) values(4,4,'d'); ERROR 1062 (23000): Duplicate entry '4' for key 't1.PRIMARY'
| 库名 | 表名 | INDEX_NAME | lock_type | lock_mode | LOCK_STATUS | LOCK_DATA |
| ceshi | t1 | NULL | TABLE | IX | GRANTED | NULL |
| ceshi | t1 | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 4 |
3、insert into 唯一约束冲突
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(id,od,name) values(400,4,'d'); ERROR 1062 (23000): Duplicate entry '4' for key 't1.od'
| 库名 | 表名 | INDEX_NAME | lock_type | lock_mode | LOCK_STATUS | LOCK_DATA |
| ceshi | t1 | NULL | TABLE | IX | GRANTED | NULL |
| ceshi | t1 | od | RECORD | S | GRANTED | 4 |
4、replace into ... 主键/唯一约束冲突
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> replace into t1(id,od,name) values(4,4,'ddddd'); Query OK, 2 rows affected (0.00 sec)
| 库名 | 表名 | INDEX_NAME | lock_type | lock_mode | LOCK_STATUS | LOCK_DATA |
| ceshi | t1 | NULL | TABLE | IX | GRANTED | NULL |
| ceshi | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| ceshi | t1 | od | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| ceshi | t1 | od | RECORD | X,GAP | GRANTED | 4, 4 |
| ceshi | t1 | od | RECORD | X | GRANTED | 100, 100 |
5、replace into ... 唯一约束冲突
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> replace into t1(id,od,name) values(null,4,'ddddd'); Query OK, 2 rows affected (0.00 sec)
| 库名 | 表名 | INDEX_NAME | lock_type | lock_mode | LOCK_STATUS | LOCK_DATA |
| ceshi | t1 | NULL | TABLE | IX | GRANTED | NULL |
| ceshi | t1 | od | RECORD | X | GRANTED | 4, 4 |
| ceshi | t1 | od | RECORD | X | GRANTED | 100, 100 |
| ceshi | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| ceshi | t1 | od | RECORD | X,GAP | GRANTED | 4, 101 |
6、同一事务 delete / insert into 唯一约束
| 库名 | 表名 | INDEX_NAME | lock_type | lock_mode | LOCK_STATUS | LOCK_DATA |
| ceshi | t1 | NULL | TABLE | IX | GRANTED | NULL |
| ceshi | t1 | od | RECORD | X,REC_NOT_GAP | GRANTED | 4, 4 |
| ceshi | t1 | od | PRIMARY | X,REC_NOT_GAP | GRANTED | 4 |
| ceshi | t1 | od | RECORD | S,GAP | GRANTED | 4, 4 |
| ceshi | t1 | od | RECORD | S | GRANTED | 100, 100 |
