How do I lock read/write to MySQL tables so that I can select and then insert without other programs reading/writing to the database?

Question

I am running many instances of a webcrawler in parallel.

Each crawler selects a domain from a table, inserts that url and a start time into a log table, and then starts crawling the domain.

Other parallel crawlers check the log table to see what domains are already being crawled before selecting their own domain to crawl.

I need to prevent other crawlers from selecting a domain that has just been selected by another crawler but doesn't have a log entry yet. My best guess at how to do this is to lock the database from all other read/writes while one crawler selects a domain and inserts a row in the log table (two queries).

How the heck does one do this? I'm afraid this is terribly complex and relies on many other things. Please help get me started.

---

This code seems like a good solution (see the error below, however):

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() ) 

but I keep getting the following mysql error:

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

Is there a way to accomplish the same thing without this problem? I've tried a couple different ways. Including this:

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

Answer 1

You can lock tables using the MySQL LOCK TABLES command like this:

LOCK TABLES tablename WRITE;  # Do other queries here  UNLOCK TABLES; 

See:

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

Answer 2

Well, table locks are one way to deal with that; but this makes parallel requests impossible. If the table is InnoDB you could force a row lock instead, using SELECT ... FOR UPDATE within a transaction.

BEGIN;  SELECT ... FROM your_table WHERE domainname = ... FOR UPDATE  # do whatever you have to do  COMMIT; 

Please note that you will need an index on domainname (or whatever column you use in the WHERE-clause) for this to work, but this makes sense in general and I assume you will have that anyway.