如何锁定MySQL表的读写权限，以便我可以选择并插入数据而不被其他程序读取或写入数据库？

Question

我正在并行运行许多个网络爬虫实例。每个爬虫从表格中选择一个域名，将该网址和开始时间插入到日志表中，然后开始爬取该域名。其他并行爬虫在选择要爬取的域名之前检查日志表以查看哪些域名已经在被爬取。我需要防止其他爬虫选择刚被另一个爬虫选择但尚未在日志中有条目的域名。我最好的猜测是，在一个爬虫选择一个域并在日志表中插入一行记录（两个查询）时，从所有其他读取/写入中锁定数据库。如何做到这一点？我担心这非常复杂，依赖于许多其他因素。请帮助我入门。

---

下面的代码似乎是一个很好的解决方案（见下面的错误）。

INSERT INTO crawlLog (companyId, timeStartCrawling)
VALUES
(
    (
        SELECT companies.id FROM companies
        LEFT OUTER JOIN crawlLog
        ON companies.id = crawlLog.companyId
        WHERE crawlLog.companyId IS NULL
        LIMIT 1
    ),
    now()
)

但是我一直收到以下mysql错误：

You can't specify target table 'crawlLog' for update in FROM clause

有没有一种方法可以在不出现这个问题的情况下完成相同的事情？我尝试了几种不同的方法，包括这个：

INSERT INTO crawlLog (companyId, timeStartCrawling)
VALUES
(
    (
        SELECT id
        FROM companies
        WHERE id NOT IN (SELECT companyId FROM crawlLog) LIMIT 1
    ),
    now()
)

- T. Brian Jones

Answer 1

你可以使用MySQL LOCK TABLES命令来锁定表，例如：

LOCK TABLES tablename WRITE;

# Do other queries here

UNLOCK TABLES;

参见：

http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html

- qbert220

---

当其他进程在锁定期间尝试读取或写入表时，它们将会收到一个 MySQL 错误。 - Albert Hendriks

来自文档的@AlbertHendriks：“当持有写锁时，对表的锁请求会阻塞其他会话。” - Jonah

是的，所以如果他们只尝试查询，将会得到一个错误，但如果他们尝试获取锁，它将会等待。 - Albert Hendriks

@AlbertHendriks查询锁定的表不会导致错误（除非连接与Web服务等绑定并超时）。锁定只是阻止其他连接访问该表，直到解除锁定。一旦解除锁定，连接将自动恢复对表的访问。 - David Mordigal

@DavidMordigal，当一个表被锁定时运行的任何插入查询是否会在该表解锁时自动执行？ - R.S.K

Answer 2

表锁是解决这个问题的一种方法；但是这会使并行请求变得不可能。如果表是InnoDB，您可以使用事务中的SELECT ... FOR UPDATE强制进行行锁定。

BEGIN;

SELECT ... FROM your_table WHERE domainname = ... FOR UPDATE

# do whatever you have to do

COMMIT;

请注意，您需要在 domainname 上创建一个索引（或者您在 WHERE 子句中使用的任何列）才能使此方法正常工作，但这通常是有意义的，我假设您已经这样做了。

- wonk0

---

我来晚了，但如果我没有任何更新查询怎么办？这个锁定会在什么时候释放？@wonk0 - Rupesh Bhandari

Answer 3

您可能不希望锁定表格。如果这样做，您将不得不担心当其他爬虫尝试写入数据库时出现错误的问题 - 这就是您在说“...非常复杂并依赖于许多其他事情”时考虑的问题。

相反，您应该在MySQL事务中包装一组查询（请参见http://dev.mysql.com/doc/refman/5.0/en/commit.html），如下所示：

START TRANSACTION;
SELECT @URL:=url FROM tablewiththeurls WHERE uncrawled=1 ORDER BY somecriterion LIMIT 1;
INSERT INTO loggingtable SET url=@URL;
COMMIT;

或者接近这样。

[编辑] 我刚意识到 - 你可能可以通过一个查询完成所有需要，甚至不用担心事务。像这样：

INSERT INTO loggingtable (url) SELECT url FROM tablewithurls u LEFT JOIN loggingtable l ON l.url=t.url WHERE {some criterion used to pick the url to work on} AND l.url IS NULL.

- ratsbane

---

我不明白那会有什么帮助；两个并行的实例怎样避免读取相同的URL？ - wonk0

你说得对 - 它确实不会。请看我刚刚编辑的第二个示例。它将日志表连接到URL表，确保日志表中没有记录（l.url IS NULL）。 - ratsbane

没错，这样更好。但是必须记住，这绝对不能在事务内工作（取决于隔离级别）。 - wonk0

Answer 4

我不会使用锁或事务。

最简单的方法是如果日志表中不存在，则插入一条记录，并检查该记录。

假设您有填充了爬虫的tblcrawels（cra_id）和填充了URL的tblurl（url_id），以及用于日志文件的tbllogging（log_cra_id，log_url_id）表。

如果爬虫1想要开始爬取URL2，则应运行以下查询：

INSERT INTO tbllogging (log_cra_id, log_url_id) 
SELECT 1, url_id FROM tblurl LEFT JOIN tbllogging on url_id=log_url 
WHERE url_id=2 AND log_url_id IS NULL;

下一步是检查是否已经插入了该记录。

SELECT * FROM tbllogging WHERE log_url_id=2 AND log_cra_id=1

如果您得到了任何结果，则爬虫1可以爬取此URL。如果您没有得到任何结果，则意味着另一个爬虫已经插入了相同的行并正在进行爬取。

- Eljakim

---

我尝试实现你建议的代码时一直收到以下错误提示："You can't specify target table 'crawlLog' for update in FROM clause"。请查看我的更新后的问题，其中包含我的代码。 - T. Brian Jones

我没有完全理解你的建议，因为我不太确定你代码中的某一部分。那里的1和2是做什么的？ - T. Brian Jones

1 是爬虫的 ID，2 是 URL 的 ID。它们来自 tblcrawlers 和 tblurl 表。 - Eljakim

Answer 5

我从@Eljakim的答案中得到了一些灵感，并开始了这个新线程，在那里我想出了一个很棒的技巧。它不涉及锁定任何东西，非常简单。

INSERT INTO crawlLog (companyId, timeStartCrawling)
SELECT id, now()
FROM companies
WHERE id NOT IN
(
    SELECT companyId
    FROM crawlLog AS crawlLogAlias
)
LIMIT 1

- T. Brian Jones

---

然后你执行 SELECT LAST_INSERT_ID(); 来获取记录条目的ID，该条目包含了爬虫应该爬行的域名，是吗？ - tonix

我也不认为这是可靠的。我有一个多线程应用程序，类似地尝试在cron计时器上插入唯一作业。如果它们太接近 -- 比如在3位数微秒内 -- 尽管WHERE检查，它们仍然会被插入。 - Rikaelus

@Rikaelus 我也遇到了完全相同的问题，在多线程应用程序中，尽管使用了“where not in”子句，插入操作仍然会发生。你是如何解决这个问题的？ - 1Mojojojo1

@PramodRoy 很难在评论中解释，但这是一些 DBA-fu。我使用可重复的标识符和一个设置为 1 的“声明”列进行插入。这两个列是唯一键。我等待 5 秒钟，然后将“声明”列设置为 NULL，然后执行任务。任何尝试使用相同标识符和声明=1的其他线程/进程都会抛出完整性错误异常（重复键），我捕获并中止它。它利用了如何处理唯一键中的 NULL。这对于那些“等待”时间不可行的应用程序可能无法工作。 - Rikaelus

@PramodRoy 这种情况下，你要用管用的方法。我现在记不清具体细节了，但我曾尝试过锁定，但它们对我的应用程序没有起作用。不过很高兴你找到了解决方案。 - Rikaelus

Answer 6

最好使用行锁或基于事务的查询，以便其他并行请求上下文可以访问表格。

- Hims1911