Re: race conditions

> SELECT ID FROM ITEM WHERE URL='X' FOR UPDATE
> IF (ROW RETURNED) {
>   $ID = ITEM.ID
> } ELSE {
... do insert ...
> }

In this situation, I would try to somehow incorporate
a constraint into the database table definition.
E.g.,

CREATE TABLE item ( ...  url VARCHAR(80), UNIQUE(url), ... );

With this UNIQUE constraint, the database will throw an
error if you try to insert a duplicate row.  Then you
can simply:

INSERT INTO item (..., url, ...) VALUES(...)
IF(error) {
    Probably a duplicate, so SELECT and/or UPDATE
    (If this operation fails, too, then something's really wrong)
} ELSE {
    INSERT succeeded, we're all done
}

There's no race condition here; even if multiple threads
run this code, one of the inserts must happen first,
and the second one is gauranteed to fail.  If you're
using Perl DBI (I saw $ characters in your outline),
you'll want to follow this outline to disable DBI's
default fascist error response:

$stmt = $dbh->prepare("INSERT ...");
$stmt->{'PrintError'}=0;
$stmt=>{'RaiseError'}=0;
if($stmt->execute(...)) { # insert failed, probably duplicate
    # Try Select or update
} else {
   ...
}
                - Tim Kientzle