mysql - 使用 SELECT...FOR UPDATE，然后更新多行

Question

我正在编写一个应用程序:

• 从订阅者表中选择一个小记录集(15 万条记录)；
• 更新这些行以表明电子邮件正在发送中；
• 向记录集中的订阅者发送电子邮件；
• 再次更新行以表明电子邮件已发送。

问题在于该表同时被多个客户端访问以分配电子邮件工作负载，这就是为什么使用中间更新(表示正在进行中)的原因——以防止不同的客户端选择相同的内容行，这会导致多封电子邮件被发送给同一订阅者。我应用了一些随机化逻辑来降低两个客户端使用相同数据的可能性，但这种情况仍然偶尔会发生。

所以现在我正在考虑使用 SELECT ... FOR UPDATE 来锁定相关行(这样另一个客户端就不会选择它们)。我的问题:是根据 SELECT...FOR UPDATE 语句的 ID 编写 UPDATE 语句更好，还是创建一个循环来单独更新每一行更好？

这是我到目前为止所得到的:

DELIMITER $$

CREATE DEFINER=`mydef`@`%` PROCEDURE `sp_SubscribersToSend`(v_limit INTEGER)
BEGIN

START TRANSACTION;

SELECT _ID, email, date_entered, DATE_FORMAT(date_entered, '%b %e, %Y') AS 'date_entered_formatted'
FROM _subscribers
WHERE send_state = 'Send'
AND status = 'Confirmed'
LIMIT v_limit
FOR UPDATE;

[[UPDATE _subscribers SET send_state = 'Sending' WHERE _ID IN (...?)]]

[[OR]]

[[Loop through the resultset and update each row?]]
COMMIT;

END

看起来单个 UPDATE 会更高效；将结果集的 _ID 列转换为 IN() 子句的逗号分隔列表的最佳方法是什么？ (在此之前我一直在客户端做这个)——或者有更好的方法吗？

Answer 1

不必尝试创建逗号分隔的列表，只需使用与 SELECT 相同的条件执行 UPDATE

START TRANSACTION;

UPDATE _subscribers
SET send_state = 'Sending'
WHERE send_state = 'Send'
AND status = 'Confirmed'
ORDER BY <something>
LIMIT v_limit;

SELECT _ID, email, date_entered, DATE_FORMAT(date_entered, '%b %e, %Y') AS 'date_entered_formatted'
FROM _subscribers
WHERE send_state = 'Send'
AND status = 'Confirmed'
ORDER BY <something>
LIMIT v_limit;

COMMIT;

ORDER BY 子句对于确保两个查询处理相同的行是必要的；如果您使用 LIMIT 而不使用 ORDER BY，他们可能会选择不同的行子集。