mysql insert语句 锁_MySQL实战45讲Day39----insert语句的锁

一、insert … select 语句：

对于表t，t2：

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

在可重复读隔离级别下，binlog_format = statement时执行insert into t2(c,d) select c,d from t;时需要对表t的所有行和间隙加锁的原因：

对于这个执行序列：

  实际的执行效果是，如果session B先执行，由于这个语句对表t主键索引加了(-∞,1]这个next-key lock，会在语句执行完成后，才允许session A的insert语句执行。

但如果没有锁的话，就可能出现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、对于语句：insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);//往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1。:

这个语句的加锁范围，就是表t索引c上的(4,supremum]这个next-key lock和主键索引上id=4这一行。

该语句的执行流程是：从表t中按照索引c倒序，扫描第一行，拿到结果写入到表t2中。

该语句执行的慢查询日志(slow log)为：

  因此整条语句的扫描行数是1。(Rows_examined :1)

2、对于语句：insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);:

该语句执行的慢查询日志(slow log)为：

因此整条语句的扫描行数是5。(Rows_examined :5)

该语句的explain结果为：

该语句的执行过程为：

创建临时表，表里有两个字段c和d。

按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表。这时，Rows_examined=4。

由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中。这时，Rows_examined的值加1，变成了5。

注意：

这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。

执行需要临时表的原因：这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。

由于实现上这个语句没有在子查询中就直接使用limit 1，从而导致了这个语句的执行需要遍历整个表t。它的优化方法也比较简单，就是先insert into到临时表temp_t，这样就只需要扫描一行；然后再从表temp_t里面取出这行数据插入表t1。

三、insert 唯一键冲突：

1、

唯一键冲突加锁

在可重复读(repeatable read)隔离级别下执行。可以看到，session B要执行的insert语句进入了锁等待状态。session A执行的insert语句，发生主键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。而由于一个next-key lock就是由它右边界的值定义的。这时候，session A持有索引c上的(5,10]共享next-key lock(读锁)。

加这个读锁，从作用上来看，这样做可以避免这一行被别的事务删掉。

2、

唯一键冲突--死锁

现象：在session A执行rollback语句回滚的时候，session C几乎同时发现死锁并返回。

死锁产生的逻辑：

在T1时刻，启动session A，并执行insert语句，此时在索引c的c=5上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁。

在T2时刻，session B要执行相同的insert语句，发现了唯一键冲突，加上读锁；同样地，session C也在索引c上，c=5这一个记录上，加了读锁。

T3时刻，session A回滚。这时候，session B和session C都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁。

流程的状态变化图：

四、insert into … on duplicate key update：

对于语句：insert into t values(11,10,10) on duplicate key update d=100;,

会给索引c上(5,10] 加一个排他的next-key lock(写锁)。

insert into … on duplicate key update;这个语义的逻辑是插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

假设现在表t里面已经有了(1,1,1)和(2,2,2)这两行，下面这个语句执行的效果：

两个唯一键同时冲突

可以看到：

主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行。

需要注意的是，执行这条语句的affected rows返回的是2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert和update都认为自己成功了，update计数加了1， insert计数也加了1。