peer review: recipe for solving SELECT/INSERT/UPDATE race condition

Sometimes it's desirable to first to a SELECT to see if a row exists,
and then INSERT if it doesn't already, or UPDATE if it does.

Using this pattern on a busy website, I noticed a race condition:
two SELECTs could happen at nearly the same time, and both decide to
INSERT. The second INSERT fails, unless a check is more for it.

Here's the recipe I'm trying to out to address the race condition now,
targeting PostgreSQL.

First, I construct INSERT that embeds the SELECT statement, so it only
happens if the row doesn't already exist.

Second, I check to see if the INSERT fails with "duplicate" error, which
is part of the string that PostgreSQL returns if you try to insert the
same primary key twice.

Seem reasonable? Here's the key code, which is executed
in a larger eval{} block, with RaiseError = 1;

    my $sth = $dbh->prepare(
         "INSERT INTO " . $self->table_name . " (a_session,id)  SELECT ?, ?
            WHERE NOT EXISTS (SELECT 1 FROM " . $self->table_name . " 
WHERE id=? LIMIT 1)");

    $sth->bind_param(1,$datastr,{ pg_type => $type });
    $sth->bind_param(2, $sid);
    $sth->bind_param(3, $sid); # in the SELECT statement
    my $rv = '';
    eval { $rv = $sth->execute(); };
    if ( $rv eq '0E0' or (defined $@ and $@ =~ m/duplicate/i) ) {
        my $sth = $dbh->prepare("UPDATE " . $self->table_name . " SET 
a_session=? WHERE id=?");
        $sth->bind_param(1,$datastr,{ pg_type => $type });
        $sth->bind_param(2,$sid);
        $sth->execute;
    }

Thanks!

     Mark

• peer review: recipe for solving SELECT/INSERT/UPDATE race condition by Mark Stosberg
• Re: peer review: recipe for solving SELECT/INSERT/UPDATE race condition by Stuart Cooper
• Re: peer review: recipe for solving SELECT/INSERT/UPDATE race condition by Mark Stosberg