V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
daoqiongsi1101
V2EX  ›  MySQL

请教一个 MySQL 死锁问题,先 delete 再批量 insert 引发的死锁

  •  1
     
  •   daoqiongsi1101 · 2021-11-28 14:18:21 +08:00 · 3471 次点击
    这是一个创建于 852 天前的主题,其中的信息可能已经有所发展或是发生改变。

    隔离级别是 RR ,这里 c_id 是一个普通索引,(c_id, d_id) 是一个唯一索引。

    transaction1 transaction2
    BEGIN;
    delete from demo where c_id = 'abc';
    Query OK, 0 rows affected (0.00 sec)
    BEGIN;
    delete from demo where c_id = 'xyz';
    Query OK, 0 rows affected (0.00 sec)
    insert into demo (c_id, d_id) values ('abc', '111'), ('abc', '222'), ('abc', '333');
    WAITING
    insert into demo (c_id, d_id) values ('xyz', '444'), ('xyz', '555'), ('xyz', '666');
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

    最终事务 1 被回滚了。

    死锁的日志关键字:

    (1) WAITING FOR THIS LOCK TO BE GRANTED
    lock_mode X locks gap before rec insert intention waiting
    (2) TRANSACTION:
    TRANSACTION 947943174, ACTIVE 0 sec updating or deleting
    mysql tables in use 1, locked 1
    (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 899935 page no 578533 n bits 200 index c_id_index of table `demo` trx id 947943174 lock_mode X
    (2) WAITING FOR THIS LOCK TO BE GRANTED:
    lock_mode X locks rec but not gap waiting
    

    原因基本很明确:应该是两个 delete 同时获取了 gap 或 next_key 锁,然后因为不存在记录,导致锁的范围很大,甚至是一个 ∞ 的区间,同时 insert 语句无法获取插入意向锁,并互相等待引发死锁。(这样理解对吗)

    那么解决方案是什么?

    不能改变隔离级别。

    是否先查一下要删除的条件有没有记录,如果有,再执行删除。这样是否就可以了。

    Thank you!

    12 条回复    2021-11-29 10:44:11 +08:00
    codebigbang
        1
    codebigbang  
       2021-11-28 14:38:37 +08:00
    看你的业务逻辑,或许可以试试 replace into
    daoqiongsi1101
        2
    daoqiongsi1101  
    OP
       2021-11-28 14:41:15 +08:00
    @codebigbang 可是需要先 delete 数据
    iplayio2019
        3
    iplayio2019  
       2021-11-28 15:08:15 +08:00
    @daoqiongsi1101 replace 也是先 replace 再 insert
    daoqiongsi1101
        4
    daoqiongsi1101  
    OP
       2021-11-28 15:20:46 +08:00
    @iplayio2019 想删除所有 c_id = 5 的数据, 如果只是 insert replace (c_id,d_id) replace (5,1)(5,2),那么还有( 5,3) 是否就保留下来了
    RipL
        5
    RipL  
       2021-11-28 15:27:55 +08:00 via iPhone
    先查在删除?
    sujin190
        6
    sujin190  
       2021-11-28 15:37:44 +08:00
    @iplayio2019 #3 replace 也需要间隙锁吧,似乎并不能解决这个问题来着

    似乎你这个应该删除改成先查询出主键,然后用主键来删除,应该就没这个问题了吧,或者用外部锁来串行化也行,删除时可以先查询下看看,如果只有一条就改成更新估计可以提高一点性能吧
    surfire91
        7
    surfire91  
       2021-11-28 16:50:06 +08:00
    先查出来,删的时候按 c_id + d_id 来删,或者按主键删都行
    bxb100
        8
    bxb100  
       2021-11-28 17:28:30 +08:00
    有一个方案: 先查有值就删, 无值就 insert, 加个 unique(c_id, d_id) 保证不会重复插入
    akira
        9
    akira  
       2021-11-28 18:15:45 +08:00
    生产环境业务代码中尽量不要做硬删除动作,改为软删除。 定时清理维护就好。
    Seayon
        10
    Seayon  
       2021-11-28 19:26:48 +08:00
    假如你本次要删除 c_id='abc' 的记录,你的本次操作中的 DELETE 和 INSERT 中间是否允许别的事务插入新的 c_id ='abc' 的新记录?
    如果不允许,那改成先查是否有然后用主键删除的话,其中 SELECT 要写成 SELECT * FROM demo WHERE c_id='abc' FOR UPATE ,用来锁定避免别的事务插入新的记录,此所谓幻读问题。当然这样也会加上 GAP 锁进而又产生死锁。
    如果允许,那我觉得吧 第一步的 DELETE 和第二步的 INSERT 拆成两个事务即可。
    最后,可以考虑下外部用锁进行串行化操作。
    jwh199588
        11
    jwh199588  
       2021-11-29 09:43:18 +08:00
    abccccabc
        12
    abccccabc  
       2021-11-29 10:44:11 +08:00
    要么优化业务逻辑,要么隔离机制改为 S 。二选一。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2777 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 12:50 · PVG 20:50 · LAX 05:50 · JFK 08:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.