sql server insert语句会锁表吗 insert into select会锁表吗

转载

mob6454cc762e37 2023-11-20 07:09:56

文章标签 mysql 数据库经验分享死锁 Java 文章分类 SQL Server 数据库

【辰兮要努力】：hello你好我是辰兮，很高兴你能来阅读，昵称是希望自己能不断精进，向着优秀程序员前行！

博客来源于项目以及编程中遇到的问题总结，偶尔会有读书分享，我会陆续更新Java前端、后台、数据库、项目案例等相关知识点总结，感谢你的阅读和关注，希望我的博客能帮助到更多的人，分享获取新知，大家一起进步!

吾等采石之人，应怀大教堂之心，愿我们奔赴在各自的热爱里…

一、死锁问题

最近在涉及到大数据量数据表数据的迁移时候，使用到了insert into select，遇到了部分死锁问题，遇到了一些坑，现整理分享！

sql server insert语句会锁表吗 insert into select会锁表吗_死锁

我出现死锁的原因可能是因为业务逻辑过于复杂，最上层加了事务，导致业务流程执行太慢，事务未提交，其余线程可能使用到了相同的数据表，前一个资源未释放导致死锁。

最后排查业务代码和两个东西有关，一个是事务的使用，一个是insert into select的应用

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

sql server insert语句会锁表吗 insert into select会锁表吗_Java_02

死锁对应的效果

sql server insert语句会锁表吗 insert into select会锁表吗_死锁_03

用命令行的方式杀死MySQL线程

-- 查询当前运行的所有事务
select * from information_schema.innodb_trx

-- 如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务
kill 258956

sql server insert语句会锁表吗 insert into select会锁表吗_Java_04

执行完对应命令后再次执行查询操作即可看到没有在运行的事务；

sql server insert语句会锁表吗 insert into select会锁表吗_mysql_05

在数据库中使用insert into select时，select语句加一个筛选条件，走索引，这样不会出现扫描全表的情况而锁表了。

如上发生死锁一定要去反复检查业务逻辑里面的sql，检查是否因为书写问题导致锁表等！

sql server insert语句会锁表吗 insert into select会锁表吗_经验分享_06

注意事项

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

如何检查自己的SQL语句是否使用到了索引？

使用EXPLAIN使用即可：key 显示SQL实际决定查询结果使用的键(索引)。如果没有使用索引，值为NULL

sql server insert语句会锁表吗 insert into select会锁表吗_经验分享_07

拓展问题

利用到事务就要注意到什么时候事务会失效
利用到索引就要注意到什么时候索引会失效

二、插入问题

在执行insert into select的时候对应的字段一定要一一对应，不然插入进去会报错，或者数据执行错误

CREATE TABLE `bill` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL COMMENT '用户id',
  `money` float(99,2) DEFAULT NULL COMMENT '金额',
  `typeid` int(11) NOT NULL COMMENT '类型 1 收入 2 支出',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `paywayid` int(11) DEFAULT NULL COMMENT '支付方式',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `bill_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL COMMENT '用户id',
  `money` float(99,2) DEFAULT NULL COMMENT '金额',
  `typeid` int(11) NOT NULL COMMENT '类型 1 收入 2 支出',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `paywayid` int(11) DEFAULT NULL COMMENT '支付方式',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

insert into bill_test (`id`,`title`,`userid`,`money`,`typeid`,`remark`,`paywayid`) 
select  `id`,`title`,`userid`,`money`,`typeid`,`remark`,`paywayid`  from   bill

sql server insert语句会锁表吗 insert into select会锁表吗_经验分享_08