Re: Re: Getting last record ID created from DB

Robert Cummings wrote:

On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:

      
An earlier post called attention to a concurrency problem.  Wouldn't

getting the last inserted ID from LAST_INSERT_ID() suffer from the same limitations as any of the other solutions which do a select to get the last ID?

No, it's a MySQL specific feature that is atomic with the insert and so you are guaranteed that the the returned ID is the exact automatic ID associated with the most recent INSERT for the connection handle. Unfortunately auto increment is MySQL specific and so it isn't transferrable to other database engines.

The problem is if the ID returned is not somehow linked to the transaction which inserted the row. If you have an insert followed by a select to retrieve the ID, in the interrum, another user may be created by another process and you will retrieve the wrong ID.

I don't believe this is an issue for MySQL. The last inserted ID isn't stored for the database table, it's stored for the connection to the database. As such there's no race condition unless the developer does something silly, like issue another insert over the same connection before requesting the previous last inserted id. Just to be sure though the following is from the MySQL online documentation: :::: For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed. :::: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html Cheers, Rob. Thanks. Very interesting, and makes a lot of sense. -- _____________________ Myron Turner http://www.room535.org http://www.bstatzero.org http://www.mturner.org/XML_PullParser/