问一个关于 MySQL 锁的问题

2020-07-19 08:30:32 +08:00
 codebigbang

一张表每天月有一千万多的数据量，第二天重新写之前需要把表里存着的数据量给全删掉再把今天需要写的数据按一定尺寸分批利用多线程写入数据库。

在清表这个阶段，一开始先用 delete from tableName 来实现清空同时返回清空的数据量的。但是走完清表步骤获得删除数据量开始分批往数据库写数据的阶段，写了几个数据之后，数据库就报死锁错误。结果只能使用 truncate 来删数据，才能确保之后写数据阶段没有锁的问题。

请教有相关经验的 V 友：在网上搜了很多资料都是说删数据的时候会加锁，这样在往表里插数据的时候会导致死锁。可是我的程序是在获取了已经删除的数据量之后才往表重新插数据，难道说这个时候其实 delete 产生的锁还没有解锁吗？如果还没有解锁，那是否有办法在 Java 端获取 MySQL 删除后真正解锁状态的办法？

3936 次点击
所在节点    程序员
27 条回复
codebigbang
2020-07-19 08:45:36 +08:00
都超过 10 分钟了，帖子不要沉啊，，，

是 V 友都忙了一晚上所以早上都在睡觉的原因吗？
allenhu
2020-07-19 08:51:59 +08:00
你新建一个表，然后 rename 不香吗？
iseki
2020-07-19 08:53:26 +08:00
总感觉 MySQL 不该有这么低级的 bug 啊
codebigbang
2020-07-19 08:59:30 +08:00
@allenhu
这个真的香不起来😂，因为权限控制的比较严，基本上创建表、rename 等高权限都需要层层审批，所以希望尽量用基本操作解决😅。
lambchasr
2020-07-19 09:07:08 +08:00
为啥不先 count，再 truncate，你都说了全表了...
wangsla
2020-07-19 09:30:34 +08:00
@codebigbang 你写了多线程操作，应该是事务使用的方式不对吧，可能是每个线程都开启了新事务？往这个方向考虑下。
wangritian
2020-07-19 09:35:49 +08:00
delete 后确保 commit，最好重新创建一个连接，然后再写入试试
如果没有 commit，锁会一直存在，当新数据主键或索引项与被删数据相同时，可能会造成死锁
brader
2020-07-19 11:17:28 +08:00
你要不尝试下，加一个删除条件？比如，先查询最大的 ID 是多少，然后加 WHERE ID<=?，
这样看下，MYSQL 会不会只锁 ID<=?部分的数据，这样就不影响你插入了。
xyjincan
2020-07-19 11:23:17 +08:00
批量写入没必要多线程，新建新表好点啊，truncate 多块
JasperWong
2020-07-19 12:08:11 +08:00
应该是间隙锁+事务合并导致的死锁问题,避免这样用就好了
banxi1988
2020-07-19 12:20:52 +08:00
1. 创建新表,操作完成之后重命名. 然后再删除旧表.
2. 使用分区, 旧的分区不用之后,可以快速删除.
codebigbang
2020-07-19 14:49:08 +08:00
@brader
这样尝试过，但因为两天数据其实大部分 ID 是有重复的，所以就还是会产生死锁问题。

@wangritian
已经写了 commit，但是还是会存在这个问题

@lambchasr
现在的解决办法就是 count 全表+truncate 全表完成 delete 产生的结果，但是 truncate 是高权限，不是每个申请都会通过，所以尝试只用 delete 看能不能解决问题
codebigbang
2020-07-19 14:59:37 +08:00
@JasperWong
只能避免先 delete 全表，然后再多次提交 insert 吗？
毕竟这种业务场景肯定是有不少存在情形的，有没有能跳过这种困境的方法呢？
wangsla
2020-07-19 17:55:16 +08:00
死锁还是因为多个事务的问题，如果单个事务肯定不会出现锁的情况。开了多线程，就要考虑线程间事务的影响，可以参考 spring 事务传播。最简单的场景，单线程进行 delete+insert 操作，考虑性能的话，就考虑 batch 操作。
codebigbang
2020-07-19 21:17:17 +08:00
@wangsla
可是我放弃 delete 用 truncate，再使用多个事务的 insert 就不会出现这个问题。
这两者对于后续 insert 的事务有什么影响吗？可以分享你的想法吗？😁
wangsla
2020-07-19 21:40:58 +08:00
@codebigbang truncate 是 ddl，会默认提交事务的，也就是说 truncate 之后，当前线程的事务就结束。delete，需要你显式结束事务。delete 锁表的话，有可能是因为其他同学提的，delete 删除时候没有条件，产生了 gap 锁或者更差的锁了整表。
ansi395958
2020-07-19 22:19:57 +08:00
delete 和 insert 在可重复读级别下都会产生 gap 锁，假设 session a 在执行 insert 对 id(1,1000)加了 gap 锁，session b 在执行 delete 也对 id(1,1000)加了 gap 锁。gap 锁之间不会互相阻塞，但是他们会互相阻塞对方的写操作，那就有可能产生 a 等待 b 释放锁，b 等待 a 释放锁，产生死锁。但是 truncate 会加表锁，表锁会阻塞写意向锁，所以 insert 会被阻塞，不会产生死锁。
还有就是 delete 并不一定可以释放表空间，表会越来越大的，truncate 会释放空间。
fangcan
2020-07-19 22:21:18 +08:00
马克下， 理论上没有多个事务间的争夺资源是不会产生死锁的， 楼主有答案的话 @我下
qbmiller
2020-07-19 22:41:20 +08:00
修改 mysql procedure ; 加个每日新建表操作;
这种原来表操作, 怎么做都恶心; 尤其你上边说 还有 id 重复...
npe
2020-07-20 00:04:07 +08:00
delete 的时候带上索引列，不然锁表了……

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://tanronggui.xyz/t/691260

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX