insert唯一键冲突的加锁情况分析

AsiaYe

发布于 2020-06-12 17:54:05

2.4K1

发布于 2020-06-12 17:54:05

文章被收录于专栏：DBA随笔DBA随笔

insert唯一键冲突的加锁情况分析

今天分享的内容是MySQL里面insert语句在发生冲突的时候加锁情况，废话就不多说了，直接从例子开始吧。

首先创建表t，其中id为主键，c为唯一索引，然后插入5条数据，

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    5 |
+----+------+------+
5 rows in set (0.00 sec)

接下来测试锁发生的场景：

从上面的图中不难看出，session A上的第一个insert操作是成功的，这容易理解，因为没有并发，表中的最大值是5。第二个insert操作因为重复的键值而报错，因为c=10的记录已经有了，按理说发生唯一键冲突之后，这条语句失败了，应该不对MySQL有影响才对，实际上，这个insert语句做了两件事情：

1、报唯一键冲突错误，返回

2、在(5,10]这个区间加上了next_key的共享锁

其中，第二点可以从show engine innodb status看出来：

------------
TRANSACTIONS
------------
Trx id counter 254732
Purge done for trx's n:o < 254730 undo n:o < 0 state: running but idle
History list length 208
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283902019123864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 254731, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 3124, query id 28 localhost ::1 root update
insert into t values (12,9,9)
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139 page no 4 n bits 80 index c of table `test`.`t` trx id 254731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

------------------
---TRANSACTION 254730, ACTIVE 89 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 4, OS thread handle 8676, query id 18 localhost ::1 root cleaning up

上面的结果中，可以看到，当前该表上一共有2个锁结果，分别是x locks gap before rec和record lock，也就是(5,10)之间的间隙锁和c=10的记录锁。

还有一点要说明，在本例子中，冲突的是唯一索引，如果是主键冲突，那么聚集索引上也要添加共享next_key锁，而不是记录锁。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-06-08，如有侵权请联系 cloudcommunity@tencent.com 删除

sql