mysql - 使用 SELECT/LIMIT 1/FOR UPDATE 时避免死锁的最佳方法是什么？

Question

我有以下两个事务导致死锁:

交易 1:

SELECT *
FROM bots b
WHERE b.status = :available
AND (b.last_checked_at IS NULL OR b.last_checked_at < :fiveMinsAgo)
ORDER BY b.last_checked_at ASC
LIMIT 1
FOR UPDATE

*AND* (I don't think that this statement is part of the issue)

UPDATE bots b
SET b.last_checked_at = :now
WHERE b.id = :botId

交易 2:

SELECT b.*, bu.*
FROM bots b
LEFT JOIN bot_users bu ON bu.id = b.assigned_to_bot_user_id
WHERE b.currency = :currency
AND (b.status = :available OR (b.status = :reserved AND bu.last_action_at < :threeMinsAgo))
AND ((b.slot_count - b.items_count) >= :amount)
ORDER BY b.keys_count ASC, b.id ASC
LIMIT 1
FOR UPDATE

*OR*

SELECT b.*, bu.*
FROM bots b
LEFT JOIN bot_users bu ON bu.assigned_bot_id = b.id
WHERE b.currency = :currency
AND (b.status = :available OR (b.status = :reserved AND bu.last_action_at < :threeMinsAgo))
AND (b.keys_count >= :amount)
ORDER BY b.keys_count DESC, b.id ASC
LIMIT 1
FOR UPDATE

*AND*

UPDATE bots SET status = :reserved WHERE id = :id

说明:

事务 #1 正在选择 1 个可用且在过去 5 分钟内未检查过的机器人，对其进行检查，然后更新 last_checked_at 字段。

交易#2(案例 1)选择 1 个机器人，该机器人处理某种货币，状态为可用或保留且“已过期”(3 分钟内没有用户操作)，并且有足够的可用插槽。

交易#2(案例 2)选择 1 个机器人，该机器人处理某种货币，状态为可用或保留且“已过期”(3 分钟内没有用户操作)，并且拥有足够的 key 来满足请求的金额。

注释:

b.keys_count、b.currency、b.status都有索引。

死锁示例:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-07-04 20:33:11 7fc46f270700
*** (1) TRANSACTION:
TRANSACTION 1657246652, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 36 row lock(s)
MySQL thread id 30422870, OS thread handle 0x7fc46f437700, query id 1217244940 10.0.88.8 mydb Creating sort index
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4752665 page no 3 n bits 176 index `PRIMARY` of table `mydb`.`bots` trx id 1657246652 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1657246644, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
10 lock struct(s), heap size 2936, 37 row lock(s), undo log entries 1
MySQL thread id 30422999, OS thread handle 0x7fc46f270700, query id 1217244943 10.0.88.8 mydb updating
UPDATE bots SET status = 1 WHERE id = 19
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4752665 page no 3 n bits 176 index `PRIMARY` of table `mydb`.`bots` trx id 1657246644 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4752665 page no 6 n bits 168 index `status_idx` of table `mydb`.`bots` trx id 1657246644 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

看起来查询锁定了大部分行，而不仅仅是某一行。

防止这些死锁的最佳方法是什么？最好不要在事务 #1 中使用 FOR UPDATE，然后尝试使用还包括其他条件的基于 ID 的选择来获取锁？例如:

SELECT *
FROM bots b
WHERE b.status = :available
AND (b.last_checked_at IS NULL OR b.last_checked_at < :fiveMinsAgo)
ORDER BY b.last_checked_at ASC
LIMIT 1

SELECT *
FROM bots b
WHERE b.id = :idSelectedAbove
AND b.status = :available
AND (b.last_checked_at IS NULL OR b.last_checked_at < :fiveMinsAgo)
FOR UPDATE

这显然会在一定比例的时间内失败，具体取决于各种因素。

使用 ORDER BY, FOR UPDATE 时有没有办法只锁定一行？

Answer 1

考虑使用 GET_LOCK() 模拟记录锁并废弃 FOR UPDATES；

选择一个特定于您想要锁定的行的名称。例如“bot_id_1”。

调用SELECT GET_LOCK('bot_id_1',30)来锁定名称'bot_id_1'..如果该名称可用，它将返回1并设置锁定，如果30秒后锁不可用则返回0(第二个参数是超时时间)。

完成后使用 SELECT RELEASE_LOCK('bot_id_1')。

注意；您必须在每个事务中使用相同的名称，并且在事务中再次调用 GET_LOCK() 将释放之前设置的锁定。

GET_LOCK() docs