Collecting ID of Auto Increment Field in MySQL

SurgeProto

I'm considering switching my MySQL tables ID fields into autoincrement fields because I'm running into some problems with duplicate IDs. However, is there a simple way to determine what this ID is on an insert?

If I have to query based on the information I inserted, I'm going to run into problems because some of the records will be the same. I suppose I could insert a date/time with the record, but if I get 2 duplicate records at the same time it could create a problem for me. Any insight into this would be greatly appreciated!!

laserlight

Use [man]mysql_insert_id/man to retrieve the autogenerated id of the row you just inserted.

SurgeProto

How does this function work if there is a row inserted between the time I insert the first row and the time I call the function? I read the docs on it and it was unclear.

stolzyboy

Because of the following quote, there is always a chance, though very slim, that you could fall into a race condition where another query was inserted after your first insert, but before your mysql_insert_id.

To combat those issues, you'd need to use transactions, which with MySQL aren't supported until, I believe MySQL 4.X

Note: Because mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value.

Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.

ahundiak

Going to have to disagree with you stolzyboy. mysql_inset_id will always return the correct last inserted id for records inserted on a particular connection. So even if someone else comes along at the same time on a different web page and manages to insert another record you will still get the correct id because the other web page will be using a different connection.

As long as you are within the same request and you use the same connection handle then mysql_insert_id will work just fine.

stolzyboy

As a standard, I'd agree with you, however, I've seen too many problems with "trusting" that technique, granted it wasn't with PHP/mysql, it was with another language and mysql, but still using the mysql last inserted id functionality. To be guaranteed, you need to use transactions or lock the tables during that specific run through of your queries. This isn't only apparent with mysql but mssql as well, not sure about postgresql as I've never had to worry about this situation with postgresql, so far.

ahundiak

stolzyboy wrote:
As a standard, I'd agree with you, however, I've seen too many problems with "trusting" that technique, granted it wasn't with PHP/mysql, it was with another language and mysql, but still using the mysql last inserted id functionality. To be guaranteed, you need to use transactions or lock the tables during that specific run through of your queries. This isn't only apparent with mysql but mssql as well, not sure about postgresql as I've never had to worry about this situation with postgresql, so far.

Sources please? AFAIK, within the PHP/mysql context mysql_insert_id can be absolutely relied upon. No table locking required and transactions seem irrelevant. Granted if you are doing some kind of multiple updates with stored procedures or something then there might be some issues. But a straight forward insert? If they are not safe then I for one would really like to know.

stolzyboy

Source would be me. I've ran into this problem... yes, it was very complex systems, but it did run into race conditions using mysql_insert_id. Now, why did it happen, I'm unsure, but it was solved by using transactions. Transactions are nice as you can rollback the WHOLE thing if something barfs, ie. nothing happens.

It's probably safe on simple inserts to just rely on mysql_insert_id, but I tend to err on the side of caution.