Hi all,

I have a user table in a mysql db, when a user is inserted, I need the
generated keyfield from their entry to enter data into another table.
To accomplish this I insert the user and immediately after, run
another query using the LAST_INSERT_ID() command. This seems to work,
but isn't there a possibility that inbetween these two queries,
another user could register and the LAST_INSERT_ID return the wrong
id?
If so, is there a better way of doing this? I tried executescaler on
the insert, but it always returned nothing.

Any help would be appreciated!

Gef.Mongoose@gmail.com wrote:
> Hi all,
>
> I have a user table in a mysql db, when a user is inserted, I need the
> generated keyfield from their entry to enter data into another table.
> To accomplish this I insert the user and immediately after, run
> another query using the LAST_INSERT_ID() command. This seems to work,
> but isn't there a possibility that inbetween these two queries,
> another user could register and the LAST_INSERT_ID return the wrong
> id?
> If so, is there a better way of doing this? I tried executescaler on
> the insert, but it always returned nothing.
>
> Any help would be appreciated!

LAST_INSERT_ID() is connection specific. You will get the value from your
insert whilst someone else would get the value from their insert.

Indeed the book commonly known as the MySQL Reference Manual, has this to
say on the subject:

The ID that was generated is maintained in the server on a per-connection
basis. This means that the value returned by the function to a given client
is the first AUTO_INCREMENT value generated for most recent statement
affecting an AUTO_INCREMENT column by that client. This value cannot be
affected by other clients, even if they generate AUTO_INCREMENT values of
their own. This behavior ensures that each client can retrieve its own ID
without concern for the activity of other clients, and without the need for
locks or transactions.

On 4 Feb, 18:08, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> Gef.Mongo...@gmail.com wrote:
> > Hi all,
>
> > I have a user table in a mysql db, when a user is inserted, I need the
> > generated keyfield from their entry to enter data into another table.
> > To accomplish this I insert the user and immediately after, run
> > another query using the LAST_INSERT_ID() command. This seems to work,
> > but isn't there a possibility that inbetween these two queries,
> > another user could register and the LAST_INSERT_ID return the wrong
> > id?
> > If so, is there a better way of doing this? I tried executescaler on
> > the insert, but it always returned nothing.
>
> > Any help would be appreciated!
>
> LAST_INSERT_ID() is connection specific. You will get the value from your
> insert whilst someone else would get the value from their insert.
>
> Indeed the book commonly known as the MySQL Reference Manual, has this to
> say on the subject:
>
> The ID that was generated is maintained in the server on a per-connection
> basis. This means that the value returned by the function to a given client
> is the first AUTO_INCREMENT value generated for most recent statement
> affecting an AUTO_INCREMENT column by that client. This value cannot be
> affected by other clients, even if they generate AUTO_INCREMENT values of
> their own. This behavior ensures that each client can retrieve its own ID
> without concern for the activity of other clients, and without the need for
> locks or transactions.

Thanks for answering this. I'm using c# asp.net with mysql and am
curious if connection pooling utilised by .net would effect this
command in any way?