I've spent the better part of the day trying to find an answer to this, but there just doesn't seem to be one out there. I have need of function which creates a row in a table, retrieves the auto-increment from the inserted row, and then uses that value to insert another row in a second table. Something functionally similar to the following php:
$mysql_query("INSERT INTO table1 SET columns='values'");
$id = mysql_insert_id();
$mysql_query("INSERT INTO table2 SET id='$id', columns='values'");
The problem I'm running into is that in my application, a single MySQL connection is shared between all the classes, so I'm worried that this might cause a race condition in which another class inserts a row between running the 1st and 2nd line of the above.
The simplest solution I can come up with is having any functions that use mysql_insert_id() open a new, unique, connection, but that seems like a huge amount of unnecessary overhead. Other answers I've played around with include inserting a unique value (possibly created with UUID()) and using that instead of an auto-increment value, or possibly putting the first insert and a call to LAST_INSERT_ID() in a stored function (though I'm not sure if that would resolve the possible race condition or not).
I've also been looking into transactions to see if they may help, but there is little documentation to be found regarding how exactly mysql_insert_id() interacts with them. Best I can tell, they probably won't help here, as another INSERT that didn't conflict with any of the transaction's locks would still be able to execute and possibly cause the same race condition.
So, assuming I'm not in error with any of the above (and please correct me if I am), what is the best way to ensure 100% that the second INSERT uses the proper value?
php script's execution is linear, not multi-threaded.
so, that's impossible to have a condition when one object being executed at the same time with another
The only possible issue can be with persistent connection. But it seems that the same connection cannot be used by 2 separate calls anyway. Even if it's persistent one, if it's already in use - it cannot be usedby another script. Thus, there will be no issues either.