mysql insert引起的表锁问题

最新推荐文章于 2021-06-10 10:24:00 发布

华海的bk

最新推荐文章于 2021-06-10 10:24:00 发布

阅读量9.9k

点赞数

分类专栏： mysql的故事

本文链接：https://blog.csdn.net/qq_40204976/article/details/103238426

版权

1 篇文章 0 订阅

订阅专栏

问题： 同事遇到对一个问题,由于线上需要刷新轨迹点常停留点的数据需求，具体常停留点定义的逻辑判断在此不叙述了，回到正题，这里通过spark离线将日志数据计算进hive,再按天为一个任务，执行多任务将hive数据刷入数据库。多个任务执行的时候，可能某个任务出现了问题停止了,这时删除这个任务一天的数据，然而其他任务还在跑插入.mysql会抛出:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。
测试： 先将测试表的建表语句贴出:
CREATE TABLE test_yuhai ( id int(20) NOT NULL AUTO_INCREMENT, imei varchar(32) NOT NULL, alarm_tag int(11) NOT NULL DEFAULT '0', test_fied date DEFAULT NULL, PRIMARY KEY (id,imei), KEY ix_imei_alarm_tag (imei,alarm_tag) USING BTREE),作者数据库事务隔离级别是读已提交的。查看事务:select@@tx_isolation;如图:

首先将自动提交事务关闭。查看是否开启自动提交事务:SHOW VARIABLES LIKE ‘autocommit’;默认是ON开启的。
如果是OFF可以通过set autocommit=0关闭自动提交。假设一个A任务失败了,B任务还在插入，与此同时开启了C任务去删除A刷进来的那部分数据。
B任务在跑，事务未提交:begin;INSERT INTO test_yuhai(imei, alarm_tag, test_fied) VALUES ('11559', 0, '2019-10-18');
C任务删除任务同时跑

查询锁表情况:show OPEN TABLES where In_use > 0;
结论：由上图可以看到如果一个事务插入并且尚未提交，另外一个事务在删除的话未命中索引会出现表锁的情况。等待超时也就会抛出这个锁等待超时的错。我还测试了insert对update、select、insert并不会有产生锁表情况（文中insert与update、select、insert类似关系都属于前后事务）
延伸：如果insert会对delete有锁表的话,那么update会对其他操作有锁表的影响吗?各位看官可以自行实践，而且实践可以加深印象。这里作者还是根据本地测试直接给出结论:update对delete、update都是只会产生行锁.
delete对update、delete产生表锁。
总结:两个先后事务A、B.

(特别说明如果B后事务操作是delete则A前事务、B事务必须都要命中索引才会是行锁,否则都将产生表锁)

关注