Forum Moderators: coopster
The answer to your question regarding how it works is in the second link...
The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions.
So mysql_insert_id() gives the last id, but if like 200,000 users use the site then that can be a problem then?
There is no problem. If mysql_insert_id() could be affected by other almost simultaneous hits to the same PHP script it would be useless.
It works on a per-connection basis, so unless you are doing anything very fancy with the mysql functions, any single instance of your script will own a single connection from start to finish.
A) INSERT 'foo' INTO bar
B) INSERT 'foo' INTO bar
A) MYSQL_INSERT_ID()
Now Alice has the ID for Bob's inserted record, which is not what you want! You can fix this by locking the DB connection while doing updates, or having a separate connection per update.
If you only have a single database connection open for your web server, that connection is shared, and you'll need locking.
If you have a connection open per user/transaction, then you will have no problems. I believe that most PHP scripts are written this way:
a) user connects, setup stuff and open a connection to the DB
b) do stuff with the DB connection
c) send page to user
d) clean up and close DB connection
Could not connect (1040): Too many connections
This means that that all available connections are already being used by other client connections. So, if Alice is connected and running a script, there is no way Bob is going to get a connection, frontend or backend. Or are you referring to something else?
Alice<--HTTP-->Webserver<--DB Socket-->Database
Bob<----HTTP-------^
In the webserver, you have something like this:
$db = Database.new if $db.nil?
def handle_connection(conn)
$db.add_entry conn.entry_text
end
handle_connection current_connection
Since there is only one shared database connection, everything gets multiplexed across this single connection.
Typically, things are architected like this:
Alice<--HTTP-->Webserver<--Alice's DB Socket-->Database
Bob<----HTTP----^ ^------Bob's DB Socket------^
Wherein each HTTP request opens, uses, and closes a DB connection. In this scenario it is possible to run out of database connections if you are getting more HTTP requests than you have available DB connections.
In a simple default installation of Apache/PHP/MySQL there is no problem at all using mysql_insert_id() - it will always work as you would reasonably expect it to.
And by "reasonably expect it to", I mean you are safe to use the return value from mysql_insert_id as a the foreign key in a record related to the one you have just inserted.
As I said before, if this was not how it worked it would be useless.
Although I agree that this is getting more complicated than necessary, I feel that when a statement is made that stretches the current understanding of a certain feature, then that statement must be ratified through discussion so we all might understand how it truly works. Of course, supporting documentation is necessary as well.
To state that the function is going to perform differently under certain circumstances will require an appropriate demonstration to include, for example, the specific web server configuration, MySQL server configuration and PHP scripting to confirm as such. I am not throwing this down as a rebuttal or challenge, but moreso an opportunity for anyone to educate the users of this forum beyond our current understanding.
