mysql_insert_id() and LAST_INSERT_ID()

Hi,

If the website is visited heavily by customers, will this make the mysql_insert_id() getting the same last insert id sometimes? (which is not what I want)

Will using last_insert_id() be safer in terms of getting the last insert id if the site has tons of customers?

Thanks for any help in advance.

 

http://www.php.net/manual/en/function.mysql-insert-id.php

Says:

So mysql_insert_id is from the INSERT query you just did (connection specific), and last_insert_id (mysql function) is the last global insert id. This means you should use mysql_insert_id and not last_insert_id.

 

I would not reply on last insert id, what if after you get the value, another value is inserted? The best way to approach this is to let MySQL handle it using auto_increment.

 

I do have the mysql_insert_id() right after an insert query. But somehow when both customers submit the orders within 30(?) seconds or less, they ended up with same customer number. (Is this possible?)

 

Well, that shouldn't be possible. Can you show us the code?

 

For a heavily visited website, I was thinking a fraction of a second apart queries.

Do you have query_cache_size in your MySQL enabled?

 

No, mysql_insert_id is connection specific. Which means that it will return the insertion id for the last query from the user executing that page, no matter how many other people are browsing the site at the same time.

If mysql_insert_id was prone to these types of concurrency clashes, a hell of alot of sites would have serious issues. This is just not the case.

 

Since mysql_insert_id() is connection specific, is it possible that one customer didn't get a good connection but still went through the whole shopping process and somehow getting the same id as the other customer?

(The code is very long and involves many different files. I don't want to bother anybody with it.)

 

No, that's not possible. If you somehow want to change something to the code (which will really probably not change a thing), you can try this, the function definition of mysql_insert_id is:

 int mysql_insert_id  ([ resource $link_identifier  ] )

Code (markup):

As you can see, you can add an optional link identifier. Which is what is returned from mysql_connect.

So you can try:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

//...

$id = mysql_insert_id($link);

PHP:

But it really shouldn't change a thing unless you are using multiple open connections to different dbs with the same user.

 

How do I check and what is normal?

 

Query cache has no effect on mysql_insert_id so you shouldn't even bother.

 

I was just wondering. If the insert query right before mysql_insert_id() didn't get excuted correctly then it would have caused the result of mysql_insert_id() one less number (the one before the latest insert), wouldn't it?

 

First of all you need to make it so that if a query fails your script redirects to an error page. If you keep processing you will end up with unreliable results.

Secondly, I think it will just return false or something like that.

 

Just curious. What would have happened if 2 customers went to the same page and triggered the insert query and mysql_insert_id() at the EXACT same time?

 

Nothing would happen, as has already been said mysql_insert_id is connection specific.. I don't know why you are still going on about this, something else is obviously wrong with your system. The function works fine and is concurrency proof.

 

in cases with many mysql connection it may be better to use a persistant connection.. so all instances oft the php script are using the same connection...

in DB classes like http://pear.php.net/package/DB there is no function to get the last inserted id, but they have an own ID management with an extra table, so you can get the next ID for an INSERT before you make these INSERT... I think thats the only really safe way to know the correct ID for the maked INSERT in all cases....

 

I've never had a problem with duplicate ID's being returned from simultaneous writes from two different script instances, I agree that there is more likely an issue with the PHP script as opposed to the server environment.

 

Thank you for all the explanation. I think I found the problem of my script.

 

Hmm good to see that you have found your problem but here is another solution if you want to get a next increment number from table i hope it will be useful for you, its just my try may give you some knowledge

$table = 'tablename';
$sql = mysql_query("SHOW TABLE STATUS LIKE '$table'");
$row = mysql_fetch_object($sql);
$newID = $row->Auto_increment;

i hope it will definitely help many developers here if you like my effort do i deserve reputation point Best of luck my friends.

 

there you get a problem, if between these query and your INSERT an other INSERT was made...