mysql-锁定选择查询的行? [英] mysql - Locking rows for select query?

问题描述

我有一个mysql表(表A)，其中包含唯一产品ID的列表.利用此表的应用程序利用多个线程，每个线程都选择一个表行(最上面)，使用API​​来获取产品数据并将其更新到另一个表(表B).完成后，线程将删除表A中相应的产品ID行，并选择另一行进行处理(循环直到表B中的所有行都被删除，而表B已更新).

I have a mysql table (table-A) that contains a list of unique product ids. The app which utilizes this table makes use of multiple threads, each of which selects a table row (top most), uses an API to grab the product data and update it to a different table (table-B). Once done, the thread deletes the corresponding product id row within table-A and selects another one to work on (looping until all rows in table-A have been deleted while table-B has been updated).

如何防止应用程序的线程意外地在表A的同一行上工作?有没有一种方法可以防止选择某行?

How do I prevent my app's threads from accidentally working on the same row from table-A? Is there a way to lock a row from being selected?

示例: 应用程序的线程1从表A中选择行1.从API抓取所有相关数据并将其更新到表B中大约需要10到15秒.在这种情况下，线程2将触发并检查表A以选择要处理的行.在这种情况下，我只希望锁定第1行，这样它就不会线程2无法看到/读取它，而是选择第2行.

Example: The application's thread-1 selects row-1 from table-A. It takes about 10 to 15 seconds to grab and update all the related data into table-B from the API. While this is happening, thread-2 will fire off and check table-A to select a row to work on. In this case, I want only row-1 locked so that it does not thread-2 does not see/read it and instead picks row-2.

推荐答案

本地MySQL锁定不提供此功能.您可以使用一列来执行锁定".

Native MySQL locking doesn't provide this functionality. You could use a column to perform your "locks".

假设每个线程都有一个唯一的ID，则可以创建一个名为thread_owner的列，默认值为0.

Assuming each thread had a unique ID, you could create a column named thread_owner, with default 0.

一个线程会抓住这样的一行:

One thread would grab a row like this:

UPDATE mytable
SET thread_owner = :my_threadID
WHERE thread_owner = 0
LIMIT 1

然后选择这样的行(如果没有要处理的行，则可能不返回):

Then select the row like this (it might return none, if there were no rows to be processed):

SELECT *
FROM mytable
WHERE thread_owner = :my_threadID

然后处理它，最后将其删除.

Then process it, and finally delete it.

此解决方案在MyISAM和InnoDB上均可使用.

This solution would work on both MyISAM and InnoDB.

但是，对于InnoDB，这可能很慢，因为每个UPDATE语句都试图锁定thread_owner = 0的所有行，除非您确定每次都以相同的顺序锁定所有行，否则甚至可能导致僵局.因此，您可以尝试在UPDATE语句中显式锁定整个表:

However, for InnoDB, it might be slow because each UPDATE statement is trying to lock all rows where thread_owner = 0, and unless you're sure you're locking all rows in the same order each time, it could even cause a deadlock. So, you might try explicitly locking the whole table in your UPDATE statement:

LOCK TABLES mytable WRITE;
UPDATE mytable
SET thread_owner = :my_threadID
WHERE thread_owner = 0
LIMIT 1;
UNLOCK TABLES;

这样，MyISAM和InnoDB都将以相同的方式工作.

That way, both MyISAM and InnoDB will work the same way.

这篇关于mysql-锁定选择查询的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！