mysql_insert_id()

[Anon]

Hi there,

I have been wondering that when one users mysql_insert_id() to get the ID of the last insert into the database, something odd might happen.

Imagine that at the same time other user is running the same script, couldn't this generate some problem, being that the ID's are not corrently returned ?

Anyone has some experience with this ?

Cheers,

TC

[Anon]

I don't believe that this would be a problem. I was looking at the mysql_insert_id() function in the MySQL API and it seems to only return the ID from the previous query in this connection, since 2 concurrent page hits would use 2 different connections I don't see that there would be a problem.

[[deleted]]

Not really, unless they're using the same connection that you just used. Theoretically. If you're worried about it, use BDB/InnoDB and transactions.

[[deleted]]

Search before you post.

mysql_insert_id is multi-user safe. (what else would be the point of having it?)

[[deleted]]

"If you're worried about it, use BDB/InnoDB and transactions."

How does that help?

[[deleted]]

Lock the row or table until you get your insert_id out.

[[deleted]]

Locking the row makes no sense, the database would still be able to create new rows wth new IDs

Locking the table could help, but it not an option. MySQL has finally gotten rid of the hopelessly slow table-level-locking in version 4.0, let's not encourage people to start using it again without good reason. :-)

the mysql_insert_id is perfectly safe to use in multi-user, providing your scipts are sane.

For ex: if you use persistent connections it is possible to insert a record, stop the script, start a new script, call mysql_insert_id() and get the id of the record that the previous script inserted. But that doesnt matter because who would ever want to retreive a new record id without first insterting a new record? :-)