Ok, this is probably obvious to most users but this bit me today. So I thought I would share it in case someone else makes the same mistake that I did. If you need the last insert ID from MySQL in a transaction on a table then do it before you commit. See the snippet below.

The format of the code is taken directly from the DBI pod so I've not done anything original ;). However, I've had to edit it to put it in snippet form so caveat emptor.

$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
my $insert_ID = 0;
eval
{
    my $sql = "some insert query";
    $sth ||= $dbh->prepare ($sql);
    $sth->execute (@params);

    $insert_ID = $dbh->{'mysql_insertid'};
    $dbh->commit;   # commit the changes if we get this far
};
if ($@)
{
    # And commit or rollback
    print STDERR "Transaction aborted because $@\n";
    $dbh->rollback; # undo the incomplete changes
}
[download]