Is it safe to update a MySQL table while iterating a resultset with Perl DBI?

Question

Specifically, I need to delete some rows from a table while iterating over all rows in the table. Does DBI have something like an updateable resultset in Java? So, if I do something like:

$query_all = $dbh->prepare("select Id, X, Y, Z from MyTable");
$delete = $dbh->prepare("delete from MyTable where Id = ?");
$query_all->execute();

while ( my @res = $query_all->fetchrow_array() ){
    my ($id, $x, $y, $z) = @res;
    # fetch the IDs of any other rows that have the same X, Y, Z values
    foreach ( the_duplicate_ids ){
        $delete->execute($dup_id); # safe ??
        # also delete from another table using $dup_id
    }
}

...is that OK?

Just to give some context, I'm deleting duplicate rows that have the same values for the X, Y, Z columns, and leaving just one row in each case (the first one found). If that was all I was doing, I'd just setup a unique index on those 3 columns to eliminate the dups, but I also need to delete a row from another table for each duplicate deleted from MyTable.

In the end, I wrote a script to identify and collect all the IDs for the rows that I need to delete into an array, then iterate over that array, deleting the relevant rows from both tables. I'm still interested in finding an answer to the original question though. If I get time I'll try to answer it myself, but if anyone already knows I'd like to hear it.

Answer 1

You're worried about a concurrent modification error?

If you're using transactions, it won't be a problem as long as you save the commit for AFTER you're done iterating.

However if you're using autocommit it could be a problem and so the solution would be:

Pseudo Code ish -- not tested

#Loop through each row and note the records to delete
my @deathrow = ();
foreach my $row ( @resultset ) {
    push $row->{id}, @deathrow;
}

#Now that we're done iterating, do all the deletes in one statement
$dbh->execute("DELETE WHERE id IN ( " . @deathrow . " )"); #something like that