insert into select from 锁表,MYSQL insert into select 锁表问题

insert into select from 锁表,MYSQL insert into select 锁表问题详细介绍

本文目录一览： 深入研究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 into select 锁表问题

假如你的b表的量很大的话肯定会锁表啦，因为你频繁地去insert和select操作这张表，建议你不要这样操作。可以先将数据查出来，之后再insert
例如：
$ff=mysql_query('select * from b');
$datalist=mysql_fetch_array($ff);
foreach($datalist as $k=>$v){
insert操作...
}
加锁情况与死锁原因分析
为方便大家复现，完整表结构和数据如下：
CREATE TABLE `t3` (`c1` int(11) NOT NULL AUTO_INCREMENT,`c2` int(11) DEFAULT NULL,PRIMARY KEY (`c1`),UNIQUE KEY `c2` (`c2`)) ENGINE=InnoDBinsert 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，即共享锁和排他锁。锁模式和锁属性是可以随意组合的，组合之后的冲突矩阵如下，这对我们分析死锁很有帮助：
请点击输入图片描述

createtableasselectfrom锁表吗

createtableasselect*from锁表参考以下方法
create table b1 as select * from b建表锁表测试
A: create table a1 like a; insert into a1 as select * from a;
B: create table b1 as select * from b;
测试AB两种建表语句对原始表的影响。其中a、b的数据量均为300000 rows。
如果使用A种方式建表，insert过程可能会堵塞DML操作
如果使用B中方式建表，此建表过程全程锁表；
建议：生产环境使用A种方式建备份表；

oracle insert select会锁表吗

你好，会锁表的，只要是dml语句，都会锁表
dml: insert into update delete
希望你满意。满意采纳。谢谢

同时执行select from table for update;和insert into table values ...的问题

执行时你是手工同时执行的，但数据库系统是按每个语句单独执行的，实际就是先执行select ,在执行select时，insert还没有执行，所以select执行的结果是insert以前的数据
对同一个表的操作只能是一个先执行，另一个后执行。
这样说，在你做select 操作时，系统给这个表加了一把锁，就不允许其它操作。只有当select 结束，这个锁才能打开，再进行下一个操作。
数据打印到屏幕上，但每次打印都间隔5秒-----这只是个显示时间问题，不代表这个select 需要5秒才完成。

Oracle中。insert到一张表数据，但是不提交，这个时候select，会不会锁表，如果在并发量是500，会锁表吗？

不提交数据的情况下，查询是不会锁表的，它们是并相操作，彼此不发生关系；
不会的，如果不是在一个事务中读取出来的数据是Insert之前的。如果在一个事务中，则是Insert之后的情况

oracle10G delete、insert和select同时进行会不会锁表？请高手指点！

select不会锁表 其他的dml加的是行级锁 数据不冲突是不会有问题的
http://www.itpub.net/thread-1100287-1-1.html 希望对你有帮助
delete会锁表。
select for update也会锁表。
只是一般select不加for update,一边delete一边insert不会出问题，但是会让客户诧异。
因为oracle是read commit级别，保证读一致性，但是当delete和insert的commit之后，查询的人重新查询会发现数据变化了，会有些诧异。

求教 INSERT INTO SELECT FROM 这语句怎么用具体的实例 谢谢了

select into from 和 insert into select都是用来复制表，两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建。insert into select from 要求目标表存在。
备份表数据： create table emp as select * from scott.emp
还原表数据：insert into emp select * from scott.emp
复制表结构及其数据：
create table table_name_new as select * from table_name_old
只复制表结构：
create table table_name_new as select * from table_name_old where 1=2;
或者：
create table table_name_new like table_name_old
只复制表数据：
如果两个表结构一样：
insert into table_name_new select * from table_name_old
如果两个表结构不一样：
insert into table_name_new(column1,column2...) select column1,column2...
from table_name_old pasting
如果两表字段相同，则可以直接这样用。
insert into table_a select * from table_b
如果两表字段不同，a表需要b中的某几个字段即可，则可以如下使用：
insert into table_a(field_a1,field_a2,field_a3) select field_b1,field_b2,field_b3) from table_b
以上语句前提条件是每个字段对应的字段类型相同或可以自动转换。

sql insert into select from 是什么语句

select * into 新表 from 旧表 --是将'旧表'中的数据插入到'新表'中（'新表'必须开始并没有，'新表'是这条语句执行后才产生的） insert into 表 select * from 旧表--将'旧表'的数据插入到'表'中（'表'示开始就存在的，此时插入的数据，按字段添加到'表'中，注意 '表' 和 '旧表'的字段）

SQL语句 INSERT……INTO…… SELECT 插入的顺序问题

INSERT INTO USERS([uName],uPwd)
SELECT '张三','123456' UNION all
SELECT '李四','123456' UNION all
SELECT '王五','123456' UNION all
SELECT '谢六','123456'
可以的。
例如：insert
into
a
select
*
from
b;
注意：这里要求a和b的表结构是一样的。如果不一样，则需要使用：
insert
into
a(c1,c2,...)
select
c1,c2,...
from
b；
这里c1、c2分别指a表与b表字段大小和类型都相同的列。