MySQL 插入记录，主键索引冲突加什么锁？
时间:2024-09-06 16:49 来源:未知 作者:IT
1. 准备工作
创建测试表：
CREATE TABLE `t1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`i1` int DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
插入测试数据：
INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);
2. 加锁情况
t1 表中已经有一条 <id = 10> 的记录，我们执行以下 insert 语句，再插入一条 <id = 10> 的记录。
begin;
insert into t1(id, i1) values (10, 1010);
因为新插入记录和表中原有记录存在主键冲突，执行 insert 语句之后，报错如下：
(1062, "Duplicate entry '10' for key 't1.PRIMARY'")
执行以下 select 语句查询加锁情况：
select
engine_transaction_id, object_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 247910
object_name | t1
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 10
lock_data = 10, lock_mode = S,REC_NOT_GAP 表示对 <id = 10> 的记录加了共享普通记录锁。
3. 原理分析
insert 语句执行过程中，插入记录到主键索引之前，需要先找插入记录的目标位置。
目标位置为表中主键字段值小于等于新插入记录中主键字段值的最后一条记录之后。
以示例 SQL 为例，插入主键字段 <id = 10> 的记录。
插入记录到主键索引之前，先找到表中 id 小于等于 10 的最后一条记录，也就是 <id = 10, i1="101"> 这条记录。新插入记录的目标位置就是这条记录之后。
InnoDB 发现表中已经有一条 <id = 10> 的记录，现在又要插入一条 <id = 10> 的记录，可是主键索引中不允许存在重复记录，这可怎么办才好？
直接报错吗？
那样简单粗暴就过于武断了。
InnoDB 还需要对表中 <id = 10> 的记录验明正身，确定它是一条有效的记录。
如果表中 <id = 10> 的记录已经被标记删除，只是还没有被清理，它就不是有效的记录了。这种情况下，新记录可以正常插入，不会报错。
否则，新记录和表中已有记录冲突，不能插入，就可以报错了。
为了防止其它事务更新或者删除这条记录，检查表中记录是否有效之前，InnoDB 会对这条记录加共享普通记录锁。
这就是示例 SQL 执行过程中对 <id = 10> 的记录加共享普通记录锁的原因。
如果表中 <id = 10> 的记录已经被标记删除，但是删除这条记录的事务还没有提交怎么办？
那我们看到的加锁情况就不一样了。
我们可以模拟下这个场景，创建 2 个 MySQL 连接，分别执行 delete 语句和 insert 语句。
-- 连接 1（事务 1）
begin;
delete from t1 where id = 10;
-- 连接 2（事务 2）
begin;
insert into t1(id, i1) values (10, 1010);
然后执行以下 select 语句查看加锁情况：
select
engine_transaction_id, object_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 247916
object_name | t1
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | WAITING
lock_data | 10
***************************[ 2. row ]***************************
engine_transaction_id | 247911
object_name | t1
lock_type | RECORD
lock_mode | X,REC_NOT_GAP
lock_status | GRANTED
lock_data | 10
事务 247911 执行删除操作对 <id = 10> 的记录加了排他普通记录锁。
事务 247916 想要对 <id = 10> 的记录加共享普通记录锁被阻塞，进入等待状态。
4. 总结
没有需要总结的内容了。
但是有两个问题：事务 247911 提交或者回滚之后，加锁情况是什么样的？为什么会这样？
(责任编辑：IT)
| 1. 准备工作 创建测试表： CREATE TABLE `t1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `i1` int DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, KEY `idx_i1` (`i1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 插入测试数据： INSERT INTO `t1` (`id`, `i1`) VALUES (10, 101), (20, 201), (30, 301), (40, 401); 2. 加锁情况 t1 表中已经有一条 <id = 10> 的记录，我们执行以下 insert 语句，再插入一条 <id = 10> 的记录。 begin; insert into t1(id, i1) values (10, 1010); 因为新插入记录和表中原有记录存在主键冲突，执行 insert 语句之后，报错如下： (1062, "Duplicate entry '10' for key 't1.PRIMARY'") 执行以下 select 语句查询加锁情况： select engine_transaction_id, object_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks where object_name = 't1' and lock_type = 'RECORD'\G ***************************[ 1. row ]*************************** engine_transaction_id | 247910 object_name | t1 lock_type | RECORD lock_mode | S,REC_NOT_GAP lock_status | GRANTED lock_data | 10 lock_data = 10, lock_mode = S,REC_NOT_GAP 表示对 <id = 10> 的记录加了共享普通记录锁。 3. 原理分析 insert 语句执行过程中，插入记录到主键索引之前，需要先找插入记录的目标位置。 目标位置为表中主键字段值小于等于新插入记录中主键字段值的最后一条记录之后。 以示例 SQL 为例，插入主键字段 <id = 10> 的记录。 插入记录到主键索引之前，先找到表中 id 小于等于 10 的最后一条记录，也就是 <id = 10, i1="101"> 这条记录。新插入记录的目标位置就是这条记录之后。 InnoDB 发现表中已经有一条 <id = 10> 的记录，现在又要插入一条 <id = 10> 的记录，可是主键索引中不允许存在重复记录，这可怎么办才好？ 直接报错吗？ 那样简单粗暴就过于武断了。 InnoDB 还需要对表中 <id = 10> 的记录验明正身，确定它是一条有效的记录。 如果表中 <id = 10> 的记录已经被标记删除，只是还没有被清理，它就不是有效的记录了。这种情况下，新记录可以正常插入，不会报错。 否则，新记录和表中已有记录冲突，不能插入，就可以报错了。 为了防止其它事务更新或者删除这条记录，检查表中记录是否有效之前，InnoDB 会对这条记录加共享普通记录锁。 这就是示例 SQL 执行过程中对 <id = 10> 的记录加共享普通记录锁的原因。 如果表中 <id = 10> 的记录已经被标记删除，但是删除这条记录的事务还没有提交怎么办？ 那我们看到的加锁情况就不一样了。 我们可以模拟下这个场景，创建 2 个 MySQL 连接，分别执行 delete 语句和 insert 语句。 -- 连接 1（事务 1） begin; delete from t1 where id = 10; -- 连接 2（事务 2） begin; insert into t1(id, i1) values (10, 1010); 然后执行以下 select 语句查看加锁情况： select engine_transaction_id, object_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks where object_name = 't1' and lock_type = 'RECORD'\G ***************************[ 1. row ]*************************** engine_transaction_id | 247916 object_name | t1 lock_type | RECORD lock_mode | S,REC_NOT_GAP lock_status | WAITING lock_data | 10 ***************************[ 2. row ]*************************** engine_transaction_id | 247911 object_name | t1 lock_type | RECORD lock_mode | X,REC_NOT_GAP lock_status | GRANTED lock_data | 10 事务 247911 执行删除操作对 <id = 10> 的记录加了排他普通记录锁。 事务 247916 想要对 <id = 10> 的记录加共享普通记录锁被阻塞，进入等待状态。 4. 总结 没有需要总结的内容了。 但是有两个问题：事务 247911 提交或者回滚之后，加锁情况是什么样的？为什么会这样？ (责任编辑：IT) |