Using mysql_insert_id() in the middle of a transaction

Question

If I begin a transaction, and make multiple INSERTs in it (pre-COMMIT), mysql_insert_id() doesn't seem to update following each one.

Is this the expected behavior? If so, what can I do to get the ID's for the things I just inserted?

Answer 1

Your assumption is wrong. Here is code that proves it:

mysql_query('CREATE TABLE `test` (
               `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
               `Name` varchar(255) NOT NULL,
                PRIMARY KEY (`ID`)
             ) ENGINE=InnoDB') or die(mysql_error());
mysql_query('SET AUTOCOMMIT=0') or die(mysql_error());
mysql_query('START TRANSACTION') or die(mysql_error());
mysql_query("INSERT INTO test VALUES (NULL, 'Martin')") or die(mysql_error());
echo mysql_insert_id().'<br />';
mysql_query("INSERT INTO test VALUES (NULL, 'Dani')") or die(mysql_error());
echo mysql_insert_id().'<br />';
mysql_query("INSERT INTO test VALUES (NULL, 'Pesho')") or die(mysql_error());
echo mysql_insert_id().'<br />';
mysql_query('COMMIT') or die(mysql_error());
mysql_query('SET AUTOCOMMIT=1') or die(mysql_error());

The output of this simple code is:

1
2
3

The interesting thing is that even if you rollback the transaction (instead of committing it), the result will be the same and the values for these ids will never be used again. So the next insert will start with 4.