MySQL -- INSERT语句的锁
INSERT…SELECT
表初始化
1 | CREATE TABLE `t` ( |
操作序列
时刻 | session A | session B |
---|---|---|
T1 | BEGIN; | |
T2 | INSERT INTO t2(c,d) SELECT c,d FROM t; | |
T3 | INSERT INTO t VALUES (-1,-1,-1); (Blocked) |
1 | -- T3时刻 |
- T2时刻,session B会在表t加上
PRIMARY:Next-Key Lock:(-∞,1]
- 如果没有锁的话,就可能会出现session B的INSERT语句先执行,但对应的binlog后写入的情况
binlog_format=STATEMENT
,binlog里面的语句序列如下INSERT INTO t VALUES (-1,-1,-1)
INSERT INTO t2(c,d) SELECT c,d FROM t
- 这个语句传到备库执行,就会把id=-1这一行也会写到t2,_主备不一致_
INSERT循环写入
非循环写入
1 | mysql> EXPLAIN INSERT INTO t2(c,d) (SELECT c+1,d FROM t FORCE INDEX(c) ORDER BY c DESC LIMIT 1); |
- 加锁范围为在表t上
c:Next-Key Lock:(3,4]
+c:Next-Key Lock:(4,+∞]
- 执行流程比较简单,从表t中按索引c倒序扫描第一行,拿到结果后写入到表t2,整个语句的扫描行数为1
循环写入
1 | -- MySQL 5.7上执行 |
Using temporary
表示用到了临时表,执行过程中,需要把表t的内容读出来,写入临时表- 实际上,
EXPLAIN
结果里的rows=1
是因为受到了LIMIT 1
的影响 - 语句执行前后,
Innodb_rows_read
的值增加了4,因为临时表默认使用的是Memory引擎- 这4行数据查的是表t,即对表t做了全表扫描
- 执行流程
- 创建临时表,表里有两个字段
c
和d
- 按照索引c扫描表t,依次取出c=4,3,2,1,然后回表,读到c和d的值写入临时表
- 此时,
Rows_examined=4
- 此时,
- 由于有
LIMIT 1
,所以只会取临时表的第一行,再插入到表t- 此时,
Rows_examined=5
- 此时,
- 创建临时表,表里有两个字段
- 该语句会导致在表t上做全表扫描,并且会给索引c上的所有间隙都加上
Share Next-Key Lock
- 在这个语句执行期间,其它事务不能在这个表上插入数据
- 需要临时表的原因
- 一边遍历数据,一边更新数据
- 如果读出来的数据直接写回原表,可能在遍历过程中,读到刚刚插入的记录
- 新插入的记录如果参与计算逻辑,就会与原语义不符
优化方案
1 | CREATE TEMPORARY TABLE temp_t(c INT,d INT) ENGINE=Memory; |
INSERT唯一键冲突
时刻 | session A | session B |
---|---|---|
T0 | SELECT * FROM t; | |
T1 | INSERT INTO t VALUES (10,10,10); | |
T2 | BEGIN; | |
T3 | INSERT INTO t VALUES (11,10,10); (Duplicate entry ‘10’ for key ‘c’) |
|
T4 | INSERT INTO t VALUES (12,9,9); (Blocked) |
1 | -- T0时刻 |
- session A要执行的INSERT语句,发生唯一键冲突,并不是简单地报错返回,还需要在冲突的索引上加锁
- 一个
Next-Key Lock
由它的右边界定义的,即是c:Shared Next-Key Lock:(5,10]
INSERT死锁
时刻 | session A | session B | session C |
---|---|---|---|
T0 | TRUNCATE t; | ||
T1 | BEGIN; INSERT INTO t VALUES (null,5,5); |
||
T2 | INSERT INTO t VALUES (null,5,5); | INSERT INTO t VALUES (null,5,5); | |
T3 | ROLLBACK; | Deadlock found |
- 在T1时刻,session A执行
INSERT
语句,在索引c=5
上加上行锁(索引c是唯一索引,可以退化为行锁) - 在T2时刻,session B和session C执行相同的
INSERT
语句,发现唯一键冲突,等待加上读锁 - 在T3时刻,session A执行
ROLLBACK
语句,session B和session C都试图继续插入执行操作,都要加上写锁- 但两个session都要等待对方的读锁,所以就出现了死锁
INSERT INTO…ON DUPLICATE KEY
1 | TRUNCATE T; |
参考资料
《MySQL实战45讲》
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.