在多个线程中从一个表选择...以进行更新

Question

我需要一点关于 SELECT FOR UPDATE (或者 LOCK IN SHARE MODE) 的帮助。

我有一个表格，大约有 400,000 条记录，我需要对每一行运行两个不同的处理函数。

表格结构应该是这样的:

data (
    `id`,
    `mtime`,  -- When was data1 set last
    `data1`,
    `data2` DEFAULT NULL,
    `priority1`,
    `priority2`,
    PRIMARY KEY `id`,
    INDEX (`mtime`),
    FOREIGN KEY ON `data2`
)

功能有所不同：

• 第一个函数 - 必须在所有记录上循环运行（非常快），应根据priority1选择记录；设置data1和mtime
• 第二个函数 - 只需在每个记录上运行一次（非常慢），应根据priority2选择记录；设置data1和mtime

它们不应同时修改同一行，但是选择可能会在两者中返回一行（priority1和priority2具有不同的值），如果情况如此，则事务等待是可以接受的（我希望这是唯一需要阻塞的情况）。

我根据以下查询选择数据：

-- For the first function - not processed first, then the oldest,
-- the same age goes based on priority
SELECT id FROM data ORDER BY mtime IS NULL DESC, mtime, priority1 LIMIT 250 FOR UPDATE;

-- For the second function - only processed not processed order by priority
SELECT if FROM data ORDER BY priority2 WHERE data2 IS NULL LIMIT 50 FOR UPDATE;

但我经历的情况是每次只有一个查询返回结果。

所以我的问题是：

1.在两个不同的事务中，是否可能在不同的行组上获取两个独立的锁（在同一张表中）？

2.我是不是在第一个和第二个查询之间有很多冲突（我有困难调试这个问题，任何关于如何调试“SELECT ... FROM (SELECT ...) WHERE ... IN (SELECT)”的提示都将不胜感激）？

3.ORDER BY ... LIMIT ...会引起任何问题吗？

4.索引和键会引起任何问题吗？

- Vyktor

---

表类型是什么？MyISAM 还是 InnoDB？据我所知，只有 InnoDB 支持行级锁定。而 MyISAM 只支持表级锁定。 - Mateus Schneiders

我明白了...你的表上有索引吗？ - Mateus Schneiders

@Mt.Schneiders 是的，我有。我已将它们添加到模式中。谢谢您的注意。 - Vyktor

Answer 1

在进一步操作之前需要检查的关键事项：

• 确保表引擎是InnoDB，否则“for update”不会锁定行，因为没有交易。
• 确保正确使用“for update”功能。如果您选择了一个要更新的内容，则该内容将锁定该交易。虽然其他交易可能能够读取该行，但在原始锁定交易释放锁之前，它不能被任何其他交易选择进行更新或删除。
• 为了保持清洁，尝试使用“START TRANSACTION”显式启动事务，运行您的“for update”选择，对返回的记录执行您要执行的任何操作，最后通过显式执行“COMMIT”来关闭事务。

就我所知，排序和限制对您遇到的问题没有影响，无论如何，Select返回的内容都将被锁定。

回答您的问题：

1. 是否可以在两个不同的事务中分别获取两个不同的锁（在同一张表中的不同行）？
   是的，但不能在相同的行上。锁只能在一次交易中以行级存在。
2. 第一个和第二个查询之间是否有很多冲突（我有困难调试这个问题，任何关于如何调试SELECT ... FROM（SELECT ...）WHERE ... IN（SELECT）的提示将不胜感激）？
   可能会有一个短暂的时间，其中正在计算行锁定，这将延迟第二个查询，但是除非您同时运行数百个这些选择进行更新，否则它不应该对您造成任何重大或显著的延迟。
3. ORDER BY ... LIMIT ...是否会引起任何问题？ 根据我的经验，它们应该像正常的选择语句一样工作。
4. 索引和键是否会引起任何问题？
   索引应该像往常一样存在以确保足够的性能，但是它们不应该对获取锁造成任何问题。

- Andrew McGrath

Answer 2

接受的答案中所有的观点都很好，除了以下两点： "无论选择返回什么，都将锁定这些行。" 和 "索引和键是否会造成任何问题？ 但它们不应该对获取锁造成任何问题。"

实际上，所有在决定选择和返回哪些行时内部读取的行都将被锁定。例如，下面的查询将锁定表的所有行，但可能只选择并返回几行： select * from table where non_primary_non_indexed_column = ? for update 由于没有索引，DB将不得不读取整个表来搜索所需的行，因此锁定整个表。

如果您只想锁定一行，则需要在where子句中指定其主键或索引列。因此，在仅锁定适当行的情况下，索引变得非常重要。

这是一个很好的参考 - https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

- Shreyas Shinde