MySQL INSERT语句的锁

最新推荐文章于 2024-08-24 09:30:00 发布

遥不可及梦

最新推荐文章于 2024-08-24 09:30:00 发布

阅读量506

点赞数

分类专栏： Mysql 文章标签： mysql 数据库索引

本文链接：https://blog.csdn.net/u013246898/article/details/107519552

版权

本文详细探讨了MySQL中的INSERT语句如何处理表初始化、唯一键冲突、死锁等问题，特别是在涉及索引和临时表时的锁行为。重点讨论了循环写入时全表扫描导致的锁影响及优化方案，同时提到了INSERT INTO...ON DUPLICATE KEY更新语句的锁行为，强调了其在避免数据不一致和并发控制中的作用。

摘要由CSDN通过智能技术生成

INSERT…SELECT

表初始化

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;

INSERT INTO t VALUES (null,1,1);
INSERT INTO t VALUES (null,2,2);
INSERT INTO t VALUES (null,3,3);
INSERT INTO t VALUES (null,4,4);

CREATE TABLE t2 LIKE t;

操作时序

时刻	SessionA	SessionB
T1		`BEGIN;`
T2		`INSERT INTO t2(c,d) SELECT c,d FROM t;`
T3	`INSERT INTO t VALUES (-1,-1,-1);(Blocked)`

-- T3时刻
mysql> SELECT locked_index,locked_type,waiting_lock_mode,blocking_lock_mode FROM sys.innodb_lock_waits WHERE locked_table='`test`.`t`';
+--------------+-------------+-------------------+--------------------+
| locked_index | locked_type | waiting_lock_mode | blocking_lock_mode |
+--------------+-------------+-------------------+--------------------+
| PRIMARY      | RECORD      | X,GAP             | S                  |
+--------------+-------------+-------------------+--------------------+

T2时刻，session B会在表t加上PRIMARY:Next-Key Lock:(-∞,1]
如果没有锁的话，就可能会出现session B的INSERT语句先执行，但对应的binlog后写入的情况
1. binlog_format=STATEMENT，binlog里面的语句序列如下
  1. INSERT INTO t VALUES (-1,-1,-1)
  2. INSERT INTO t2(c,d) SELECT c,d FROM t
2. 这个语句传到备库执行，就会把id=-1这一行也会写到t2，主备不一致

INSERT循环写入

非循环写入

mysql> EXPLAIN INSERT INTO t2(c,d) (SELECT c+1,d FROM t FORCE INDEX(c) ORDER BY c DESC LIMIT 1);
+----+-------------+-------+------------+-------+---------------+------