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
cnoder
V2EX  ›  MySQL

这为什么会死锁呢,为什么换 duplicate key update 就不报错了,有沒有 dba 大哥说说

  •  
  •   cnoder · 302 天前 · 1555 次点击
    这是一个创建于 302 天前的主题,其中的信息可能已经有所发展或是发生改变。
    隔离级别
    REPEATABLE-READ

    并发也不高,qps 估计 10 以内

    CREATE TABLE `tb` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `convert_key` varchar(200) NOT NULL COMMENT 'a',
    `convert_status` tinyint(8) NOT NULL COMMENT 'a',
    `error_msg` varchar(256) NOT NULL,
    `convert_type` varchar(128) NOT NULL,
    `sid` varchar(128) NOT NULL COMMENT 'a',
    `create_time` bigint(20) NOT NULL,
    `modify_time` bigint(20) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_convert_key` (`convert_key`) USING BTREE,
    KEY `idx_create_time` (`create_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=19785 DEFAULT CHARSET=utf8;



    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    *** (1) TRANSACTION:
    TRANSACTION 107851589284, ACTIVE 0 sec updating or deleting
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
    MySQL thread id 2012848, OS thread handle 70370117332960, query id 28214859058 ww update
    replace into tb set modify_time=1704268105,convert_status=1,convert_key='4dfbecb0ba209a',convert_type='pdf2excel',create_time=1704268105,sid='645cbb3582'
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1186 page no 543732 n bits 0 index PRIMARY of table `tb` trx id 107851589284 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    0: len=8; bufptr=0x4003257e8070; hex= 73757072656d756d; asc supremum;;

    *** (2) TRANSACTION:
    TRANSACTION 107851589283, ACTIVE 0 sec updating or deleting
    mysql tables in use 1, locked 1
    LOCK WAIT 6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
    MySQL thread id 2013484, OS thread handle 70369103069152, query id 28214859053 ww update
    replace into tb set modify_time=1704268105,convert_status=1,convert_key='5e846da1374180679',convert_type='pdf2ppt',create_time=1704268105,sid='ce2b6ea5671'
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 1186 page no 543732 n bits 0 index PRIMARY of table `tb` trx id 107851589283 lock_mode X
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    0: len=8; bufptr=0x4003257e8070; hex= 73757072656d756d; asc supremum;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1186 page no 543732 n bits 0 index PRIMARY of table `tb` trx id 107851589283 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    0: len=8; bufptr=0x4003257e8070; hex= 73757072656d756d; asc supremum;;

    *** WE ROLL BACK TRANSACTION (1)
    3 条回复    2024-01-17 18:57:00 +08:00
    xlzyxxn
        1
    xlzyxxn  
       302 天前
    sql_safe_updates 设置为 1 ,不用 duplicate key update 还会出现吗
    shidakang0
        2
    shidakang0  
       302 天前
    死锁通常在多个事务同时尝试以不兼容的方式锁定资源时发生。在您提供的信息中,两个事务都尝试对同一资源进行替换操作,这涉及到删除旧记录和插入新记录的过程。由于 REPLACE INTO 实质上是一个删除后跟着一个插入的操作组合,两个事务都试图获取同样的锁,从而导致死锁。

    在 REPEATABLE-READ 隔离级别下,InnoDB 存储引擎为表中的索引记录保持行锁,直到事务结束。这意味着一旦事务获得了某个记录的锁,它将在事务结束前保持该锁定状态。如果另一个事务尝试执行与已锁定的记录相关的操作,则该事务将进入等待状态,直到第一个事务完成。如果两个事务互相等待对方释放锁,就会发生死锁。

    在您的案例中,两个事务都在尝试执行 REPLACE INTO 操作,并且因为涉及到删除操作,它们都需要 X (排他锁)。第一个事务等待释放因为第二个事务持有的旧记录的锁,而第二个事务等待释放第一个事务尝试插入的新记录相同位置的锁。这就是为什么发生死锁的原因。

    当改用 INSERT ... ON DUPLICATE KEY UPDATE 时,如果尝试插入的记录的唯一键已经存在,则不会删除旧记录,而是更新它。这意味着只需要一次索引记录的锁,减少了锁的复杂性和冲突的可能性,因此不再出现死锁错误。

    为了避免死锁,可以考虑以下策略:

    重新考虑索引策略,确保索引尽可能高效,这样可以降低锁定的需求。
    尽可能减少事务大小,执行更多的小事务而不是少数几个大事务。
    对于可能会导致死锁的操作,可以使用排他锁( SELECT ... FOR UPDATE ),或者在应用逻辑中实现重试机制。
    确保应用程序中的事务按照相同的顺序访问数据库对象,以减少锁冲突的可能性。
    alexfarm
        3
    alexfarm  
       288 天前
    @shidakang0 大佬你好,能麻烦看下我的一个帖子吗,是关于 ON DUPLICATE KEY UPDATE 引发的死锁问题,https://cn.v2ex.com/t/1008054
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1100 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 22:50 · PVG 06:50 · LAX 15:50 · JFK 18:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.