未分類
14 7 月 2020

问一个关于 MySQL 锁的问题

问一个关于 MySQL 锁的问题

資深大佬 : codebigbang 5

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

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

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

大佬有話說 (27)

  • 主 資深大佬 : codebigbang

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

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

  • 資深大佬 : allenhu

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

  • 資深大佬 : iseki

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

  • 主 資深大佬 : codebigbang

    @allenhu
    这个真的香不起来

  • 資深大佬 : 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 条一批 每批的事务都要单独提交