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

隔离级别
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)

lock

record

null

bits

3 条回复 2024-01-17 18:57:00 +08:00

xlzyxxn

2024-01-03 18:10:12 +08:00

sql_safe_updates 设置为 1 ，不用 duplicate key update 还会出现吗

shidakang0

2024-01-03 19:51:17 +08:00

死锁通常在多个事务同时尝试以不兼容的方式锁定资源时发生。在您提供的信息中，两个事务都尝试对同一资源进行替换操作，这涉及到删除旧记录和插入新记录的过程由于 REPLACE INTO 实质上是一个删除后跟着一个插入的操作组合，两个事务都试图获取同样的锁，从而导致死锁。

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

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

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

为了避免死锁，可以考虑以下策略：

重新考虑索引策略，确保索引尽可能高效，这样可以降低锁定的需求。
尽可能减少事务大小，执行更多的小事务而不是少数几个大事务。
对于可能会导致死锁的操作，可以使用排他锁（ SELECT ... FOR UPDATE ），或者在应用逻辑中实现重试机制。
确保应用程序中的事务按照相同的顺序访问数据库对象，以减少锁冲突的可能性。

alexfarm

2024-01-17 18:57:00 +08:00

@shidakang0 大佬你好，能麻烦看下我的一个帖子吗，是关于 ON DUPLICATE KEY UPDATE 引发的死锁问题，https://cn.v2ex.com/t/1008054