Implementing a Sequence Counter for multi-threaded crawler

[formido]

I'm crawling a site and storing links to be crawled in a table. I have multiple threads running at the same time. What I'd like is for a thread to be able to get the primary key of the next unprocessed link in a single command and run with it.

I thought I could use a sequence counter, but I can't see a way to both increment the counter and return the value in a single command. Is there a way to do this? I want to avoid a race condition.

[pbmods]

Changed thread title to better describe the problem (did you know that threads whose titles contain three words or less actually get FEWER responses?).

Heya, formido. Welcome to TSDN!

You could do something like this:

SELECT @counter := @counter + 1
 

[formido]

Heh. Nope, never heard that. Awesome.

Regarding the code:

I've never programmed an RDBMS before, so I don't know all the ins-and-outs yet. It appears that I'd put that in a stored procedure and then call it from PHP?

I had thought there was some sort of feature called a 'sequence' that you could attach to a table or row. Your solution doesn't use this, but instead just increments a variable, right? If one thread increments the variable, will another thread see the new value if it accesses the same variable.

Now that I think of it, I had another question about 'sequences', which is that I thought I read that they were special to clients, so incrementing them wouldn't be visible to other clients. I assume each thread is considered a separate client?

[pbmods]

Heya, formido.

If you are inserting data into a table, you'll like MySQL's auto_increment feature:

CREATE TABLE
        `sequences`
        (
            `sequenceid`
                BIGINT(20)
                UNSIGNED
                NOT NULL
                AUTO_INCREMENT
                PRIMARY KEY,
            .
            .
            .
        )
 

[formido]

No, yeah, it's great, but it doesn't quite solve my problem, I think. See, when one of the spider PHP processes needs a new link it gets it out of the links table. There has to be a bullet proof way for any process to request the next unprocessed link in the links table. I thought the best way would be for a SQL maintained counter. A thread would ask SQL for the next number, and SQL would take care of incrementing the counter for the next process that requested it. That number would then be used on the autoincremented primary key column of the links table to retrieve the next URL.