LAST_INSERT_ID() how it works at multi-users environment
check that question first:
Inserting an automatically generated index from a table into another table I got that this function "LAST_INSERT_ID()" do the required the question is: I'm using that in a php script, what if some user inserted d开发者_StackOverflowata in "table1" and before he insert the "id" into "table2" another user inserted data into "table1"...then which "id" would the function retrieve for the first user, his id or the id of the second user "the last one"?? I wish to retrieve the "id" that the user himself "the first user" inserted, so if it doesn't achieve that how to do it??LAST_INSERT_ID() gives you the last autogenerated id on the connection you execute it on, it does not return the last insert id globally produced by the MySQL server.
NEVER ever use lastInsertId() with PostgreSQL sequences, ESPECIALLY when your application's insert/update load is high. PostgreSQL sequences are non-transactional (a natural design feature to avoid exclusive locking which otherwise produces unacceptable performance). This means that any concurrent transaction incrementing the same sequence will render the value returned by lastInsertId() invalid with respect to the last insert by your transaction. Example:
Transaction 1 inserts with nextval('some_seq') yielding 100;
Concurrent transaction 2 inserts with nextval('some_seq') yielding 101;
Transaction 1 calls lastInsertId(), expecting 100, BUT GETS 101.
This PDO method is braindead for PostgreSQL, always use INSERT ... RETURNING instead. Regards.
Source
精彩评论