http://www.perlmonks.org?node_id=739700

cog has asked for the wisdom of the Perl Monks concerning the following question:

Here's a shorter version of what I was trying to do:
my $sql = "INSERT INTO table_name" . " (a, b, c)" . " VALUE (?, ?, ?)" . " ON DUPLICATE KEY" . " UPDATE field_three = ?"; my $sth = $dbh->prepare( $sql ); for my $entry ( keys %entries ) { $sth->execute( $entries{'a'}, $entries{'b'}, $entries{'b'} ); my $id = $dbh->last_insert_id( undef, undef, undef, undef ); # use $id to insert stuff in another table }

Where the id is an auto-increment column, and also the primary key.

The problem is that last_insert_id() not always gives me the right id. (concurrency? transaction issues?)

I actually tried doing a SELECT on the table right after that INSERT, but since I'm doing this inside a transaction, the SELECT clause returns nothing...

Any ideas on how to go around this? Am I missing something trivial?

Both DBI's and MySQL's documentation point to a few CAVEATS around last_insert_id()...