> 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
