insert语句会锁表吗，insert违反唯一约束条件

大家好，今天来为大家解答insert语句会锁表吗这个问题的一些问题点，包括insert违反唯一约束条件也一样很多人还不知道，因此呢，今天就来为大家分析分析，现在让我们一起来看看吧！如果解决了您的问题，还望您关注下本站哦，谢谢~

MYSQL insert into select 锁表问题

加锁情况与死锁原因分析

为方便大家复现，完整表结构和数据如下：

CREATE TABLE `t3`(

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY(`c1`),

UNIQUE KEY `c2`(`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1执行 commit的瞬间，我们会看到 session2、session3的其中一个报死锁。这个死锁是这样产生的：

1.session1执行 delete会在唯一索引 c2的 c2= 15这一记录上加 X lock（也就是在MySQL内部观测到的：X Lock but not gap）；

2.session2和 session3在执行 insert的时候，由于唯一约束检测发生唯一冲突，会加 S Next-Key Lock，即对(1,15]这个区间加锁包括间隙，并且被 seesion1的 X Lock阻塞，进入等待；

3.session1在执行 commit后，会释放 X Lock，session2和 session3都获得 S Next-Key Lock；

4.session2和 session3继续执行插入操作，这个时候 INSERT INTENTION LOCK（插入意向锁）出现了，并且由于插入意向锁会被 gap锁阻塞，所以 session2和 session3互相等待，造成死锁。

死锁日志如下：

请点击输入图片描述

INSERT INTENTION LOCK

在之前的死锁分析第四点，如果不分析插入意向锁，也是会造成死锁的，因为插入最终还是要对记录加 X Lock的，session2和 session3还是会互相阻塞互相等待。

但是插入意向锁是客观存在的，我们可以在官方手册中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.插入意向锁其实是一种特殊的 gap lock，但是它不会阻塞其他锁。假设存在值为 4和 7的索引记录，尝试插入值 5和 6的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙，即在（4，7）上加 gap lock，但是这两个事务不会互相冲突等待。

当插入一条记录时，会去检查当前插入位置的下一条记录上是否存在锁对象，如果下一条记录上存在锁对象，就需要判断该锁对象是否锁住了 gap。如果 gap被锁住了，则插入意向锁与之冲突，进入等待状态（插入意向锁之间并不互斥）。总结一下这把锁的属性：

1.它不会阻塞其他任何锁；

2.它本身仅会被 gap lock阻塞。

在学习 MySQL过程中，一般只有在它被阻塞的时候才能观察到，所以这也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一个重要的点就是 gap lock，通常情况下我们说到 gap lock都只会联想到 REPEATABLE-READ隔离级别利用其解决幻读。但实际上在 READ-COMMITTED隔离级别，也会存在 gap lock，只发生在：唯一约束检查到有唯一冲突的时候，会加 S Next-key Lock，即对记录以及与和上一条记录之间的间隙加共享锁。

通过下面这个例子就能验证：

请点击输入图片描述

这里 session1插入数据遇到唯一冲突，虽然报错，但是对(15,20]加的 S Next-Key Lock并不会马上释放，所以 session2被阻塞。另外一种情况就是本文开始的例子，当 session2插入遇到唯一冲突但是因为被 X Lock阻塞，并不会立刻报错“Duplicate key”，但是依然要等待获取 S Next-Key Lock。

有个困惑很久的疑问：出现唯一冲突需要加 S Next-Key Lock是事实，但是加锁的意义是什么？还是说是通过 S Next-Key Lock来实现的唯一约束检查，但是这样意味着在插入没有遇到唯一冲突的时候，这个锁会立刻释放，这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。

如果是在 REPEATABLE-READ，除以上所说的唯一约束冲突外，gap lock的存在是这样的：

普通索引（非唯一索引）的S/X Lock，都带 gap属性，会锁住记录以及前1条记录到后1条记录的左闭右开区间，比如有[4,6,8]记录，delete 6，则会锁住[4,8）整个区间。

对于 gap lock，相信 DBA们的心情是一样一样的，所以我的建议是：

1.在绝大部分的业务场景下，都可以把 MySQL的隔离界别设置为 READ-COMMITTED；

2.在业务方便控制字段值唯一的情况下，尽量减少表中唯一索引的数量。

锁冲突矩阵

前面我们说的 GAP LOCK其实是锁的属性，另外我们知道 InnoDB常规锁模式有：S和 X，即共享锁和排他锁。锁模式和锁属性是可以随意组合的，组合之后的冲突矩阵如下，这对我们分析死锁很有帮助：

请点击输入图片描述

深入研究insert into select锁表故障(上)

前几天，一个mysql数据库运维同事，在生产上用insert into select* from语句，在生产上备份了一张表，结果将备份表全表锁住了，业务影响了大约10分钟。

看到这个语句，我第一反应就是select语句也能锁表，可是生产上的故障，证明确实锁表了。所以，需要将insert into select* from获取锁的情况彻底研究明白。

从innodb引擎获取的lock信息，太少了，只能看到有3 lock struct(s)，6 row lock(s)，不清楚那表申请的锁，申请什么类型的锁，不知道这些信息，就研究不明白故障到底怎么发生的。

幸运的是，mysql数据库提供一个参数innodb_status_output_locks，可以打印更详细的lock信息。

启用innodb_status_output_locks参数，默认是不开启，所以需要开启。

下面是开启innodb_status_output_locks参数之后，获取的详细lock信息

从上面的信息，可以很清晰看到，t_test_1获取到IS锁，并且有5个Record lock信息，即锁了5条记录，而此表只有5条记录，所以锁全表。

TABLE LOCK table testdb1.t_test_1 trx id 182551 lock mode IS

insert into t_test_2 select* from t_test_1 where name like'trest';这个sql语句中，t_test_1表的name字段没有索引，索引走了全表扫描，如果在name字段创建索引呢，会有什么变化呢

创建索引

重新获取innodb的lock详细信息

看到没有，在这里，现在只有一个Record lock，不再是锁全表了。

mysql insert语句注意什么

1.基础的Insert语句示例

下面的语句向员工表插入一条新记录。在这个例子中，后的“values”指定要插入到表中的所有字段对应的值。

INSERT INTO employee VALUES(100,'Thomas','Sales',5000);

用SELECT语句来验证数据是否插入成功。

SELECT* FROM employee;

2.只针对选定的字段插入值

如果你只想向选定的字段插入值，你需要在INSERT语句中明确指定要插入的字段名字。下面的代码仅插入id和name列的数据

INSERT INTO employee(id,name) VALUES(200,'Jason');

以上代码中，我们未对dept和salary列指定任何值。所以，以上两个字段的值为NULL，当我们用SELECT语句检索数据时。需要注意的是，NULL不是指"NULL"字符串，SELECT语句使用"NULL"来表示这个字段的值是空的而已。

mysql> SELECT* FROM employee;+-----+--------+-------+--------+| id| name| dept| salary|+-----+--------+-------+--------+| 100| Thomas| Sales| 5000|| 200| Jason| NULL| NULL|+-----+--------+-------+--------+2 rows in set(0.00 sec)

3. INSERT SET示例

INSERT语句不仅仅可以使用"VALUE"关键字，也可以使用"SET"关键字。下面的例子与上面的例子结果一样，但是使用的是SET关键字。

mysql> INSERT INTO employee SET id=300, name='Mayla';mysql> select* from employee;+-----+--------+-------+--------+| id| name| dept| salary|+-----+--------+-------+--------+| 100| Thomas| Sales| 5000|| 200| Jason| NULL| NULL|| 300| Mayla| NULL| NULL|+-----+--------+-------+--------+

4.从另一个表中检索数据插入

下面的例子中，我们会使用INSERT... SELECT语句，这个语句可以从其他表中检索数据，并插入到目标表。下面的代码从Contractor表中检索所有数据并插入到Employee表。

INSERT INTO employee SELECT* FROM contractor;

SELECT语句中可以根据需要使用WHERE子句，用来检索需要的从Contractor表插入到Employee表的数据。

INSERT INTO employee SELECT* FROM contractor WHERE salary>= 7000;

注意:如果你使用oracle数据库，你的SQL应该这样写"INSERT INTO employee AS SELECT* FROM contractor"。需要注意在MySQL中不适用AS关键字。

5.从其他表中选择部分字段插入

当然，你也可以从其他表中选择部分字段的数据插入到你的表中。下面的例子将从contractor表中选择id，name字段的所有数据插入到employee表。

INSERT INTO employee(id,name) SELECT id,name FROM contractor;

跟前面的例子一样，可以使用WHERE子句过滤数据。

INSERT INTO employee(id,name) SELECT id,name FROM contractor WHERE salary>= 7000;

注意:如果employee表中已存在相同主键（这里的主键是id）的记录，你会得到一条错误信息。下面是一个示例错误信息，指出employee表中已经存在id为100的记录。

ERROR 1062(23000): Duplicate entry'100' for key'PRIMARY'

6.插入数据到指定分区

如果创建了分区表（分区类型为range）并希望将输入插入到指定分区，可以参考下面代码。将数据插入到分区p1中的employee表。

INSERT INTO employee PARTITION(p1) VALUES(100,'Thomas','Sales',5000);

注意:如果该分区的employee表中已包含相应的数据（这里是id=100的employee数据），你会得到如下错误信息。

ERROR 1729(HY000): Found a row not matching the given partition set

注意:分区仅在MySQL5.6及以上版本可用。

7.插入数据到多个分区

可以使用单条SQL语句将数据插入到多个分区。下面的INSERT语句将id为100的记录插入到p1分区，将id为200的记录插入到p2分区。

INSERT INTO employee PARTITION(p1, p2) VALUES(100,'Thomas','Sales',5000),(200,'Jason','Technology',5500);

注意:在上例中，如果MySQL往任意分区插入数据失败，那么整个INSERT语句将失败。当然，这同样仅能在MySQL5.6及以上版本使用。

8.在插入过程中忽略错误

在一些场景中（比如：批处理），你可能希望忽略插入过程中MySQL产生的错误信息，你可以使用INSERT IGNORE语句。比如：下面的例子会抛出错误信息，提示数据已经存在。

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000);ERROR 1062(23000): Duplicate entry'100' for key'PRIMARY'

要忽略上面的错误信息，可以使用INSERT IGNORE来替换INSERT语句（执行前请确保这条对应的主键已经存在）。

mysql> INSERT IGNORE INTO employee VALUES(100,'Thomas','Sales',5000);Query OK, 0 rows affected(0.00 sec)

INSERT IGNORE仅仅只是忽略错误信息，不会进行其他任何处理。

9. INSERT语句的默认值

如果MySQL运行在strict模式下，并且在我们的INSERT语句中并未指定默认值，那么MySQL将抛出错误信息。不过，如果MySQL并未启用strict模式（这个是默认启用的）时，同时INSERT语句没有为字段指定值，那么MySQL会为该字段使用字段类型的默认值。比如，bonus表的所有字段都不允许为空(not null)。

mysql> DESC bonus;+--------+---------+------+-----+---------+-------+| Field| Type| Null| Key| Default| Extra|+--------+---------+------+-----+---------+-------+| id| int(11)| NO|| NULL||| amount| int(11)| NO|| NULL||+--------+---------+------+-----+---------+-------+

我们先执行以下语句，插入一条数据(id=100)

INSERT INTO bonus(id) VALUES(100);

查看刚插入的数据，可以看到amount字段被设置为默认值0

SELECT* FROM bonus;+-----+--------+| id| amount|+-----+--------+| 100| 0|+-----+--------+

如果，SQL语句中同时不指定id和amount字段的值，那么他们都会被自动设置为默认值0。参考下面例子。

INSERT INTO bonus VALUES();mysql> select*

关于本次insert语句会锁表吗和insert违反唯一约束条件的问题分享到这里就结束了，如果解决了您的问题，我们非常高兴。