隔离级别是 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!
1
codebigbang 2021-11-28 14:38:37 +08:00
看你的业务逻辑,或许可以试试 replace into
|
2
daoqiongsi1101 OP @codebigbang 可是需要先 delete 数据
|
3
iplayio2019 2021-11-28 15:08:15 +08:00
@daoqiongsi1101 replace 也是先 replace 再 insert
|
4
daoqiongsi1101 OP @iplayio2019 想删除所有 c_id = 5 的数据, 如果只是 insert replace (c_id,d_id) replace (5,1)(5,2),那么还有( 5,3) 是否就保留下来了
|
5
RipL 2021-11-28 15:27:55 +08:00 via iPhone
先查在删除?
|
6
sujin190 2021-11-28 15:37:44 +08:00
@iplayio2019 #3 replace 也需要间隙锁吧,似乎并不能解决这个问题来着
似乎你这个应该删除改成先查询出主键,然后用主键来删除,应该就没这个问题了吧,或者用外部锁来串行化也行,删除时可以先查询下看看,如果只有一条就改成更新估计可以提高一点性能吧 |
7
surfire91 2021-11-28 16:50:06 +08:00
先查出来,删的时候按 c_id + d_id 来删,或者按主键删都行
|
8
bxb100 2021-11-28 17:28:30 +08:00
有一个方案: 先查有值就删, 无值就 insert, 加个 unique(c_id, d_id) 保证不会重复插入
|
9
akira 2021-11-28 18:15:45 +08:00
生产环境业务代码中尽量不要做硬删除动作,改为软删除。 定时清理维护就好。
|
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 拆成两个事务即可。 最后,可以考虑下外部用锁进行串行化操作。 |
11
jwh199588 2021-11-29 09:43:18 +08:00
|
12
abccccabc 2021-11-29 10:44:11 +08:00
要么优化业务逻辑,要么隔离机制改为 S 。二选一。
|