how to use IF ?

[benutzername]

I have a table containing three columns: "ID", "url" and "status". The table contains a list of URLs,
all with status = 0 in the beginning.

With each query I want to read one URL from the database and mark the read URLs by setting their status
to 1.

In the moment, I'm doing this with two queries:

1.) "SELECT ID, url FROM myTable WHERE status = 0 LIMIT 1"

This way I get an URL and the ID of this URL. Now I can set the status of this URL to 1 to mark it as
read:

2.) "UPDATE myTable SET status = 1 WHERE ID = " + urlID

This works fine...

The problem: I'm working with multiple threads, so it could happen, that another thread gets the same
URL, before the status is set to 1.

The solution: I want to perform both actions in a single query - this shouldn't be so hard, but I'm
not very into SQL so I don't know, how to do it.

best regards,

Ben Utzer

 

[piti]

as i'm not exactly sure what you want to do, here are some options for you

1. you could use the LOCK/UNLOCK TABLES statement - a problem may occur when the process is stopped when the table is locked and there's not a procedure for unlocking it

2. UPDATE myTable SET status = 1 WHERE ID = urlID AND status = 0 and after runing this query check the affected rows number, here the row will not be updated if someone has updated it before

3. your select-update style is not so clear for me (the select is returning one row with status set to 0, but what row?):
- if you are updating all records in a loop then instead of that do: UPDATE myTable SET status = 1 WHERE status=0
- if you want to update one row (whatever) :
UPDATE myTable SET status = 1 WHERE status = 0 LIMIT 1
but you can't control which row is updated ?!

hope this helps

 

[benutzername]

I'm beginning to understand the concept behind SQL

Here's my problem in higher detail:

- I'm programming a webcrawler
- the database contains all the urls, the crawler still has to visit, the order in which he visits them doesn't matter at all
- the crawler is running with multiple threads, so i want to prevent, that two crawlers get the same url

I think, that since I can't update and query in SQL-Line, I will have to solve the problem in Java, just prevent, that two crawlers work on the database at the same time. Thats a pitty, because it will slow down my program...parallel access would be faster.

Ben Utzer

 

[piti]

i would try it with the lock/unlock option, as it's a program, there is an easy way to prevent that locked table problem

or what about having one url assigning process and multiple crawlers?
each time the crawler is finished with one url, he asks for onother one the assigning process

 

[benutzername]

hey, i like that second solution.

thx