mysql - 有没有办法避免由于同一张表中的共享锁提升而导致死锁？

Question

我有一个 InnoDB 表“测试”，并且有两个事务查询该表。

第 1 节：

start transaction;
select * from test where testid = 200 lock in share mode;

第 2 节：

start transaction;
select * from test where testid = 200 lock in share mode;

第 1 节：

select * from test where testid = 200 for update;
-- Trying to get X lock and waits as session 2 already has a shared lock for this entry

第 2 节：

select * from test where testid = 200 for update;
--Deadlock occurs because of Shared lock of session 1 

有没有办法摆脱这种情况？

标签： mysqldatabase-deadlocks