问一个关于 MySQL 锁的问题

[codebigbang]

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

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

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

[codebigbang]

都超过 10 分钟了，帖子不要沉啊，，，

是 V 友都忙了一晚上所以早上都在睡觉的原因吗？

[allenhu]

你新建一个表，然后 rename 不香吗？

[iseki]

总感觉 MySQL 不该有这么低级的 bug 啊

[codebigbang]

@allenhu
这个真的香不起来😂，因为权限控制的比较严，基本上创建表、rename 等高权限都需要层层审批，所以希望尽量用基本操作解决😅。

[lambchasr]

为啥不先 count，再 truncate，你都说了全表了...

[wangsla]

@codebigbang 你写了多线程操作，应该是事务使用的方式不对吧，可能是每个线程都开启了新事务？往这个方向考虑下。

[wangritian]

delete 后确保 commit，最好重新创建一个连接，然后再写入试试
如果没有 commit，锁会一直存在，当新数据主键或索引项与被删数据相同时，可能会造成死锁

[brader]

你要不尝试下，加一个删除条件？比如，先查询最大的 ID 是多少，然后加 WHERE ID<=?，
这样看下，MYSQL 会不会只锁 ID<=?部分的数据，这样就不影响你插入了。

[xyjincan]

批量写入没必要多线程，新建新表好点啊，truncate 多块

[JasperWong]

应该是间隙锁+事务合并导致的死锁问题,避免这样用就好了

[banxi1988]

1. 创建新表,操作完成之后重命名. 然后再删除旧表.
2. 使用分区, 旧的分区不用之后,可以快速删除.

[codebigbang]

@brader
这样尝试过，但因为两天数据其实大部分 ID 是有重复的，所以就还是会产生死锁问题。

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

@lambchasr
现在的解决办法就是 count 全表+truncate 全表完成 delete 产生的结果，但是 truncate 是高权限，不是每个申请都会通过，所以尝试只用 delete 看能不能解决问题

[codebigbang]

@JasperWong
只能避免先 delete 全表，然后再多次提交 insert 吗？
毕竟这种业务场景肯定是有不少存在情形的，有没有能跳过这种困境的方法呢？

[wangsla]

死锁还是因为多个事务的问题，如果单个事务肯定不会出现锁的情况。开了多线程，就要考虑线程间事务的影响，可以参考 spring 事务传播。最简单的场景，单线程进行 delete+insert 操作，考虑性能的话，就考虑 batch 操作。

[codebigbang]

@wangsla
可是我放弃 delete 用 truncate，再使用多个事务的 insert 就不会出现这个问题。
这两者对于后续 insert 的事务有什么影响吗？可以分享你的想法吗？😁

[wangsla]

@codebigbang truncate 是 ddl，会默认提交事务的，也就是说 truncate 之后，当前线程的事务就结束。delete，需要你显式结束事务。delete 锁表的话，有可能是因为其他同学提的，delete 删除时候没有条件，产生了 gap 锁或者更差的锁了整表。

[ansi395958]

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]

马克下， 理论上没有多个事务间的争夺资源是不会产生死锁的， 楼主有答案的话 @我下

[qbmiller]

修改 mysql procedure ; 加个每日新建表操作;
这种原来表操作, 怎么做都恶心; 尤其你上边说 还有 id 重复...

[npe]

delete 的时候带上索引列，不然锁表了……

[johnj]

显然应该先插入临时表 再通过 rename 来替换 rename 可以做到原子操作（旧表改其他名字 临时表改为旧表名 最后把其他名字表 drop ）

[awanganddong]

想判断死锁的问题，把 mysql log 慢日志贴出来让大家分析。
rename 是一个比较好的方案

[sha4yu]

可以贴下死锁信息和执行的相关 SQL 不

[hejw19970413]

如果是那么大数据量，为什么不每天都创建一个新表，创建完后 删除以前的表不就可以吗

[codebigbang]

@awanganddong
@sha4yu
这是前期开发时候遇到的问题，当时就用 truncate 解决掉这个问题了。项目都上线很久了，问题日志啥哪里还能拿到呢。但是后面项目复盘的时候就留意了这点，想向 V 友取取经。

SQL 大概就是先 delete from tableName, 然后多线程利用 jdbc 连接池建立连接，把一定尺寸的数据用 insert 写入，执行则是 PreparedStatement.executeLargeUpdate()这个方法，最后 commit 。

@hejw19970413
@johnj
应用和运维都不能随心所欲随意删表建表

[codebigbang]

@npe
带上索引列可以让 MySQL 删数据的时候不加锁吗？

[johnj]

事务怎么控制的？
因为数据很多 所以不要搞大事务 delete 和 insert 都要分批 比如 每 50 条一批 每批的事务都要单独提交