3

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?

Michael Fenwick
  • 2,374
  • 2
  • 19
  • 28

2 Answers2

7

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Believe me, I have spend far more than a couple minutes reading manual pages. I am aware that `mysql_insert_id()` is connection specific, but like I said in the original post, all the classes of my application use the same connection at present (there is a single db class which stores the connection and all the classes use it through `global $db;`. Now, if it's not possible for more than one part of the php code to run at the same time while still using the same connection, that would indeed simplify this, but I haven't be able to find anything to support or reject that yet. – Michael Fenwick Jan 09 '12 at 23:06
  • What about AJAX scripts loading multiple php files at the same time? I understand that PHP doesn't directly support multi-threading, but might the browser/server result in similar behaviour? – Michael Fenwick Jan 09 '12 at 23:12
  • @MikeF Don't worry, it's not possible - that's what he meant by saying that execution is linear. – Narf Jan 09 '12 at 23:15
  • An AJAX request would trigger a separate script execution, which can't use the same connection at the same time. – Narf Jan 09 '12 at 23:16
  • @Mike PHP has absolutely nothing to do with AJAX. PHP knows nothing about ajax. For the PHP, all the HTTP calls are the same (save for the particular headers whose doesn't matter). – Your Common Sense Jan 09 '12 at 23:20
  • And, you know, your classes aren't that unique. Every [sane] PHP application is doing the same. – Your Common Sense Jan 09 '12 at 23:22
  • 1
    If I load a page through AJAX, each one reports the same resource number for the connection, so if they were to run at the same time, I could run into issues. I'm still not seeing where they are prevented from using the same connection at the same time. Is it somewhere in the server level with the XHRs not being fulfilled until the previous ones are finished loading? Note: not trying to argue with your answers, just trying to gain a deeper understanding of what's happening under the hood. – Michael Fenwick Jan 09 '12 at 23:45
  • LOL, if you load a page through regular request, the resource numbers would be the same. They are script-specific as well. I dunno where you can read a proof of my words though. These things are so basic that nobody even bother to mention them, I think. May be an HTTP protocol definition, RFC 2616 can be of help – Your Common Sense Jan 09 '12 at 23:51
  • 2
    @Mike the same resource number is coincidental, I think they are counted on a per-script basis anyway. A new PHP request is a new PHP request, be it through Ajax or not. Simultaneous requests do not share the same database connection. There is nothing for you to worry about, the code you show will always run fine – Pekka Jan 10 '12 at 00:13
  • Alright, I think I see better what's going on, and despite my best efforts, I am unable to get PHP to run in the manner I feared it might. I suppose I was being a little too over-cautious. Thanks for all the help. – Michael Fenwick Jan 10 '12 at 00:57
0

Try to use if conditional

example 

if(mysql_query(query)){
    $id = mysql_last_id();
    mysql_query(query using $id);
}
dstudeba
  • 8,878
  • 3
  • 32
  • 41
Manuel Garcia
  • 1