You said:
I have tried the "last_insert_id" function, but I really don't trust it. I could get into a race condition if a second row were inserted before I could retrieve the id from the row that I just inserted.
Based on the description of "last_insert_id" in the DBI man page, it seems clear that this function is no more portable than the notion of an auto-increment field itself. If portability to different DB servers is a serious design goal for you, then adapting to different servers will have to be handled either as an install-time config issue, or as a run-time branching issue for the code you are writing. One way or another, if you have to support DB servers that differ wrt autoincrement/sequence keys, you'll need to write distinct code for each distinct flavor, and there's no way around it.
Apart from that, as pointed out above by Jenda, when the DB server and associated DBD module properly support last_insert_id, there's no reason to worry about race conditions, and it's easy enough to test for the proper support:
#!/usr/bin/perl
use strict;
use DBI;
my $db1 = DBI->connect( "DBI:mysql:database=test;host=localhost", "",
+"" );
$db1->do( "DROP TABLE IF EXISTS testautoinc" );
$db1->do( "CREATE TABLE testautoinc (tkey int not null auto_increment
+key,".
"tval varchar(100))" );
my $db2 = DBI->connect( "DBI:mysql:database=test;host=localhost", "",
+"" );
my $sth1 = $db1->prepare( "insert into testautoinc (tval) values (?)"
+);
my $sth2 = $db2->prepare( "insert into testautoinc (tval) values (?)"
+);
$sth1->execute( "foo on sth1" );
$sth2->execute( "bar on sth2" );
$sth1->finish;
$sth2->finish;
my ( $key2 ) = $db2->last_insert_id( undef, undef, undef, undef );
my ( $key1 ) = $db1->last_insert_id( undef, undef, undef, undef );
print "key1 is $key1, key2 is $key2\n";
my $data = $db1->selectall_arrayref( "select * from testautoinc" );
for my $row ( @$data ) {
print join( "\t", @$row ), "\n";
}
(update: inverted the order of the two "last_insert_id" calls, just to prove that calling order doesn't matter)
For me (using mysql 5.0), the output of that script shows that last_insert_id does the right thing in terms of keeping the connections separate and returning the correct id value for each one.
My inclination would be to go ahead and write distinct code for distinct servers (as the need arises), so that I don't slow down operations unnecessarily on the servers that don't pose any problems. |