mysql - SELECT ... FOR UPDATE from one table in multiple threads

Question

我需要一点帮助 SELECT FOR UPDATE (resp. 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;

但我遇到的情况是，每次只返回一个查询。

所以我的问题是:

• 是否可以在两个单独的事务中对不同的行(在同一个表中)获取两个单独的锁？
• 我是否在第一个和第二个查询之间有那么多冲突(我在调试时遇到了麻烦，关于如何调试的任何提示 SELECT ... FROM (SELECT ...) WHERE ... IN (SELECT) 将不胜感激 )?
• ORDER BY ... LIMIT ... 会导致任何问题吗？
• 索引和键会导致任何问题吗？

Answer 1

在进一步了解之前要检查的关键事项:

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

据我所知，顺序和限制对您遇到的问题没有影响，Select 返回的任何内容都将是被锁定的行。

回答您的问题:

1. 是否可以在两个单独的事务中对不同行(在同一个表中)获取两个单独的锁？
   是的，但不在同一行。锁一次只能存在于一个事务中的行级别。
2. 我在第一个和第二个查询之间有那么多冲突吗(我在调试时遇到了麻烦，关于如何调试的任何提示 SELECT ... FROM (SELECT ...) WHERE ... IN (SELECT) 将不胜感激)?
   计算行锁的时间可能很短，这会延迟第二个查询，但是除非您同时运行数百个这样的更新选择，否则它不会导致任何重大或明显的延迟。
3. ORDER BY ... LIMIT ... 会导致任何问题吗？不是我的经验。它们应该像在普通 select 语句上一样工作。
4. 索引和键会导致任何问题吗？
   索引应一如既往地存在以确保足够的性能，但它们不应导致获取锁时出现任何问题。