I would be very surprised if you found a database where the last_insert_id is not session specific. That is you always get YOUR last inserted id. You can test it by a script that'd make two connections, insert one row by each connection and then looked at the last_insert_id() for each connection.

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";
}
[download]

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.

DBI has last_insert_id but be sure to read all the notes about portability and such. I'm no dba but I believe the dilemma is that there's no real standard for such a query.

Edit: Oops, you already mentioned that. Sorry, I didn't read your post closely enough. I'm afraid you'll probably need to get into something more driver specific. As Anon mentioned, PostgreSQL has a native way to get an id, and so do some (most?) other databases.

One way to achieve database portability and avoid race conditions is to assign the id from a common source independent of the database, then insert the id along with your data. The example below (untested) of course is not OS portable and has some warts of its own.

my $episodeid = nextId();

my $query=qq(INSERT INTO MasterEpisode SET 
    episoideid   = "$episodeid",
    ShowId       = "$ShowID",
    title        = "$p->{'ep_title'}",
    description  = "$p->{'ep_description'}",
    uploaddate   = "@{[localtime]}"
    );

sub nextId {
    open my $seq, "+<", "sequence.dat";
    flock $seq, 2
    my $id = <$seq>;
    $id++;
    print $seq $id;
    close $seq;
    return $id;
}
[download]

assign the id from a common source independent of the database

I would want to keep the sequence ids in the database...one way to do that in a portable way is with DBIx::Sequence.

I would want to keep the sequence ids in the database

I agree, its one of the warts from using a separate file. Like the OP, I've never completely trusted last_insert_id either, and getting unique row id's is just not an easy thing to do portably. I wasn't aware of DBIx::Sequence, just tried it out and it seems to work as advertized. Thanks for the pointer.

Thanks, but I prefer to remain driver independent.

Skip

use DBI;
#connection stuff 
#prepare your statement handles 
#assuming the values are unique enough...
my $sth_ins = $dbh->prepare("INSERT INTO table_foo(col1,col2,col3) VAL
+UES(?,?,?)");
#after the insert run the query on those same values.
my $sth_sel = $dbh->prepare("SELECT id FROM table_foo WHERE col1=?, co
+l2=?, col3=?");

#...later in the code...
my $last_id;
for(@some_data_of_yours){
   $sth_ins->execute($_->{'col1'},$_->{'col2'},$_->{'col3'});
   $sth_sel->execute($_->{'col1'},$_->{'col2'},$_->{'col3'});
   $sth_sel->bind_columns(\$last_id);
   $sth_sel->fetch();
   #do some stuff with your $last_id...
}
[download]