40 insert语句的锁

40 insert语句的锁

上一篇文章中对mysql自增主键锁做了优化，尽量在申请到自增id后，就释放自增锁。

因此，insert语句是一个很轻量的操作，不过，这个结论对于”普通的insert”才生效，其他特殊的insert语句，在执行过程中需要给其他资源加锁，或者在无法申请到自增id以后就立马释放自增锁。

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

在隔离级别为rr下，binlog_format=statement

SESSION A

SESSION B

Insert into t values(-1,-1,-1);

Insert into t2(c,d) select c,d from 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循环写入

当然，执行insert...select的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

下面这一个sql语句

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

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

执行流程，从表t中按照索引c倒序，扫描第一行，拿到这个结果写到表t2中。扫描1行

把这一行插入到表t中

insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

用到了临时表，执行过程中，需要把表t的内存读出来，写入临时表。

explain结果里rows=1，受到了limit 1的影响。

 

执行前后增加了6行，innodb_rows_read，因为默认临时表使用memory引擎，所以这6行查的都是表t，进行全部扫描。

执行过程：

--1 创建临时表，字段c和d

--2 按照索引c扫描表t，依次取值，然后回表，读到c和d的值写入临时表，rows_examined=6

--3 由于语义里面有limit 1，所有只取了临时表的第一行，在插入到t中

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

优化方法，这个语句设计的数据量很小，可以考虑使用内存临时表来优化。

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

Insert唯一键冲突

对于有唯一键的表，插入数据时出现唯一性冲突比较常见，

SESSION A

SESSION B

insert into t values (10,10,10);

begin;

insert into t values (11,10,10);

ERROR 1062 (23000): Duplicate entry '10' for key 'c'

insert into t values (12,9,9);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这个例子在rc，rr下执行，session b都会进入block状态。

Session a在执行insert 发生唯一性冲突的时候，在冲突的索引上加了锁，一个next-key lock是由它的右边界的值定义的，session a持有索引c的(5,10]共享next-key lock（读锁）。

官方有一个错误的描述，认为如果冲突的是主键索引，就加记录锁，唯一索引才加next-key lock，但实际上，这两类索引冲突加的都是next-key lock。--已被官方认证。

一个经典的唯一性冲突死锁案例：

SESSION A

SESSION B

SESSION C

begin;

insert into t values (11,11,11);

T1

insert into t values (11,11,11);

insert into t values (11,11,11);

T2

rollback;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

T3

在session a执行rollback回滚的时候，session c几乎同时发现死锁并返回。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-03-27 06:10:07 0x7f3489976700
*** (1) TRANSACTION:
TRANSACTION 524813, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 37, OS thread handle 139863640983296, query id 16569 127.0.0.1 system update
insert into t values (11,11,11)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 661 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 524813 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 524814, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 36, OS thread handle 139863623427840, query id 16570 127.0.0.1 system update
insert into t values (11,11,11)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 661 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 524814 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 661 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 524814 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

set global innodb_print_all_deadlocks=1 

[mysql@mysqlt1 scripts]$ tail -n 100 /data/mysqldata/3306/log/mysql-error.log 
TRANSACTION 524813, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 37, OS thread handle 139863640983296, query id 16569 127.0.0.1 system update
insert into t values (11,11,11)
RECORD LOCKS space id 661 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 524813 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

TRANSACTION 524814, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 36, OS thread handle 139863623427840, query id 16570 127.0.0.1 system update
insert into t values (11,11,11)
RECORD LOCKS space id 661 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 524814 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 661 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 524814 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

死锁产生的逻辑：

--1 在T1时刻，启动session A，并执行insert语句，此时在索引上加了记录锁，这里是唯一索引，next-key 退化为记录锁

--2 在T2时刻，session B要执行相同的insert语句，发现了唯一性冲突，加上读锁；同样的，SESSION C也在索引上加读锁。--间隙锁加锁成功。

--3 在T3时刻，session A回滚，这时候，SESSION B和SESSION C都试图继续执行插入操作，都要加上写锁，两个session都要等待对方的行锁，就出现死锁。--pk，uk都一样的唯一性冲突死锁。

--虽然session在t1时刻没有提交，但是已经作为记录写进入。

Insert into 。。。on duplicate key update

上面的insert改写为

insert into t values(12,11,11) on duplicate key update d=100; 的话，就会给所有c上(11,11]加一个排他的next-key lock(写锁)。这个语句的逻辑是插入一行数据，如果碰到唯一性约束，就执行后面的更新语句。

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

--主键id，唯一索引12,1冲突，修改这一行

--主键id=13冲突，修改id=13这一行

主键是先判断的。

需要注意的是，执行这个语句的2 rows affected，实际上更新的只有一行，insert和update都认为成功了，所以update计数加1，insert计数加1。

小结：

上面介绍了几种insert的语句，insert 。。。Select是很常见的在两个表之间拷贝数据的方法，在事务隔离级别为rr下，会给select的表里扫描到的记录和间隙加读锁。

而如果insert和select是同一张表，则可能会造成循环写入，这种情况，可以引入临时表来做优化。

Insert语句如果出现唯一性冲突，会在冲突的唯一键上加共享的next-key lock（S锁），因此，碰到由于唯一性约束的报错后，要尽快提交或回滚事务，避免加锁时间过长。

两个表拷贝数据的常用工具pt-archiver，可以批量删除，插入等。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yhq1314/p/10912777.html