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";
}

[download]
(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.

In reply to Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert. by graff
in thread DBI insert: Need to retrieve an autoincremented column from a table after an insert. by SkipHuffman

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.